Re: BUG #6278: Index scans on '>' condition on field with many NULLS

On Mon, Oct 31, 2011 at 10:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Sun, Oct 30, 2011 at 11:39 PM, Maksym Boguk <maxim.boguk@gmail.com> w=
rote:
>>> Seems index scan cannot stop after finding first NULL during scan on '>'
>>> condition, and doing scan through all 90% nulls in table.
>
>> I can reproduce this. =A0I'm not sure whether it's a bug either, but it
>> sure seems less than ideal. =A0I suppose the problem is that we are
>> generating an index scan that starts at 0.9999 and runs through the
>> end of the index, rather than stopping when it hits the first NULL.
>
> I poked at this a bit last night. =A0The reason it's happening is that the
> ">" key is only marked SK_BT_REQBKWD, not SK_BT_REQFWD, so _bt_checkkeys
> doesn't think it can stop when it hits the NULLs. =A0Right at the moment
> it seems like we could mark that key with both flags, which leads to the
> conclusion that two flags are unnecessary and we could get by with only
> one direction-independent flag. =A0Which, if memory serves, is how it used
> to be ... until I split the flag into two to fix some bug or other. =A0But
> the regression tests still pass if you make _bt_mark_scankey_required
> mark any required key with both flags (which is the zeroth-order version
> of recombining them). =A0So either my analysis was wrong at the time,
> or some later change has eliminated the need for two flags, or the
> regression tests aren't covering the problematic case. =A0Will investigate
> further once I've absorbed some caffeine.

I know almost nothing about this code, but it seems both flags were
introduced in commit 7ccaf13a06b8e1f70b26ab049fdb4f8c8dece3f8.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company