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

On Mon, Oct 31, 2011 at 10:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Oct 30, 2011 at 11:39 PM, Maksym Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>>> 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.  I'm not sure whether it's a bug either, but it
>> sure seems less than ideal.  I 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.  The 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.  Right 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.  Which, if memory serves, is how it used
> to be ... until I split the flag into two to fix some bug or other.  But
> 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).  So 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.  Will 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.

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