Index ordering after IS NULL

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Index ordering after IS NULL
Date: 2022-09-10 21:28:10
Message-ID: CAMkU=1xSOGCwyJ9d+g9B7WA9qv5k_wKskhihB6uNi3TG65Pyog@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Banck 2022-09-10 21:43:11 Re: Support load balancing in libpq
Previous Message Nathan Bossart 2022-09-10 21:10:46 Re: archive modules