explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MFRm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,171.570 7,163.016 ↑ 1.0 1 1

Result (cost=1,841,665.79..1,841,665.80 rows=1 width=32) (actual time=7,163.008..7,163.016 rows=1 loops=1)

  • Output: COALESCE($16, '[]'::json)
  • Buffers: shared hit=221447
  • Functions: 107
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 18.996 ms, Inlining 16.336 ms, Optimization 661.859 ms, Emission 487.825 ms, Total 1185.016 ms
2.          

Initplan (for Result)

3. 91.005 5,991.446 ↑ 1.0 1 1

Aggregate (cost=1,841,665.77..1,841,665.78 rows=1 width=32) (actual time=5,991.438..5,991.446 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=221447
4. 31.538 31.538 ↑ 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.055..31.538 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. 90.354 5,868.903 ↑ 1.0 1 4,107

Result (cost=447.87..447.88 rows=1 width=32) (actual time=1.425..1.429 rows=1 loops=4,107)

  • Output: json_build_object('data'::text, COALESCE($15, '[]'::json))
  • Buffers: shared hit=217329
7.          

Initplan (for Result)

8. 87.458 5,778.549 ↑ 1.0 1 4,107

Aggregate (cost=447.85..447.86 rows=1 width=32) (actual time=1.403..1.407 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=217329
9. 65.712 102.675 ↓ 0.0 0 4,107

Sort (cost=8.30..8.31 rows=1 width=384) (actual time=0.024..0.025 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. 36.963 36.963 ↓ 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.008..0.009 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. 21.344 5,588.416 ↑ 1.0 1 928

Result (cost=439.51..439.52 rows=1 width=32) (actual time=6.018..6.022 rows=1 loops=928)

  • Output: json_build_object('data'::text, COALESCE($12, '[]'::json))
  • Buffers: shared hit=208187
13.          

Initplan (for Result)

14. 57.268 5,567.072 ↑ 1.0 1 928

Aggregate (cost=439.49..439.50 rows=1 width=32) (actual time=5.994..5.999 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=208187
15. 31.552 59.392 ↑ 1.0 2 928

Sort (cost=10.97..10.97 rows=2 width=173) (actual time=0.055..0.064 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. 19.488 27.840 ↑ 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.021..0.030 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. 36.294 5,450.412 ↑ 1.0 1 1,578

Result (cost=214.22..214.23 rows=1 width=32) (actual time=3.450..3.454 rows=1 loops=1,578)

  • Output: json_build_object('data'::text, COALESCE($9, '[]'::json))
  • Buffers: shared hit=205482
20.          

Initplan (for Result)

21. 196.319 5,414.118 ↑ 1.0 1 1,578

Aggregate (cost=214.20..214.21 rows=1 width=32) (actual time=3.427..3.431 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=205482
22. 105.726 178.314 ↑ 1.0 5 1,578

Sort (cost=21.15..21.17 rows=5 width=242) (actual time=0.089..0.113 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. 58.386 72.588 ↑ 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.022..0.046 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. 14.202 14.202 ↑ 1.0 5 1,578

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

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

SubPlan (for Aggregate)

26. 248.480 5,039.485 ↑ 1.0 1 7,765

Index Scan using product_pkey on product.product __local_8__ (cost=0.28..38.58 rows=1 width=32) (actual time=0.643..0.649 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=200653
27.          

SubPlan (for Index Scan)

28. 178.595 4,791.005 ↑ 1.0 1 7,765

Result (cost=30.27..30.28 rows=1 width=32) (actual time=0.612..0.617 rows=1 loops=7,765)

  • Output: json_build_object('data'::text, COALESCE($5, '[]'::json))
  • Buffers: shared hit=177358
29.          

Initplan (for Result)

30. 147.535 4,612.410 ↑ 1.0 1 7,765

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.589..0.594 rows=1 loops=7,765)

  • Output: json_agg(to_json(ROW(to_json((ROW("*SELECT* 1".id, "*SELECT* 1".names, "*SELECT* 1".target_product_id, "*SELECT* 1".operation, "*SELECT* 1".qty, "*SELECT* 1".unit, "*SELECT* 1"."?column?"))), to_json(json_build_object('@id'::text, "*SELECT* 1".id)))))
  • Buffers: shared hit=177358
31. 139.770 4,464.875 ↓ 0.0 0 7,765

Append (cost=0.00..29.93 rows=25 width=48) (actual time=0.538..0.575 rows=0 loops=7,765)

  • Buffers: shared hit=177358
32. 69.885 465.900 ↓ 0.0 0 7,765

Subquery Scan on *SELECT* 1 (cost=0.00..14.13 rows=24 width=48) (actual time=0.060..0.060 rows=0 loops=7,765)

  • Output: ROW("*SELECT* 1".id, "*SELECT* 1".names, "*SELECT* 1".target_product_id, "*SELECT* 1".operation, "*SELECT* 1".qty, "*SELECT* 1".unit, "*SELECT* 1"."?column?"), "*SELECT* 1".id
  • Buffers: shared hit=42988
33. 128.521 396.015 ↓ 0.0 0 7,765

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.051..0.051 rows=0 loops=7,765)

  • Output: m.id, m.names, m.target_product_id, m.operation, m.qty, m.unit, 0
  • Join Filter: (m.modifier_group_id = pmg.modifier_group_id)
  • Buffers: shared hit=42988
34. 201.890 201.890 ↓ 0.0 0 7,765

Seq Scan on product.product_modifier_group pmg (cost=0.00..7.19 rows=1 width=16) (actual time=0.023..0.026 rows=0 loops=7,765)

  • 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
  • Filter: ((pmg.deleted_at IS NULL) AND (pmg.product_id = (__local_8__.*).id))
  • Rows Removed by Filter: 255
  • Buffers: shared hit=31060
35. 65.604 65.604 ↓ 0.0 0 2,982

Seq Scan on product.modifier m (cost=0.00..6.20 rows=40 width=139) (actual time=0.022..0.022 rows=0 loops=2,982)

  • 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.deleted_at IS NULL) AND ((m.operation)::text = ANY ('{INFO,ADD,REPLACE}'::text[])))
  • Rows Removed by Filter: 160
  • Buffers: shared hit=11928
36. 93.180 3,859.205 ↓ 0.0 0 7,765

Subquery Scan on *SELECT* 2 (cost=5.24..15.68 rows=1 width=48) (actual time=0.463..0.497 rows=0 loops=7,765)

  • Output: ROW("*SELECT* 2".id, "*SELECT* 2".names, "*SELECT* 2".target_product_id, "*SELECT* 2".operation, "*SELECT* 2".qty, "*SELECT* 2".unit, "*SELECT* 2"."position"), "*SELECT* 2".id
  • Buffers: shared hit=134370
37. 121.782 3,766.025 ↓ 0.0 0 7,765

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.454..0.485 rows=0 loops=7,765)

  • Output: m_1.id, m_1.names, m_1.target_product_id, m_1.operation, ri.qty, ri.unit, ri."position
  • Inner Unique: true
  • Buffers: shared hit=134370
38. 239.689 3,618.490 ↓ 0.0 0 7,765

Nested Loop (cost=4.97..14.60 rows=1 width=141) (actual time=0.440..0.466 rows=0 loops=7,765)

  • Output: m_1.id, m_1.names, m_1.target_product_id, m_1.operation, m_1.modifier_group_id, ri.qty, ri.unit, ri."position
  • Buffers: shared hit=124685
39. 262.208 3,269.065 ↑ 5.0 2 7,765

Nested Loop (cost=4.83..12.82 rows=10 width=27) (actual time=0.373..0.421 rows=2 loops=7,765)

  • Output: ri.qty, ri.unit, ri."position", ri.product_id
  • Buffers: shared hit=106852
40. 188.336 2,880.815 ↑ 2.0 1 7,765

Nested Loop (cost=4.54..11.24 rows=2 width=16) (actual time=0.355..0.371 rows=1 loops=7,765)

  • Output: rg.id
  • Buffers: shared hit=88803
41. 2,570.215 2,570.215 ↑ 1.0 1 7,765

Function Scan on product.product_current_recipe r (cost=0.25..0.26 rows=1 width=16) (actual time=0.328..0.331 rows=1 loops=7,765)

  • Output: r.id
  • Function Call: product.product_current_recipe(__local_8__.*)
  • Filter: ((r.deleted_at IS NULL) AND ((r.type)::text = 'NORMAL'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=76094
42. 84.320 122.264 ↑ 2.0 1 4,216

Bitmap Heap Scan on product.recipe_group rg (cost=4.29..10.96 rows=2 width=32) (actual time=0.022..0.029 rows=1 loops=4,216)

  • Output: rg.recipe_id, rg.id
  • Recheck Cond: (rg.recipe_id = r.id)
  • Filter: (rg.deleted_at IS NULL)
  • Heap Blocks: exact=4277
  • Buffers: shared hit=12709
43. 37.944 37.944 ↑ 2.0 1 4,216

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

  • Index Cond: (rg.recipe_id = r.id)
  • Buffers: shared hit=8432
44. 126.042 126.042 ↑ 2.5 2 6,002

Index Scan using recipe_item_group_id_idx on product.recipe_item ri (cost=0.28..0.74 rows=5 width=43) (actual time=0.009..0.021 rows=2 loops=6,002)

  • Output: ri.qty, ri.unit, ri."position", ri.group_id, ri.product_id
  • Index Cond: (ri.group_id = rg.id)
  • Filter: (ri.deleted_at IS NULL)
  • Buffers: shared hit=18049
45. 109.736 109.736 ↓ 0.0 0 13,717

Index Scan using modifier_target_product_id_idx on product.modifier m_1 (cost=0.14..0.17 rows=1 width=130) (actual time=0.007..0.008 rows=0 loops=13,717)

  • Output: m_1.id, m_1.names, m_1.traits, m_1.created_at, m_1.created_by, m_1.updated_at, m_1.updated_by, m_1.deleted_at, m_1.deleted_by, m_1.modifier_group_id, m_1.target_product_id, m_1.operation, m_1.qty, m_1.unit, m_1.customer_visible
  • Index Cond: (m_1.target_product_id = ri.product_id)
  • Filter: (m_1.customer_visible AND (m_1.deleted_at IS NULL) AND ((m_1.operation)::text = 'REMOVE'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=17833
46. 25.753 25.753 ↑ 1.0 1 3,679

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_1 (cost=0.27..1.06 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=3,679)

  • Output: pmg_1.id, pmg_1.product_id, pmg_1.modifier_group_id, pmg_1.created_at, pmg_1.created_by, pmg_1.updated_at, pmg_1.updated_by, pmg_1.deleted_at, pmg_1.deleted_by
  • Index Cond: ((pmg_1.product_id = (__local_8__.*).id) AND (pmg_1.modifier_group_id = m_1.modifier_group_id))
  • Filter: (pmg_1.deleted_at IS NULL)
  • Buffers: shared hit=9685
Execution time : 7,183.167 ms