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

create table projects (id int, parent_id int, is_chosen boolean);
insert into projects (id, parent_id, is_chosen) values
(1, 	null, 	false),
(2, 	1, 	true),
(3, 	2, 	true),
(4, 	1, 	false),
(5, 	4, 	false),
(6, 	1, 	false),
(7, 	6, 	true),
(8, 	1, 	true),
(9, 	8, 	false);
9 rows affected

select distinct *
from (
  select p.*
  from projects p
  join projects c on c.parent_id = p.id
  where p.is_chosen
  union all
  select c.*
  from projects p
  join projects c on c.parent_id = p.id
  where not p.is_chosen and c.is_chosen
) x


id
parent_id
is_chosen


2
1
t

8
1
t

7
6
t