Re: Index not used with IS NULL

On Sun, 16 Feb 2003, Dima Tkach wrote:

> Stephan Szabo wrote:
> > On Sat, 15 Feb 2003, Dima Tkach wrote:
> >
> >
> >>It would be a lot nicer if the default operators could handle that...
> >>Why can it not be done?
> >
> >
> > Jumping in... I usually use a partial index as a workaround.  Postgresql
> > will look at a partial index whose condition is IS NULL for queries of col
> > IS NULL.
> >
>
> - create two indexes, one with predicate, and one without predicate - is
> a waste of space, because all the rows with nulls get indexed twice. The
>   space may not be such an important consideration by itself, but, when
> the table is huge and heavily being updated, the overhead of having to
> keep both indexes in synch becomes significant.

Yes, this solution does double index the NULLs, but if you have alot of
NULLs you probably should be doing a seqscan to find them anyway and don't
need the index.  High update frequency costs you the NULL check, which is
a little annoying, and if you've got a small number of NULL rows or the
data is clustered in some fashion (so that the index is a win) that have
lots of updates this may become significant.