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