Re: Index ordering after IS NULL

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On a two-column btree index, we can constrain the first column with
> equality and read the rows in order by the 2nd column. But we can't
> constrain the first column by IS NULL and still read the rows in order by
> the 2nd column. But why not?

"x IS NULL" doesn't give rise to an EquivalenceClass, which is what
is needed to drive the deduction that the first index column isn't
affecting the result ordering.

Maybe we could extend the notion of ECs to allow that, but I'm not
too sure about how it'd work. There are already some expectations
that EC equality operators be strict, and this'd blow a large hole
in a lot of related assumptions. For example, given "x IS NULL AND
x = y", the correct deduction is not "y IS NULL", it's that the
WHERE condition is constant-FALSE.

regards, tom lane