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,
       -- UNIQUE constraint needed for the following FK
       constraint transitive_closure_UQ
       unique 
           (id, first_in_chain_id),
       -- transitive closure FK
       constraint transitive_closure_FK 
       foreign key 
           (direct_ancestor_id, first_in_chain_id)
       references chained_records
           (id, first_in_chain_id)
       -- snip
    );

CREATE TABLE


with new_id (aid, bid, tid) as (
  values (gen_random_uuid(), gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id)
select tid, tid, tid from new_id
union all
select aid, bid, tid from new_id
union all
select bid, aid, tid from new_id ;

INSERT 0 3


select * from chained_records ;


id
direct_ancestor_id
first_in_chain_id


ffb73dcd-a143-40bd-bb73-3fd36a2b7e37
ffb73dcd-a143-40bd-bb73-3fd36a2b7e37
ffb73dcd-a143-40bd-bb73-3fd36a2b7e37

d16ae6c6-e13a-47ce-a8fb-005dbccd77dc
8d7e1169-3bae-400b-bc04-5ff1b0b74ad8
ffb73dcd-a143-40bd-bb73-3fd36a2b7e37

8d7e1169-3bae-400b-bc04-5ff1b0b74ad8
d16ae6c6-e13a-47ce-a8fb-005dbccd77dc
ffb73dcd-a143-40bd-bb73-3fd36a2b7e37



SELECT 3


delete from chained_records ;

DELETE 3


with new_id (aid, bid) as (
  values (gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id)
select aid, bid, aid from new_id
union all
select bid, aid, aid from new_id ;

INSERT 0 2


select * from chained_records ;


id
direct_ancestor_id
first_in_chain_id


2f769cb6-5034-45f0-8d18-dde116c4e69e
8a22602e-4610-4471-8f68-3a2293ad6964
2f769cb6-5034-45f0-8d18-dde116c4e69e

8a22602e-4610-4471-8f68-3a2293ad6964
2f769cb6-5034-45f0-8d18-dde116c4e69e
2f769cb6-5034-45f0-8d18-dde116c4e69e



SELECT 2

id	direct_ancestor_id	first_in_chain_id
ffb73dcd-a143-40bd-bb73-3fd36a2b7e37	ffb73dcd-a143-40bd-bb73-3fd36a2b7e37	ffb73dcd-a143-40bd-bb73-3fd36a2b7e37
d16ae6c6-e13a-47ce-a8fb-005dbccd77dc	8d7e1169-3bae-400b-bc04-5ff1b0b74ad8	ffb73dcd-a143-40bd-bb73-3fd36a2b7e37
8d7e1169-3bae-400b-bc04-5ff1b0b74ad8	d16ae6c6-e13a-47ce-a8fb-005dbccd77dc	ffb73dcd-a143-40bd-bb73-3fd36a2b7e37

id	direct_ancestor_id	first_in_chain_id
2f769cb6-5034-45f0-8d18-dde116c4e69e	8a22602e-4610-4471-8f68-3a2293ad6964	2f769cb6-5034-45f0-8d18-dde116c4e69e
8a22602e-4610-4471-8f68-3a2293ad6964	2f769cb6-5034-45f0-8d18-dde116c4e69e	2f769cb6-5034-45f0-8d18-dde116c4e69e