The following bug has been logged online:
Bug reference: 6283
Logged by: Naoya Anzai
Email address: anzai-na...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
