#1 | Result | |
└ InitPlan 3 (returns $2) | ||
#2 | Aggregate | |
#3 | ├ Seq Scan | |
└ SubPlan 2 | ||
#4 | Aggregate | |
#5 | ├ Seq Scan | |
└ SubPlan 1 | ||
#6 | Aggregate | |
#7 | └ Seq Scan |
Result (cost=5.32..5.33 rows=1 width=32) (actual time=0.114..0.116 rows=1 loops=1)
" Output: json_build_object('persons', $2)"
Buffers: shared hit=5
InitPlan 3 (returns $2)
-> Aggregate (cost=5.31..5.32 rows=1 width=32) (actual time=0.107..0.109 rows=1 loops=1)
" Output: json_agg(json_build_object('person_id', person.id, 'cars', (SubPlan 2)))"
Buffers: shared hit=5
-> Seq Scan on public.person (cost=0.00..1.02 rows=2 width=4) (actual time=0.008..0.009 rows=2 loops=1)
Output: person.id, person.name
Buffers: shared hit=1
SubPlan 2
-> Aggregate (cost=2.13..2.14 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=2)
" Output: json_agg(json_build_object('car_id', car.id, 'wheels', (SubPlan 1)))"
Buffers: shared hit=4
-> Seq Scan on public.car (cost=0.00..1.04 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=2)
Output: car.id, car.type, car.personid
Filter: (car.id = person.id)
Rows Removed by Filter: 2
Buffers: shared hit=2
SubPlan 1
-> Aggregate (cost=1.07..1.08 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=2)
" Output: json_agg(json_build_object('wheel_id', wheel.id, 'whichone', wheel.whichone, 'serialnumber', wheel.serialnumber, 'car_id', wheel.carid))"
Buffers: shared hit=2
-> Seq Scan on public.wheel (cost=0.00..1.06 rows=2 width=17) (actual time=0.003..0.005 rows=2 loops=2)
Output: wheel.id, wheel.whichone, wheel.serialnumber, wheel.carid
Filter: (wheel.carid = car.id)
Rows Removed by Filter: 3
Buffers: shared hit=2
Planning:
Buffers: shared hit=39
Planning Time: 1.021 ms
Execution Time: 0.245 ms
explain (ANALYSE, costs, buffers, VERBOSE)
select json_build_object(
'persons', (
SELECT json_agg(
json_build_object(
'person_id',id,
'cars', (
SELECT json_agg(
json_build_object(
'car_id', car.id,
'wheels', (
SELECT json_agg(
json_build_object(
'wheel_id', wheel.id,
'whichone', wheel.whichone,
'serialnumber', wheel.serialnumber,
'car_id', wheel.carid
)
)
FROM wheel WHERE wheel.carid = car.id
)
)
) FROM car WHERE id = person.id
)
)
) FROM person
)
);