Postgres 11

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 t1 (
  a int not null primary key
);
create table t2 (
  b serial primary key,
  a int not null 
    references t1(a)
    on delete cascade
);
create table t3 (
  c serial primary key,
  b int not null
    references t2(b)
    on delete cascade
);
create table t4 (
  d serial primary key,
  c int not null default 3
    references t3(c)
    on delete cascade
);
alter table t4
  add constraint fk_t4c_t1a
  foreign key (c)
  references t1(a)
  on delete set default; 
 
insert into t1 (a) values (1),(2),(3),(4),(5);
insert into t2 (a) values (1),(2),(3),(4);
insert into t3 (b) values (1),(2),(3);
insert into t4 (c) values (1),(2);
5 rows affected

4 rows affected

3 rows affected

2 rows affected

select v.v, 
       t1.a, 
       t2.b, 
       t3.c, 
       t4.d
from (values (1),(2),(3),(4),(5)) v (v)
left join t4 on t4.d = v.v
left join t3 on t3.c = v.v
left join t2 on t2.b = v.v
left join t1 on t1.a = v.v;


v
a
b
c
d


1
1
1
1
1

2
2
2
2
2

3
3
3
3
null

4
4
4
null
null

5
5
null
null
null


delete from t1 
where a = 1;
1 rows affected

select v.v, 
       t1.a, 
       t2.b, 
       t3.c, 
       t4.d
from (values (1),(2),(3),(4),(5)) v (v)
left join t4 on t4.d = v.v
left join t3 on t3.c = v.v
left join t2 on t2.b = v.v
left join t1 on t1.a = v.v;


v
a
b
c
d


1
null
null
null
1

2
2
2
2
2

3
3
3
3
null

4
4
4
null
null

5
5
null
null
null

v	a	b	c	d
1	1	1	1	1
2	2	2	2	2
3	3	3	3	null
4	4	4	null	null
5	5	null	null	null

v	a	b	c	d
1	null	null	null	1
2	2	2	2	2
3	3	3	3	null
4	4	4	null	null
5	5	null	null	null