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.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


    create table chained_records (
       id uuid not null primary key default gen_random_uuid(),
       -- direct ancestor FK
       direct_ancestor_id uuid not null references chained_records(id),
       -- in the case, where an inserted record *is* the first of a chain,
       -- this column would reference itself (i.e. the above `id` column) 
       first_in_chain_id uuid not null references chained_records(id)
       -- snip
    );

CREATE TABLE


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, bid
from new_id ;

INSERT 0 2


select * from chained_records ;


id
direct_ancestor_id
first_in_chain_id


2df0d559-9e1e-420f-9bf4-3dbd5de01748
1713196a-55e9-4981-99cc-00c1fdf719c9
2df0d559-9e1e-420f-9bf4-3dbd5de01748

1713196a-55e9-4981-99cc-00c1fdf719c9
2df0d559-9e1e-420f-9bf4-3dbd5de01748
1713196a-55e9-4981-99cc-00c1fdf719c9



SELECT 2

id	direct_ancestor_id	first_in_chain_id
2df0d559-9e1e-420f-9bf4-3dbd5de01748	1713196a-55e9-4981-99cc-00c1fdf719c9	2df0d559-9e1e-420f-9bf4-3dbd5de01748
1713196a-55e9-4981-99cc-00c1fdf719c9	2df0d559-9e1e-420f-9bf4-3dbd5de01748	1713196a-55e9-4981-99cc-00c1fdf719c9