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

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


    create table chained_records (
       id uuid not null primary key default gen_random_uuid(),
       -- direct ancestor
       direct_ancestor_id uuid not null,
       -- top ancestor
       first_in_chain_id uuid not null,
       level int not null,
       ancestor_level int not null GENERATED ALWAYS AS (CASE WHEN level = 0 THEN 0 ELSE level - 1 END) STORED,
       -- UNIQUE constraint needed for the following FK
       constraint transitive_closure_UQ
       unique 
           (id, first_in_chain_id, level),
       -- transitive closure FK
       constraint transitive_closure_FK 
       foreign key 
           (direct_ancestor_id, first_in_chain_id, ancestor_level)
       references chained_records
           (id, first_in_chain_id, level)
       -- snip
    );

CREATE TABLE


with new_id (aid, bid, cid, did) as (
  values (gen_random_uuid(), gen_random_uuid(), gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id, level)
select aid, aid, aid, 0 from new_id
union all
select bid, aid, aid, 1 from new_id
union all
select cid, bid, aid, 2 from new_id
union all
select did, cid, aid, 3 from new_id;

INSERT 0 4


select * from chained_records ;
delete from chained_records ;


id
direct_ancestor_id
first_in_chain_id
level
ancestor_level


f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
0
0

56643607-fb5e-42e7-9fc3-8d699d27e6eb
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
1
0

2782aed3-6518-40af-b39f-412309715142
56643607-fb5e-42e7-9fc3-8d699d27e6eb
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
2
1

2b20c21c-ae4b-41f3-b62e-36bd28ef1516
2782aed3-6518-40af-b39f-412309715142
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea
3
2



SELECT 4



DELETE 4


select * from chained_records ;


id
direct_ancestor_id
first_in_chain_id
level
ancestor_level



SELECT 0

id	direct_ancestor_id	first_in_chain_id	level	ancestor_level
f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	0	0
56643607-fb5e-42e7-9fc3-8d699d27e6eb	f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	1	0
2782aed3-6518-40af-b39f-412309715142	56643607-fb5e-42e7-9fc3-8d699d27e6eb	f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	2	1
2b20c21c-ae4b-41f3-b62e-36bd28ef1516	2782aed3-6518-40af-b39f-412309715142	f6f4e818-6eb7-4d3b-9f14-badc5f7f53ea	3	2