explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QD5t

Settings
# exclusive inclusive rows x rows loops node
1. 1,242.652 29,242.220 ↑ 1.0 1 1

Result (cost=1,549,011.87..1,549,011.88 rows=1 width=32) (actual time=29,242.211..29,242.220 rows=1 loops=1)

  • Output: COALESCE($18, '[]'::json)
  • Buffers: shared hit=1700278 read=1
  • Functions: 103
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 10.761 ms, Inlining 18.164 ms, Optimization 705.142 ms, Emission 480.507 ms, Total 1214.573 ms
2.          

Initplan (for Result)

3. 324.072 27,999.568 ↑ 1.0 1 1

Aggregate (cost=1,549,011.85..1,549,011.86 rows=1 width=32) (actual time=27,999.558..27,999.568 rows=1 loops=1)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_1__.id), '@recipes'::text, (SubPlan 9))))))
  • Buffers: shared hit=1700278 read=1
4. 35.386 35.386 ↑ 1.0 4,107 1

Index Scan using product_pkey on product.product __local_1__ (cost=0.28..2,117.74 rows=4,107 width=1,014) (actual time=0.043..35.386 rows=4,107 loops=1)

  • Output: __local_1__.id, NULL::text, NULL::uuid, NULL::uuid, NULL::jsonb, NULL::character varying(40), NULL::character varying(40), NULL::character varying(40), NULL::numeric(18,6), NULL::character varying(40), NULL::boolean, NULL::numeric(18,6), NULL::numeric(18,6), NULL::numeric(18,6), NULL::numeric(15,4), NULL::uuid, NULL::uuid, NULL::jsonb, NULL::jsonb, NULL::jsonb, NULL::jsonb, NULL::jsonb, NULL::character varying(40), NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_1__.deleted_at, NULL::uuid, NULL::boolean, NULL::daterange, NULL::interval
  • Filter: (__local_1__.deleted_at IS NULL)
  • Buffers: shared hit=4118
5.          

SubPlan (for Aggregate)

6. 131.424 27,640.110 ↑ 1.0 1 4,107

Result (cost=376.61..376.63 rows=1 width=32) (actual time=6.725..6.730 rows=1 loops=4,107)

  • Output: json_build_object('data'::text, COALESCE($17, '[]'::json))
  • Buffers: shared hit=1696160 read=1
7.          

Initplan (for Result)

8. 306.693 27,508.686 ↑ 1.0 1 4,107

Aggregate (cost=376.59..376.60 rows=1 width=32) (actual time=6.693..6.698 rows=1 loops=4,107)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_3__.id), '@recipeGroups'::text, (SubPlan 7))))))
  • Buffers: shared hit=1696160 read=1
9. 69.819 110.889 ↓ 0.0 0 4,107

Sort (cost=8.30..8.31 rows=1 width=384) (actual time=0.026..0.027 rows=0 loops=4,107)

  • Output: __local_3__.id, NULL::uuid, NULL::character varying(40), NULL::numeric(18,6), NULL::character varying(40), NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_3__.deleted_at, NULL::uuid, NULL::daterange, NULL::jsonb
  • Sort Key: __local_3__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=9142
10. 41.070 41.070 ↓ 0.0 0 4,107

Index Scan using recipe_product_id_idx on product.recipe __local_3__ (cost=0.28..8.29 rows=1 width=384) (actual time=0.009..0.010 rows=0 loops=4,107)

  • Output: __local_3__.id, NULL::uuid, NULL::character varying(40), NULL::numeric(18,6), NULL::character varying(40), NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_3__.deleted_at, NULL::uuid, NULL::daterange, NULL::jsonb
  • Index Cond: (__local_3__.product_id = __local_1__.id)
  • Filter: ((__local_1__.deleted_at IS NOT NULL) OR (__local_3__.deleted_at IS NULL))
  • Buffers: shared hit=9142
11.          

SubPlan (for Aggregate)

12. 69.600 27,091.104 ↑ 1.0 1 928

Result (cost=368.25..368.26 rows=1 width=32) (actual time=29.188..29.193 rows=1 loops=928)

  • Output: json_build_object('data'::text, COALESCE($14, '[]'::json))
  • Buffers: shared hit=1687018 read=1
13.          

Initplan (for Result)

14. 272.192 27,021.504 ↑ 1.0 1 928

Aggregate (cost=368.23..368.24 rows=1 width=32) (actual time=29.114..29.118 rows=1 loops=928)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_5__.id), '@recipeItemsByGroupId'::text, (SubPlan 5))))))
  • Buffers: shared hit=1687018 read=1
15. 32.480 62.176 ↑ 1.0 2 928

Sort (cost=10.97..10.97 rows=2 width=173) (actual time=0.058..0.067 rows=2 loops=928)

  • Output: __local_5__.id, NULL::uuid, NULL::jsonb, NULL::integer, NULL::boolean, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_5__.deleted_at, NULL::uuid, NULL::int8range
  • Sort Key: __local_5__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2705
16. 21.344 29.696 ↑ 1.0 2 928

Bitmap Heap Scan on product.recipe_group __local_5__ (cost=4.29..10.96 rows=2 width=173) (actual time=0.023..0.032 rows=2 loops=928)

  • Output: __local_5__.id, NULL::uuid, NULL::jsonb, NULL::integer, NULL::boolean, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_5__.deleted_at, NULL::uuid, NULL::int8range
  • Recheck Cond: (__local_5__.recipe_id = __local_3__.id)
  • Filter: ((__local_3__.deleted_at IS NOT NULL) OR (__local_5__.deleted_at IS NULL))
  • Heap Blocks: exact=849
  • Buffers: shared hit=2705
17. 8.352 8.352 ↑ 1.0 2 928

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (actual time=0.009..0.009 rows=2 loops=928)

  • Index Cond: (__local_5__.recipe_id = __local_3__.id)
  • Buffers: shared hit=1856
18.          

SubPlan (for Aggregate)

19. 93.102 26,687.136 ↑ 1.0 1 1,578

Result (cost=178.59..178.60 rows=1 width=32) (actual time=16.907..16.912 rows=1 loops=1,578)

  • Output: json_build_object('data'::text, COALESCE($11, '[]'::json))
  • Buffers: shared hit=1684313 read=1
20.          

Initplan (for Result)

21. 370.207 26,594.034 ↑ 1.0 1 1,578

Aggregate (cost=178.57..178.58 rows=1 width=32) (actual time=16.848..16.853 rows=1 loops=1,578)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_7__.id), '@product'::text, (SubPlan 3))))))
  • Buffers: shared hit=1684313 read=1
22. 110.460 187.782 ↑ 1.0 5 1,578

Sort (cost=21.15..21.17 rows=5 width=242) (actual time=0.094..0.119 rows=5 loops=1,578)

  • Output: __local_7__.id, NULL::uuid, __local_7__.product_id, NULL::integer, NULL::numeric(18,6), NULL::character varying(40), NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid
  • Sort Key: __local_7__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4829
23. 61.542 77.322 ↑ 1.0 5 1,578

Bitmap Heap Scan on product.recipe_item __local_7__ (cost=4.32..21.09 rows=5 width=242) (actual time=0.024..0.049 rows=5 loops=1,578)

  • Output: __local_7__.id, NULL::uuid, __local_7__.product_id, NULL::integer, NULL::numeric(18,6), NULL::character varying(40), NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid
  • Recheck Cond: (__local_7__.group_id = __local_5__.id)
  • Filter: ((__local_5__.deleted_at IS NOT NULL) OR (__local_7__.deleted_at IS NULL))
  • Heap Blocks: exact=1665
  • Buffers: shared hit=4829
24. 15.780 15.780 ↑ 1.0 5 1,578

Bitmap Index Scan on idx_recipe_item (cost=0.00..4.32 rows=5 width=0) (actual time=0.010..0.010 rows=5 loops=1,578)

  • Index Cond: (__local_7__.group_id = __local_5__.id)
  • Buffers: shared hit=3164
25.          

SubPlan (for Aggregate)

26. 295.070 26,036.045 ↑ 1.0 1 7,765

Index Scan using product_pkey on product.product __local_8__ (cost=0.28..31.46 rows=1 width=32) (actual time=3.348..3.353 rows=1 loops=7,765)

  • Output: json_build_object('__identifiers'::text, json_build_array(__local_8__.id), '@availableModifiers'::text, (SubPlan 2))
  • Index Cond: (__local_8__.id = __local_7__.product_id)
  • Buffers: shared hit=1679484 read=1
27.          

SubPlan (for Index Scan)

28. 232.950 25,740.975 ↑ 1.0 1 7,765

Result (cost=23.14..23.15 rows=1 width=32) (actual time=3.310..3.315 rows=1 loops=7,765)

  • Output: json_build_object('data'::text, COALESCE($7, '[]'::json))
  • Buffers: shared hit=1656189 read=1
29.          

Initplan (for Result)

30. 3,354.480 25,508.025 ↑ 1.0 1 7,765

Aggregate (cost=23.12..23.13 rows=1 width=32) (actual time=3.280..3.285 rows=1 loops=7,765)

  • Output: json_agg(to_json(ROW(to_json(ROW(m.id, m.names, m.target_product_id, m.operation, COALESCE(ri.qty, m.qty), COALESCE(ri.unit, m.unit), COALESCE(ri."position", 0))), to_json(json_build_object('@id'::text, m.id)))))
  • Buffers: shared hit=1656189 read=1
31. 3,880.034 22,153.545 ↓ 32.0 32 7,765

Nested Loop Left Join (cost=1.41..23.11 rows=1 width=134) (actual time=0.088..2.853 rows=32 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, ri.qty, m.qty, ri.unit, m.unit, ri."position
  • Inner Unique: true
  • Join Filter: (ri.product_id = m.target_product_id)
  • Filter: (COALESCE(m.deleted_at, mg.deleted_at, pmg.deleted_at, p.deleted_at, r.deleted_at, rg.deleted_at, ri.deleted_at) IS NULL)
  • Buffers: shared hit=1656189 read=1
32. 3,545.364 16,539.450 ↓ 16.0 32 7,765

Nested Loop Left Join (cost=1.13..21.60 rows=2 width=187) (actual time=0.077..2.130 rows=32 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, m.deleted_at, mg.deleted_at, pmg.deleted_at, p.deleted_at, r.deleted_at, rg.id, rg.deleted_at
  • Buffers: shared hit=1250823 read=1
33. 2,761.562 10,871.000 ↓ 20.0 20 7,765

Nested Loop Left Join (cost=0.85..20.95 rows=1 width=179) (actual time=0.066..1.400 rows=20 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, m.deleted_at, mg.deleted_at, pmg.deleted_at, p.deleted_at, r.id, r.deleted_at
  • Buffers: shared hit=929468 read=1
34. 2,339.064 6,289.650 ↓ 20.0 20 7,765

Nested Loop Left Join (cost=0.57..20.62 rows=1 width=171) (actual time=0.054..0.810 rows=20 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, m.deleted_at, mg.deleted_at, pmg.deleted_at, p.id, p.deleted_at
  • Inner Unique: true
  • Buffers: shared hit=491992 read=1
35. 1,497.830 2,585.745 ↓ 20.0 20 7,765

Nested Loop (cost=0.29..16.74 rows=1 width=163) (actual time=0.043..0.333 rows=20 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, m.deleted_at, mg.deleted_at, pmg.product_id, pmg.deleted_at
  • Buffers: shared hit=37045 read=1
36. 106.440 279.540 ↓ 0.0 0 7,765

Nested Loop (cost=0.15..14.20 rows=1 width=171) (actual time=0.032..0.036 rows=0 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, m.modifier_group_id, m.deleted_at, mg.id, mg.deleted_at
  • Inner Unique: true
  • Buffers: shared hit=33290
37. 163.065 163.065 ↓ 0.0 0 7,765

Seq Scan on product.modifier m (cost=0.00..6.00 rows=1 width=147) (actual time=0.019..0.021 rows=0 loops=7,765)

  • Output: m.id, m.names, m.traits, m.created_at, m.created_by, m.updated_at, m.updated_by, m.deleted_at, m.deleted_by, m.modifier_group_id, m.target_product_id, m.operation, m.qty, m.unit, m.customer_visible
  • Filter: (m.customer_visible AND (m.target_product_id = (__local_8__.*).id))
  • Rows Removed by Filter: 160
  • Buffers: shared hit=31060
38. 10.035 10.035 ↑ 1.0 1 1,115

Index Scan using modifier_group_pkey on product.modifier_group mg (cost=0.15..8.17 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=1,115)

  • Output: mg.id, mg.legacy_id, mg.names, mg.traits, mg.created_at, mg.created_by, mg.updated_at, mg.updated_by, mg.deleted_at, mg.deleted_by
  • Index Cond: (mg.id = m.modifier_group_id)
  • Buffers: shared hit=2230
39. 808.375 808.375 ↓ 1.6 136 1,115

Index Scan using product_modifier_group_modifier_group_id_idx on product.product_modifier_group pmg (cost=0.14..1.69 rows=85 width=40) (actual time=0.011..0.725 rows=136 loops=1,115)

  • Output: pmg.id, pmg.product_id, pmg.modifier_group_id, pmg.created_at, pmg.created_by, pmg.updated_at, pmg.updated_by, pmg.deleted_at, pmg.deleted_by
  • Index Cond: (pmg.modifier_group_id = mg.id)
  • Buffers: shared hit=3755 read=1
40. 1,364.841 1,364.841 ↑ 1.0 1 151,649

Index Scan using product_pkey on product.product p (cost=0.28..3.88 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=151,649)

  • Output: p.id, p.deleted_at
  • Index Cond: (p.id = pmg.product_id)
  • Buffers: shared hit=454947
41. 1,819.788 1,819.788 ↑ 1.0 1 151,649

Index Scan using recipe_product_id_idx on product.recipe r (cost=0.28..0.32 rows=1 width=40) (actual time=0.008..0.012 rows=1 loops=151,649)

  • Output: r.product_id, r.id, r.deleted_at
  • Index Cond: (r.product_id = p.id)
  • Filter: (((r.type)::text = 'NORMAL'::text) AND ((r.valid_during IS NULL) OR (r.valid_during @> (now())::date)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=437476
42. 2,123.086 2,123.086 ↑ 2.0 1 151,649

Index Scan using recipe_group_recipe_id_idx on product.recipe_group rg (cost=0.28..0.63 rows=2 width=40) (actual time=0.007..0.014 rows=1 loops=151,649)

  • Output: rg.recipe_id, rg.id, rg.deleted_at
  • Index Cond: (rg.recipe_id = r.id)
  • Buffers: shared hit=321355
43. 1,734.061 1,734.061 ↓ 0.0 0 247,723

Index Scan using idx_recipe_item on product.recipe_item ri (cost=0.28..0.74 rows=1 width=51) (actual time=0.007..0.007 rows=0 loops=247,723)

  • Output: ri.qty, ri.unit, ri."position", ri.group_id, ri.product_id, ri.deleted_at
  • Index Cond: ((ri.group_id = rg.id) AND (ri.product_id = (__local_8__.*).id))
  • Buffers: shared hit=405366
Execution time : 29,277.667 ms