Postgres 10

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 c (
   id   serial  not null primary key,
   name char(3) not null unique
);
insert into c (name) 
values ('c1'), ('c2'), ('c3');

CREATE TABLE



INSERT 0 3


create table d (
    id   serial not null primary key,
    c_id int not null references c (id),
    d1   int not null,
    d2   int not null
);

insert into d (
    c_id,
    d1,
    d2
)
select
    c.id,
    v.d1,
    v.d2
from ( values
    ('c1',1,2), 
    ('c2',1,2), 
    ('c3',3,4)
) v ( name,d1,d2 )
join c on c.name = v.name;

CREATE TABLE



INSERT 0 3


create table b (
    id   int     not null primary key,
    name char(1) not null unique
);
insert into b ( id, name )
values ( 1, 'b' );

CREATE TABLE



INSERT 0 1


create table b_d (
    b_id int not null references b (id),
    d_id int not null references d (id)
);
insert into b_d ( b_id, d_id )
select 
    b.id,
    d.id 
from (
    values
    ('c1',1,2), 
    ('c2',1,2), 
    ('c3',3,4)
) v ( name,d1,d2 )
join b on b.name = 'b'
join c on c.name = v.name
join d 
  on d.c_id = c.id
  and d.d1 = v.d1
  and d.d2 = v.d2;

CREATE TABLE



INSERT 0 3


select 
    b_d.b_id,
    jsonb_pretty(jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;


b_id
b


1
{
    "c1 ": {
        "d1": 1,
        "d2": 2
    },
    "c2 ": {
        "d1": 1,
        "d2": 2
    },
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}



SELECT 1