Thread: Re: [PATCHES] Bundle of patches
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
> And what happens when we implement NULLS FIRST/LAST correctly? This is
> really a poor choice of representation.
If it's just appending of indexscan's it's not a problem...
>
> 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.)
Is there a guarantee that hash value of some not-null keys doesn't equal to
special hash code?
>
> the patch to do IS NULL only. But if we are going areto support both,
> we probably have to have two pg_am flags not one.
GiST isn't effective with single NOT NULL condition ... So, using two flags may
be useful.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
On Mon, Dec 04, 2006 at 01:35:21PM -0500, Tom Lane wrote: > > 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. > > 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. Originally I didn't have IS NOT NULL but added it because it was easy and someone suggested a use case: for indexed columns that have a lot of nulls, it allows you to create an index scan that stops as soon as it reaches the first null entry. This is useful for the NULL FIRST/LAST optimisation for example. You're right, it doesn't work for hash indexes, but you can't do full scans on them anyway, so it's not terribly important. I'd say that ordered indexes like b-tree are the only ones that would get any benefit from IS NOT NULL. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.