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 person
(
  id integer primary key,
  nachname varchar(100),
  vorname varchar(100)
);
create table adresse 
(
  id integer primary key,
  person_id integer not null references person,
  plz varchar(5),
  wohnort text,
  strasse text,
  hausnr varchar(25),
  land text
);
insert into person (id, nachname, vorname)values 
(1, 'Grabowsky','Karl Heinz'),
(2, 'Lustig', 'Peter');
2 rows affected

insert into adresse  
values
(1,1,'55801', 'Bad Salzuflen','Soester Weg','51','Deutschland'),
(2,1,'15098','Emsbueren','Ackerstraße','54b','Deutschland'),
(3,2,'12345','Im Wald','Bauwagen','1a','Deutschland');
3 rows affected

create or replace view person_und_adressen
as
select p.id, p.vorname, p.nachname, to_jsonb(p)||jsonb_build_object('adressen', (jsonb_agg(a) - 'person_id')) as person_json
from person p 
  join adresse a on a.person_id = p.id
group by p.id;
select *
from person_und_adressen
order by id;


id
vorname
nachname
person_json


1
Karl Heinz
Grabowsky
{"id": 1, "vorname": "Karl Heinz", "adressen": [{"id": 1, "plz": "55801", "land": "Deutschland", "hausnr": "51", "strasse": "Soester Weg", "wohnort": "Bad Salzuflen", "person_id": 1}, {"id": 2, "plz": "15098", "land": "Deutschland", "hausnr": "54b", "strasse": "Ackerstraße", "wohnort": "Emsbueren", "person_id": 1}], "nachname": "Grabowsky"}

2
Peter
Lustig
{"id": 2, "vorname": "Peter", "adressen": [{"id": 3, "plz": "12345", "land": "Deutschland", "hausnr": "1a", "strasse": "Bauwagen", "wohnort": "Im Wald", "person_id": 2}], "nachname": "Lustig"}

id	vorname	nachname	person_json
1	Karl Heinz	Grabowsky	{"id": 1, "vorname": "Karl Heinz", "adressen": [{"id": 1, "plz": "55801", "land": "Deutschland", "hausnr": "51", "strasse": "Soester Weg", "wohnort": "Bad Salzuflen", "person_id": 1}, {"id": 2, "plz": "15098", "land": "Deutschland", "hausnr": "54b", "strasse": "Ackerstraße", "wohnort": "Emsbueren", "person_id": 1}], "nachname": "Grabowsky"}
2	Peter	Lustig	{"id": 2, "vorname": "Peter", "adressen": [{"id": 3, "plz": "12345", "land": "Deutschland", "hausnr": "1a", "strasse": "Bauwagen", "wohnort": "Im Wald", "person_id": 2}], "nachname": "Lustig"}