Postgres 15

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 base (base_id integer primary key, base_val text not null);
insert into base values (1, 'one');

CREATE TABLE

INSERT 0 1

create table subone (subone_id serial primary key, base_id integer, subone_value text not null);
insert into subone (base_id, subone_value) values (1, 'subone-one'), (1, 'subone-two');

CREATE TABLE

INSERT 0 2

create table subtwo (subtwo_id serial primary key, base_id integer, subtwo_value text not null);
insert into subtwo (base_id, subtwo_value) values (1, 'subtwo-one'), (1, 'subtwo-two');

CREATE TABLE

INSERT 0 2

select * from base join subone using (base_id) join subtwo using (base_id); -- not good

base_id
base_val
subone_id
subone_value
subtwo_id
subtwo_value

1
one
2
subone-two
1
subtwo-one

1
one
1
subone-one
1
subtwo-one

1
one
2
subone-two
2
subtwo-two

1
one
1
subone-one
2
subtwo-two

SELECT 4

select jsonb_build_object('base_id', base_id, 'subone_arr', subone_arr, 'subtwo_arr', subtwo_arr)
from base
join lateral (select json_agg(jsonb_build_object('key', subone_value) order by subone_value) 
               from subone where subone.base_id = base.base_id) as so (subone_arr) on true
join (select base_id, json_agg(subtwo_value order by subtwo_value desc) 
               from subtwo group by base_id) as st (base_id, subtwo_arr) using (base_id)

jsonb_build_object

{"base_id": 1, "subone_arr": [{"key": "subone-one"}, {"key": "subone-two"}], "subtwo_arr": ["subtwo-two", "subtwo-one"]}

SELECT 1

base_id	base_val	subone_id	subone_value	subtwo_id	subtwo_value
1	one	2	subone-two	1	subtwo-one
1	one	1	subone-one	1	subtwo-one
1	one	2	subone-two	2	subtwo-two
1	one	1	subone-one	2	subtwo-two