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.