Teodor Sigaev <teodor@sigaev.ru> writes:
> 3) Allow to use index for IS [NOT] NULL
> http://www.sigaev.ru/misc/indexnulls_82-0.6.gz
> Initially patch was developed by Martijn van Oosterhout <kleptog@svana.org>.
> But it's reworked and support of searching NULLS to GiST too. Patch
> adds new column named amsearchnull to pg_am. To recognize IS NULL clause
> ScanKey->sk_flags contains (SK_ISNULL & SK_INDEXFINDNULL) and
> ScanKey->sk_strategy == BTEqualStrategyNumber. For IS NOT NULL,
> ScanKey->sk_strategy == BTLessStrategyNumber. Thats because NULLs are
> treated greater than any value.
And what happens when we implement NULLS FIRST/LAST correctly? This is
really a poor choice of representation.
One thing I find questionable about this is the assumption that indexes
can support "foo IS NULL" and "foo IS NOT NULL" searches equally
conveniently. This is demonstrably false for, say, hash. (Hash could
store null keys by assigning them a fixed hashcode, say 0. Then it
would be able to handle IS NULL searches, but not IS NOT NULL, because
it can't do full-index scans.)
I am not real sure that there is any point in making IS NOT NULL an
indexable condition. We don't support <> as an indexable condition,
and no one's yelled about that. It might be best just to simplify
the patch to do IS NULL only. But if we are going to support both,
we probably have to have two pg_am flags not one.
regards, tom lane