Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf7111f7

Browse files
committed
Improve planner's selectivity estimates for inequalities on CTID.
We were getting just DEFAULT_INEQ_SEL for comparisons such as"ctid >= constant", but it's possible to do a lot better if we don'tmind some assumptions about the table's tuple density being reasonablyuniform. There are already assumptions much like that elsewhere inthe planner, so that hardly seems like much of an objection.Extracted from a patch set that also proposes to introduce a specialexecutor node type for such queries. Not sure if that's going to makeit into v12, but improving the selectivity estimate is usefulindependently of that.Edmund Horner, reviewed by David RowleyDiscussion:https://postgr.es/m/CAMyN-kB-nFTkF=VA_JPwFNo08S0d-Yk0F741S2B7LDmYAi8eyA@mail.gmail.com
1 parentb3bd819 commitf7111f7

File tree

1 file changed

+75
-0
lines changed

1 file changed

+75
-0
lines changed

‎src/backend/utils/adt/selfuncs.c

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -557,6 +557,81 @@ scalarineqsel(PlannerInfo *root, Oid operator, bool isgt, bool iseq,
557557

558558
if (!HeapTupleIsValid(vardata->statsTuple))
559559
{
560+
/*
561+
* No stats are available. Typically this means we have to fall back
562+
* on the default estimate; but if the variable is CTID then we can
563+
* make an estimate based on comparing the constant to the table size.
564+
*/
565+
if (vardata->var&&IsA(vardata->var,Var)&&
566+
((Var*)vardata->var)->varattno==SelfItemPointerAttributeNumber)
567+
{
568+
ItemPointeritemptr;
569+
doubleblock;
570+
doubledensity;
571+
572+
/*
573+
* If the relation's empty, we're going to include all of it.
574+
* (This is mostly to avoid divide-by-zero below.)
575+
*/
576+
if (vardata->rel->pages==0)
577+
return1.0;
578+
579+
itemptr= (ItemPointer)DatumGetPointer(constval);
580+
block=ItemPointerGetBlockNumberNoCheck(itemptr);
581+
582+
/*
583+
* Determine the average number of tuples per page (density).
584+
*
585+
* Since the last page will, on average, be only half full, we can
586+
* estimate it to have half as many tuples as earlier pages. So
587+
* give it half the weight of a regular page.
588+
*/
589+
density=vardata->rel->tuples / (vardata->rel->pages-0.5);
590+
591+
/* If target is the last page, use half the density. */
592+
if (block >=vardata->rel->pages-1)
593+
density *=0.5;
594+
595+
/*
596+
* Using the average tuples per page, calculate how far into the
597+
* page the itemptr is likely to be and adjust block accordingly,
598+
* by adding that fraction of a whole block (but never more than a
599+
* whole block, no matter how high the itemptr's offset is). Here
600+
* we are ignoring the possibility of dead-tuple line pointers,
601+
* which is fairly bogus, but we lack the info to do better.
602+
*/
603+
if (density>0.0)
604+
{
605+
OffsetNumberoffset=ItemPointerGetOffsetNumberNoCheck(itemptr);
606+
607+
block+=Min(offset /density,1.0);
608+
}
609+
610+
/*
611+
* Convert relative block number to selectivity. Again, the last
612+
* page has only half weight.
613+
*/
614+
selec=block / (vardata->rel->pages-0.5);
615+
616+
/*
617+
* The calculation so far gave us a selectivity for the "<=" case.
618+
* We'll have one less tuple for "<" and one additional tuple for
619+
* ">=", the latter of which we'll reverse the selectivity for
620+
* below, so we can simply subtract one tuple for both cases. The
621+
* cases that need this adjustment can be identified by iseq being
622+
* equal to isgt.
623+
*/
624+
if (iseq==isgt&&vardata->rel->tuples >=1.0)
625+
selec-= (1.0 /vardata->rel->tuples);
626+
627+
/* Finally, reverse the selectivity for the ">", ">=" cases. */
628+
if (isgt)
629+
selec=1.0-selec;
630+
631+
CLAMP_PROBABILITY(selec);
632+
returnselec;
633+
}
634+
560635
/* no stats available, so default result */
561636
returnDEFAULT_INEQ_SEL;
562637
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp