Index ordering after IS NULL
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? Surely the structure of the btree index
would allow for this to work.
Example:
create table if not exists j as select case when random()<0.9 then
floor(random()*10)::int end b, random() c from generate_series(1,1000000);
create index if not exists j_b_c_idx on j (b,c);
set enable_sort TO off;
explain analyze select * from j where b is null order by c limit 10;
explain analyze select * from j where b =8 order by c limit 10;
The first uses a sort despite it being disabled.
Cheers,
Jeff
On Sat, Sep 10, 2022 at 2:28 PM Jeff Janes <jeff.janes@gmail.com> wrote:
explain analyze select * from j where b is null order by c limit 10;
explain analyze select * from j where b =8 order by c limit 10;The first uses a sort despite it being disabled.
The first/is null query seems to give the result and plan you're
looking for if the query is rewritten to order by "b, c", and not just
"c".
That in itself doesn't make your complaint any less valid, of course.
You don't have to do this with the second query, so why should you
have to do it with the first?
--
Peter Geoghegan
Jeff Janes <jeff.janes@gmail.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