Re: Odd behaviour with indexes for NULLs

Steve Hill <steve(at)opendium(dot)com> writes:
> In this case, rather than using the index to order the records, it uses
> a separate sorting step, which is considerably slower. The only
> difference between the two queries is that the one that doesn't use the
> index for sorting is looking for a NULL realm column, instead of a realm
> column which contains some text.

You're out of luck on that. It's true that "x IS NULL" can be matched
to a btree index, but that's a special-case hack that is not tied into
the logic that matches equivalence-class operators to ORDER BY.

> However, this other query does
> not work as I would expect:

> EXPLAIN ANALYZE SELECT * FROM sessions WHERE realm IS NOT NULL AND eui64
> = '9e:cc:b9:ff:fe:5d:28:0a' ORDER BY update_time DESC LIMIT 1;

Again, IS NOT NULL is a special case that isn't tied into as many
places as you might wish.

Generally speaking, using NULL as if it were a real, searchable value
is a bad idea that is going to lead you to grief. There are semantic
gotchas with that, inherent to SQL not just PG's fault, because of
the way they've overloaded NULL to mean a few different things.
Because of that, we've also not worked that hard on making the planner
super-intelligent about IS [NOT] NULL. I'm not even 100% sure that
we *could* safely optimize IS NULL in the same way as a normal
equivalence-class operator is handled; but even if it's semantically
sound, it hasn't gotten done.

You might think about reserving some value such as "*UNASSIGNED*"
for your realm column, and using that rather than NULL. Grotty,
I know, but less likely to have unforeseen gotchas.

regards, tom lane