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 family 
(
  id int primary key, 
  name text, 
  parent_id int, 
  foreign key (parent_id) references family on delete cascade
);

CREATE TABLE


insert into family
  (id, name, parent_id)
values
  (1, 'adam', null),
  (2, 'eva', null),
  (3, 'peter', 2),
  (4, 'pan', 3),
  (5, 'arthur', 1),
  (6, 'ford', 5),
  (7, 'tricia', 4);

INSERT 0 7


select *
from family;


id
name
parent_id


1
adam
null

2
eva
null

3
peter
2

4
pan
3

5
arthur
1

6
ford
5

7
tricia
4



SELECT 7


delete from family
where name = 'peter';

DELETE 1


select *
from family;


id
name
parent_id


1
adam
null

2
eva
null

5
arthur
1

6
ford
5



SELECT 4