Steve Hill <steve@xxxxxxxxxxxx> 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
