Re: B-tree performance improvements in 8.x

On Wed, Feb 08, 2006 at 06:02:08PM +0100, Dick Kniep wrote:
> On Wednesday 08 February 2006 06:18, Tom Lane wrote:
> > Dick Kniep <dick(at)kniep(dot)nl> writes:
> > > Does this also affect if you have many NULL values in the key? So testing
> > > Not is NULL would also be affected?
> >
> > IS NOT NULL isn't an indexable operation, so your question doesn't really
> > apply :-(
>
> Does this mean that if you have a table that has many rows, and 95% of the
> rows contain a NULL value for a field, that indexing will be useless, because
> it will always do a tablescan?

Well, if 95% of a table is NULL then an index scan is useless anyway.

To the more general question, IS NULL is not an indexable operator. The
btree code works on binary operators and IS NULL isn't one. I submitted
a patch a while ago to make it indexable by creating a special scankey
type for them but it didn't get much discussion[1]. No-one has come up
with any other approach yet AFAIK.

I certainly hope indexing NULLs will get in eventually one way or the
other. It's kind of odd to have to create two indexes on the same
column (one partial) just to speed up IS NULL queries.

[1] http://archives.postgresql.org/pgsql-patches/2005-09/msg00093.php

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.