Re: Index not used with IS NULL

Dima Tkach <dmitry@openratings.com> writes:
> What is the problem with indexing nulls?

We do index nulls (at least in btree indexes).

What I said was

>> IS NULL is not an indexable operator.

IS NULL is not an operator at all (it's a special syntactic construct).
It has no entry in pg_operator.  Therefore it doesn't fit into the
operator class structure that describes which operators can be used
with indexes.  There are a bunch of internal structures (ScanKeys, etc)
that it wouldn't fit into, either.

> I had the similar problem some time ago, and created a custom set of
> operators as a work around (that do the same thing as <=> for numbers,
> but treat null as infinity and '=' returns true if both operand are
> null, and false if only one is)...
> It seems to work fine.

Non-strict = operators wil be a real bad idea starting in PG 7.4,
as they prevent usage of a number of hashed-aggregation optimizations.

I suggest rethinking your schema: whatever you are using NULL to
represent does not fit very well with SQL's idea of NULL semantics.
In particular, the notion that "NULL = NULL" should yield true is
going to get you in all kinds of trouble.

            regards, tom lane