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@mxu.nes.nec.co.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