Re: [NOVICE] WHERE clause not used when index is used

I wrote:
> I believe the way to fix this would be to stop regarding SK_BT_MATCHED
> as state, and instead treat it as a scankey property identified during
> _bt_preprocess_keys, analogously to SK_BT_REQFWD/SK_BT_REQBKWD --- and,
> like those, you'd need two flags not one since the properties will be
> determined independently of knowing which direction you'll be going in.

BTW, the analogy to SK_BT_REQFWD/SK_BT_REQBKWD exposes another way in
which the patch leaves money on the table: if the leading key is "=" then
MATCHED behavior can't apply to it, but it might apply to a later key.

I'm imagining a specification like this (in the comments for
_bt_preprocess_keys, after the para starting "The output keys are marked
with flags SK_BT_REQFWD and/or SK_BT_REQBKWD ..."):

* Another property of the first attribute without an "=" key is that it may
* not be necessary to recheck its value at each index entry as we scan
* through the index. Again considering "x = 1 AND y < 4 AND z < 5", once we
* have positioned to an entry satisfying those keys, it is unnecessary to
* recheck "y < 4" as we scan forward, at least so long as the index's y
* value is not NULL. Every later row with x=1 must have y>=4; though we
* can't make any similar statement about z. Similarly, a key like "y > 4"
* need not be rechecked in a backwards scan. We mark appropriate keys with
* flags SK_BT_NORECHECK_FWD or SK_BT_NORECHECK_BKWD to indicate that _bt_next
* can skip checking those keys (at non-null index entries) when scanning in
* the indicated direction.

I'm also wondering whether it'd be worth taking more care about the
handling of index entries containing some null columns. Right now,
the presence of any nulls disables the MATCH improvement, but it would
still apply if the null(s) are in lower-order columns. I'm not sure
if that case comes up often enough to justify checking the flag bit
twice per iteration, but it might.

regards, tom lane