Postgres 12

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 music (
    id serial primary key,
    common1 int not null, 
    common2 boolean not null
);

create table music_symphony (
    music_id integer references music(id),
    layers int not null,
    at1 text not null
);

create table music_concerto (
    music_id integer references music(id),
    lead text not null,
    strings integer not null
);

CREATE TABLE

CREATE TABLE

CREATE TABLE

insert into music (id, common1, common2)
  values (1, 1, true);
insert into music_concerto(lead, strings)
  values ('a', 5);

insert into music (id, common1, common2)
  values (2, 2, false);
insert into music_symphony (music_id, layers, at1)
  values (2, 3, 'b');
  
insert into music (id, common1, common2)
  values (3, 3, true);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

-- Fetch all symphonies
select *
from music m
join music_symphony ms on ms.music_id = m.id

id
common1
common2
music_id
layers
at1

2
2
f
2
3
b

SELECT 1