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 detail_table (id int, pieces int, item_id int,  material_detail jsonb);

CREATE TABLE


insert into detail_table
values
(1, 10, 2, '[{"material_id":1,"pieces":10},{"material_id":2,"pieces":20},{"material_id":3,"pieces":30}]'),
(2, 20, 2, '[{"material_id":1,"pieces":40}]'),
(3, 30, 3, '[{"material_id":1,"pieces":20},{"material_id":3,"pieces":30}]');

INSERT 0 3


with pieces as (

  -- the basic aggregation for the "detail pieces"
  select dt.item_id, sum(dt.pieces) as pieces
  from detail_table dt
  where dt.item_id = 2
  group by dt.item_id

), details as (

  -- normalize the material information and aggregate the pieces per material_id
  select dt.item_id, (m.detail -> 'material_id')::int as material_id, sum((m.detail -> 'pieces')::int) as pieces
  from detail_table dt
    cross join lateral jsonb_array_elements(dt.material_detail) as m(detail)
  where dt.item_id in (select item_id from pieces) --<< don't aggregate too much    
  group by dt.item_id, material_id

), material as (

  -- now de-normalize the material aggregation back into a single JSON array
  -- for each item_id
  select item_id, jsonb_agg(to_jsonb(d) - 'item_id') as material_detail
  from details d
  group by item_id

)
-- join both results together
select p.item_id, p.pieces, m.material_detail
from pieces p
  join material m on m.item_id = p.item_id
; 


item_id
pieces
material_detail


2
30
[{"pieces": 20, "material_id": 2}, {"pieces": 50, "material_id": 1}, {"pieces": 30, "material_id": 3}]



SELECT 1