Re: Index not used with IS NULL

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.
>

Yeah... I thought about it...
But, one problem was that in 7.2 partial indexes do not really work
(more precisely, do not get used)  if your query has more than one table
(Tom has given me a patch to fix that a while back, but I never got to
installing it) :-(

More importantly, if you need to make queries of both kinds (for 'is
null' and for = something), ther are two options, and both of them are
not very good:

- 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.

- create two indexes with complimetary predicates (one of IS NULL, one
for IS NOT NULL)... Well, this seems to be better, at least from the
space and performance standpoint, but I don't know how to even begin to
explain to my users that they have to write queries like

... where parent is not null and parent=1

looks pretty reidiculous to me :-)
.. and the planner does not seem to be smart enough to know to use the
index unless you mention the predicate *explicitly* in the query.

Dima