Teodor Sigaev <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend