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

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.

regards, tom lane