BUG #6283: About the behavior of indexscan in case there are some NULL values.

The following bug has been logged online:

Bug reference: 6283
Logged by: Naoya Anzai
Email address: anzai-naoya(at)mxu(dot)nes(dot)nec(dot)co(dot)jp
PostgreSQL version: 9.1.1
Operating system: RHEL5.5
Description: About the behavior of indexscan in case there are some
NULL values.
Details:

Hello,

In the newest PostgreSQL(9.1.1) or lower, Index-Scan's actual time may
increase unexpectedly.

I think that this is for scanning All NULL VALUES when performing an
indexscan
even if they does not need to be scanned.

I think that the cause is here.

[src/backend/access/nbtree/nbtutils.c(L963-L996) in PostgreSQL9.1.1]
--------
if (isNull)
{
if (key->sk_flags & SK_BT_NULLS_FIRST)
{
/*
* Since NULLs are sorted before non-NULLs, we know we have
* reached the lower limit of the range of values for this
* index attr. On a backward scan, we can stop if this qual
* is one of the "must match" subset. On a forward scan,
* however, we should keep going.
*/
if ((key->sk_flags & SK_BT_REQBKWD) &&
ScanDirectionIsBackward(dir))
*continuescan = false;
}
else
{
/*
* Since NULLs are sorted after non-NULLs, we know we have
* reached the upper limit of the range of values for this
* index attr. On a forward scan, we can stop if this qual is
* one of the "must match" subset. On a backward scan,
* however, we should keep going.
*/
if ((key->sk_flags & SK_BT_REQFWD) &&
ScanDirectionIsForward(dir))
*continuescan = false;
}
/*
* In any case, this indextuple doesn't match the qual.
*/
return false;
}
---------
For example, with NULLS_LAST, GREATER THAN scan key('value > scankey' etc.),
and FORWARD SCAN conditions,
even if scan have reached a NULL value, continuescan is still true all the
time.

If it rewrites as follows, I think that this problem is solved, but how is
it?
--------

--- nbtutils.c 2011-11-02 14:10:55.000000000 +0900
+++ nbtutils.c.new 2011-11-02 14:11:38.000000000 +0900
@@ -971,8 +971,7 @@
* is one of the "must match" subset. On a
forward scan,
* however, we should keep going.
*/
- if ((key->sk_flags & SK_BT_REQBKWD) &&
- ScanDirectionIsBackward(dir))
+ if (ScanDirectionIsBackward(dir))
*continuescan = false;
}
else
@@ -984,8 +983,7 @@
* one of the "must match" subset. On a
backward scan,
* however, we should keep going.
*/
- if ((key->sk_flags & SK_BT_REQFWD) &&
- ScanDirectionIsForward(dir))
+ if (ScanDirectionIsForward(dir))
*continuescan = false;
}

---------

Regards,
Naoya Anzai