Execution time: 0.245ms
Planning time: 1.02ms
#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

InitPlan 3 (returns $2)

Aggregate

Seq Scan

on public.person
SubPlan 2

Aggregate

Seq Scan

on public.car
SubPlan 1

Aggregate

Seq Scan

on public.wheel
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
  ) 
);
Table Count Time
public.car 1 0.01ms 4%
#5 Seq Scan 0.01ms 4%
public.wheel 1 0.01ms 4%
#7 Seq Scan 0.01ms 4%
public.person 1 0.009ms 4%
#3 Seq Scan 0.009ms 4%
Function Count Time
No function used
Node Type Count Time
Aggregate 3 0.08ms 33%
#6 Aggregate 0.044ms 18%
#4 Aggregate 0.018ms 7%
#2 Aggregate 0.018ms 7%
Seq Scan 3 0.029ms 12%
#7 Seq Scan 0.01ms 4%
#5 Seq Scan 0.01ms 4%
#3 Seq Scan 0.009ms 4%
Result 1 -1d -1h -42%
#1 Result -1d -1h -42%
Index Count Time
No index used