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 product (product_id int, product_name text);
create table stock (product_id int, location text, quantity int);

insert into product values (1, 'Product 1');
insert into stock values (1, 'a', 10), (1, 'b', 20);


create table sites (site_id int, location text);
insert into sites values (1, 'a'),(1, 'b'),(1, 'c'),(1, 'd');
1 rows affected

2 rows affected

4 rows affected

select 
    dk.product_id, 
    dk.product_name,
    dr.location,
    dr.quantity
from 
    product dk 
left join 
    stock dr 
    on dr.product_id = dk.product_id


product_id
product_name
location
quantity


1
Product 1
a
10

1
Product 1
b
20


select 
    dk.product_id,
    dk.product_name,
    (
    select 
        json_agg(
            json_build_object('location', s.location,
                              'quantity', coalesce(dr.quantity, 0)))::jsonb stock
    from
        sites s
    left join
        stock dr
        on dr.location = s.location
        and dr.product_id = dk.product_id
    where
        s.site_id = 1
    ) stock
from
    product dk


product_id
product_name
stock


1
Product 1
[{"location": "a", "quantity": 10}, {"location": "b", "quantity": 20}, {"location": "c", "quantity": 0}, {"location": "d", "quantity": 0}]