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 table1(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,Name VARCHAR(30) NOT NULL
);
INSERT INTO table1(id,Name) VALUES (1,'ab');
INSERT INTO table1(id,Name) VALUES (2,'cd');
INSERT INTO table1(id,Name) VALUES (3,'ef');
INSERT INTO table1(id,Name) VALUES (4,'xy');
INSERT INTO table1(id,Name) VALUES (5,'zx');
INSERT INTO table1(id,Name) VALUES (6,'mn');

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE TABLE table2(
   pop INTEGER  NOT NULL  
  ,kid INTEGER  NOT NULL
);
INSERT INTO table2(pop,kid) VALUES (2,1);
INSERT INTO table2(pop,kid) VALUES (3,2);
INSERT INTO table2(pop,kid) VALUES (5,4);
INSERT INTO table2(pop,kid) VALUES (2,6);

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

select t1c.id, t1c.name, t1p.name, t1g.name
from table1 t1c
left join table2 t2c on t2c.kid = t1c.id
left join table1 t1p on t1p.id  = t2c.pop
left join table2 t2g on t2g.kid = t1p.id
left join table1 t1g on t1g.id  = t2g.pop

id
name
name
name

1
ab
cd
ef

2
cd
ef
null

4
xy
zx
null

6
mn
cd
ef

5
zx
null
null

3
ef
null
null

SELECT 6

id	name	name	name
1	ab	cd	ef
2	cd	ef	null
4	xy	zx	null
6	mn	cd	ef
5	zx	null	null
3	ef	null	null