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

Sorry to keep coming back to this, but I just realized that the next para
in _bt_preprocess_keys' doco explains yet another way in which this patch
is broken:

* Note that one reason we need direction-sensitive required-key flags is
* precisely that we may not be able to eliminate redundant keys. Suppose
* we have "x > 4::int AND x > 10::bigint", and we are unable to determine
* which key is more restrictive for lack of a suitable cross-type operator.
* _bt_first will arbitrarily pick one of the keys to do the initial
* positioning with. If it picks x > 4, then the x > 10 condition will fail
* until we reach index entries > 10; but we can't stop the scan just because
* x > 10 is failing. On the other hand, if we are scanning backwards, then
* failure of either key is indeed enough to stop the scan. (In general, when
* inequality keys are present, the initial-positioning code only promises to
* position before the first possible match, not exactly at the first match,
* for a forward scan; or after the last match for a backward scan.)

This means that the patch's basic assumption, that _bt_first() leaves us
positioned on a row that satisfies all the keys, is sometimes wrong.
It may not be possible to demonstrate this bug using any standard
opclasses, because we don't ship any incomplete cross-type operator
families in core Postgres, but it's still wrong.

Offhand the most practical solution seems to be to not mark keys with
MATCH or NORECHECK or whatever you call it unless there is just a single
inequality key for the column after preprocessing. That might add a bit
more complication in bt_preprocess_keys, I fear, because it would be that
much less like the REQFWD/REQBKWD case; but it would be a localized fix.
I doubt we want to get into making _bt_first's API contract tighter.

regards, tom lane