Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table invoices (invoice_number integer);

insert into invoices 
values
(1),
(2),
(3),
(5),
(6),
(10),
(11);

CREATE TABLE



INSERT 0 7


select series, invoice_number
from generate_series(2, 11, 1) series
left join invoices on series = invoices.invoice_number
where invoice_number is null;


series
invoice_number


4
null

7
null

8
null

9
null



SELECT 4


select invoices.invoice_number + 1 as start, 
       min(fr.invoice_number) - 1 as stop
from invoices
left join invoices r on invoices.invoice_number = r.invoice_number - 1
left join invoices fr on invoices.invoice_number < fr.invoice_number
where r.invoice_number is null
      and fr.invoice_number is not null
group by invoices.invoice_number,
         r.invoice_number;


start
stop


4
4

7
9



SELECT 2

series	invoice_number
4	null
7	null
8	null
9	null