Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Bundle of patches

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org, Teodor Sigaev <teodor(at)sigaev(dot)ru>,Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] Bundle of patches
Date: 2006-12-04 22:05:06
Message-ID: 20061204220506.GC24581@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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(at)svana(dot)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(at)svana(dot)org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

In response to

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2006-12-04 22:12:04
Subject: Re: [HACKERS] Bundle of patches
Previous:From: Bruce MomjianDate: 2006-12-04 22:03:01
Subject: Re: postgresql roadmap for horizontal scalability?

pgsql-patches by date

Next:From: Martijn van OosterhoutDate: 2006-12-04 22:12:04
Subject: Re: [HACKERS] Bundle of patches
Previous:From: Teodor SigaevDate: 2006-12-04 20:03:37
Subject: Re: Bundle of patches

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group