Postgres 16

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

select version();


version


PostgreSQL 16.6 (Debian 16.6-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


create type order_status as enum
       ( 'pending'
       , 'sailing'
       , 'ordered'
       , 'complete'
       ); 
      

CREATE TYPE


create table orders( ord_id  integer generated always as identity
                   , status  order_status
                   , eta     date
                   ); 
                  

CREATE TABLE


insert into orders(status,eta)
      values ('pending', '2025-01-20') 
           , ('complete','2025-01-15') 
           , ('ordered', '2025-01-28')  
           , ('complete','2025-01-16') 
           , ('sailing', '2025-01-25'); 

INSERT 0 5


select * 
  from orders 
 order by status, eta desc;


ord_id
status
eta


1
pending
2025-01-20

5
sailing
2025-01-25

3
ordered
2025-01-28

4
complete
2025-01-16

2
complete
2025-01-15



SELECT 5


select * 
  from orders 
 order by status desc, eta desc;


ord_id
status
eta


4
complete
2025-01-16

2
complete
2025-01-15

3
ordered
2025-01-28

5
sailing
2025-01-25

1
pending
2025-01-20



SELECT 5

ord_id	status	eta
1	pending	2025-01-20
5	sailing	2025-01-25
3	ordered	2025-01-28
4	complete	2025-01-16
2	complete	2025-01-15