Postgres 16

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 t_personal 
(
  personal_id int primary key, 
  name text, 
  vorname text, 
  e_mail text
);

create table t_objekte 
(
  ob_id int, 
  obname text, 
  antragsteller int references t_personal, 
  rzusteller int references t_personal, 
  rempfaenger int references t_personal, 
  kostentraeger int references t_personal
);

insert into t_personal (personal_id, name, vorname)
values
(1, 'Dent', 'Arthur'),
(2, 'Beeblebrox', 'Zaphod'),
(3, 'Prefect', 'Ford'),
(4, 'McMillan', 'Tricia');

insert into t_objekte (ob_id, obname, antragsteller, rzusteller, rempfaenger, kostentraeger)
values
(1, 'Objekt eins', 1, 2, 3, 4),
(2, 'Objekt zwei', 2, null, null, null),
(3, 'Objekt drei', 3, null, 2, 1);


CREATE TABLE



CREATE TABLE



INSERT 0 4



INSERT 0 3


select o.ob_id, 
       o.obname, 
       a.name as antragsteller_vorname, a.name as antragsteller_name, 
       rz.name as zusteller,
       empf.name as empfaenger,
       kt.name as kostentraeger
from t_objekte o
  left join t_personal a on a.personal_id = o.antragsteller
  left join t_personal rz on rz.personal_id = o.rzusteller
  left join t_personal empf on empf.personal_id = o.rempfaenger
  left join t_personal kt on kt.personal_id = o.kostentraeger
;


ob_id
obname
antragsteller_vorname
antragsteller_name
zusteller
empfaenger
kostentraeger


1
Objekt eins
Dent
Dent
Beeblebrox
Prefect
McMillan

2
Objekt zwei
Beeblebrox
Beeblebrox
null
null
null

3
Objekt drei
Prefect
Prefect
null
Beeblebrox
Dent



SELECT 3


select o.ob_id, 
       o.obname, 
       concat_ws(' ', a.vorname, a.name) as antragsteller,
       concat_ws(' ', rz.vorname, rz.name) as zusteller,
       concat_ws(' ', empf.vorname, empf.name) as empfaenger,
       concat_ws(' ', kt.vorname, kt.name) as kostentraeger
from t_objekte o
  left join t_personal a on a.personal_id = o.antragsteller
  left join t_personal rz on rz.personal_id = o.rzusteller
  left join t_personal empf on empf.personal_id = o.rempfaenger
  left join t_personal kt on kt.personal_id = o.kostentraeger
;


ob_id
obname
antragsteller
zusteller
empfaenger
kostentraeger


1
Objekt eins
Arthur Dent
Zaphod Beeblebrox
Ford Prefect
Tricia McMillan

2
Objekt zwei
Zaphod Beeblebrox




3
Objekt drei
Ford Prefect

Zaphod Beeblebrox
Arthur Dent



SELECT 3

ob_id	obname	antragsteller_vorname	antragsteller_name	zusteller	empfaenger	kostentraeger
1	Objekt eins	Dent	Dent	Beeblebrox	Prefect	McMillan
2	Objekt zwei	Beeblebrox	Beeblebrox	null	null	null
3	Objekt drei	Prefect	Prefect	null	Beeblebrox	Dent

ob_id	obname	antragsteller	zusteller	empfaenger	kostentraeger
1	Objekt eins	Arthur Dent	Zaphod Beeblebrox	Ford Prefect	Tricia McMillan
2	Objekt zwei	Zaphod Beeblebrox
3	Objekt drei	Ford Prefect		Zaphod Beeblebrox	Arthur Dent