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
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 |