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

Commit1a77f8b

Browse files
committed
Avoid scanning nulls at the beginning of a btree index scan.
If we have an inequality key that constrains the other end of the index,it doesn't directly help us in doing the initial positioning ... but itdoes imply a NOT NULL constraint on the index column. If the index storesnulls at this end, we can use the implied NOT NULL condition for initialpositioning, just as if it had been stated explicitly. This avoids wastingtime when there are a lot of nulls in the column. This is the reverse ofthe examples given in bugs #6278 and #6283, which were about failing tostop early when we encounter nulls at the end of the indexscan.
1 parent882368e commit1a77f8b

File tree

1 file changed

+60
-7
lines changed

1 file changed

+60
-7
lines changed

‎src/backend/access/nbtree/nbtsearch.c

Lines changed: 60 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -456,6 +456,7 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
456456
boolgoback;
457457
ScanKeystartKeys[INDEX_MAX_KEYS];
458458
ScanKeyDatascankeys[INDEX_MAX_KEYS];
459+
ScanKeyDatanotnullkeys[INDEX_MAX_KEYS];
459460
intkeysCount=0;
460461
inti;
461462
StrategyNumberstrat_total;
@@ -506,6 +507,14 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
506507
* one we use --- by definition, they are either redundant or
507508
* contradictory.
508509
*
510+
* Any regular (not SK_SEARCHNULL) key implies a NOT NULL qualifier.
511+
* If the index stores nulls at the end of the index we'll be starting
512+
* from, and we have no boundary key for the column (which means the key
513+
* we deduced NOT NULL from is an inequality key that constrains the other
514+
* end of the index), then we cons up an explicit SK_SEARCHNOTNULL key to
515+
* use as a boundary key. If we didn't do this, we might find ourselves
516+
* traversing a lot of null entries at the start of the scan.
517+
*
509518
* In this loop, row-comparison keys are treated the same as keys on their
510519
* first (leftmost) columns. We'll add on lower-order columns of the row
511520
* comparison below, if possible.
@@ -519,6 +528,7 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
519528
{
520529
AttrNumbercurattr;
521530
ScanKeychosen;
531+
ScanKeyimpliesNN;
522532
ScanKeycur;
523533

524534
/*
@@ -528,6 +538,8 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
528538
*/
529539
curattr=1;
530540
chosen=NULL;
541+
/* Also remember any scankey that implies a NOT NULL constraint */
542+
impliesNN=NULL;
531543

532544
/*
533545
* Loop iterates from 0 to numberOfKeys inclusive; we use the last
@@ -540,8 +552,32 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
540552
{
541553
/*
542554
* Done looking at keys for curattr. If we didn't find a
543-
* usable boundary key, quit; else save the boundary key
544-
* pointer in startKeys.
555+
* usable boundary key, see if we can deduce a NOT NULL key.
556+
*/
557+
if (chosen==NULL&&impliesNN!=NULL&&
558+
((impliesNN->sk_flags&SK_BT_NULLS_FIRST) ?
559+
ScanDirectionIsForward(dir) :
560+
ScanDirectionIsBackward(dir)))
561+
{
562+
/* Yes, so build the key in notnullkeys[keysCount] */
563+
chosen=&notnullkeys[keysCount];
564+
ScanKeyEntryInitialize(chosen,
565+
(SK_SEARCHNOTNULL |SK_ISNULL |
566+
(impliesNN->sk_flags&
567+
(SK_BT_DESC |SK_BT_NULLS_FIRST))),
568+
curattr,
569+
((impliesNN->sk_flags&SK_BT_NULLS_FIRST) ?
570+
BTGreaterStrategyNumber :
571+
BTLessStrategyNumber),
572+
InvalidOid,
573+
InvalidOid,
574+
InvalidOid,
575+
(Datum)0);
576+
}
577+
578+
/*
579+
* If we still didn't find a usable boundary key, quit; else
580+
* save the boundary key pointer in startKeys.
545581
*/
546582
if (chosen==NULL)
547583
break;
@@ -574,24 +610,41 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
574610
*/
575611
curattr=cur->sk_attno;
576612
chosen=NULL;
613+
impliesNN=NULL;
577614
}
578615

579-
/* Can we use this key as a starting boundary for this attr? */
616+
/*
617+
* Can we use this key as a starting boundary for this attr?
618+
*
619+
* If not, does it imply a NOT NULL constraint? (Because
620+
* SK_SEARCHNULL keys are always assigned BTEqualStrategyNumber,
621+
* *any* inequality key works for that; we need not test.)
622+
*/
580623
switch (cur->sk_strategy)
581624
{
582625
caseBTLessStrategyNumber:
583626
caseBTLessEqualStrategyNumber:
584-
if (chosen==NULL&&ScanDirectionIsBackward(dir))
585-
chosen=cur;
627+
if (chosen==NULL)
628+
{
629+
if (ScanDirectionIsBackward(dir))
630+
chosen=cur;
631+
else
632+
impliesNN=cur;
633+
}
586634
break;
587635
caseBTEqualStrategyNumber:
588636
/* override any non-equality choice */
589637
chosen=cur;
590638
break;
591639
caseBTGreaterEqualStrategyNumber:
592640
caseBTGreaterStrategyNumber:
593-
if (chosen==NULL&&ScanDirectionIsForward(dir))
594-
chosen=cur;
641+
if (chosen==NULL)
642+
{
643+
if (ScanDirectionIsForward(dir))
644+
chosen=cur;
645+
else
646+
impliesNN=cur;
647+
}
595648
break;
596649
}
597650
}

0 commit comments

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