explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MW6b : Optimization for: plan #MFRm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,153.967 4,608.364 ↑ 1.0 1 1

Result (cost=2,171,560.56..2,171,560.57 rows=1 width=32) (actual time=4,608.284..4,608.364 rows=1 loops=1)

  • Output: COALESCE($16, '[]'::json)
  • Buffers: shared hit=165209
  • Functions: 109
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.357 ms, Inlining 15.462 ms, Optimization 672.598 ms, Emission 462.715 ms, Total 1162.132 ms
2.          

Initplan (for Result)

3. 91.603 3,454.397 ↑ 1.0 1 1

Aggregate (cost=2,171,560.54..2,171,560.55 rows=1 width=32) (actual time=3,454.371..3,454.397 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=165209
4. 32.017 32.017 ↑ 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.046..32.017 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 3,330.777 ↑ 1.0 1 4,107

Result (cost=528.20..528.21 rows=1 width=32) (actual time=0.806..0.811 rows=1 loops=4,107)

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

Initplan (for Result)

8. 90.196 3,240.423 ↑ 1.0 1 4,107

Aggregate (cost=528.18..528.19 rows=1 width=32) (actual time=0.785..0.789 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=161091
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. 23.200 3,047.552 ↑ 1.0 1 928

Result (cost=519.83..519.84 rows=1 width=32) (actual time=3.279..3.284 rows=1 loops=928)

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

Initplan (for Result)

14. 60.140 3,024.352 ↑ 1.0 1 928

Aggregate (cost=519.81..519.82 rows=1 width=32) (actual time=3.254..3.259 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=151949
15. 30.624 57.536 ↑ 1.0 2 928

Sort (cost=10.97..10.97 rows=2 width=173) (actual time=0.054..0.062 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. 18.560 26.912 ↑ 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.029 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. 37.872 2,906.676 ↑ 1.0 1 1,578

Result (cost=254.38..254.39 rows=1 width=32) (actual time=1.838..1.842 rows=1 loops=1,578)

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

Initplan (for Result)

21. 190.160 2,868.804 ↑ 1.0 1 1,578

Aggregate (cost=254.36..254.37 rows=1 width=32) (actual time=1.814..1.818 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=149244
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.023..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. 240.715 2,500.330 ↑ 1.0 1 7,765

Index Scan using product_pkey on product.product __local_8__ (cost=0.28..46.62 rows=1 width=32) (actual time=0.317..0.322 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=144415
27.          

SubPlan (for Index Scan)

28. 170.830 2,259.615 ↑ 1.0 1 7,765

Result (cost=38.30..38.31 rows=1 width=32) (actual time=0.287..0.291 rows=1 loops=7,765)

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

Initplan (for Result)

30. 147.535 2,088.785 ↑ 1.0 1 7,765

Aggregate (cost=38.28..38.29 rows=1 width=32) (actual time=0.264..0.269 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=121120
31. 139.770 1,941.250 ↓ 0.0 0 7,765

Append (cost=0.00..37.96 rows=25 width=48) (actual time=0.213..0.250 rows=0 loops=7,765)

  • Buffers: shared hit=121120
32. 69.885 450.370 ↓ 0.0 0 7,765

Subquery Scan on *SELECT* 1 (cost=0.00..14.13 rows=24 width=48) (actual time=0.058..0.058 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. 115.973 380.485 ↓ 0.0 0 7,765

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.049..0.049 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.022..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. 62.622 62.622 ↓ 0.0 0 2,982

Seq Scan on product.modifier m (cost=0.00..6.20 rows=40 width=139) (actual time=0.021..0.021 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. 100.945 1,351.110 ↓ 0.0 0 7,765

Subquery Scan on *SELECT* 2 (cost=5.27..23.71 rows=1 width=48) (actual time=0.140..0.174 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=78132
37. 114.017 1,250.165 ↓ 0.0 0 7,765

Nested Loop (cost=5.27..23.70 rows=1 width=125) (actual time=0.130..0.161 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=78132
38. 239.689 1,110.395 ↓ 0.0 0 7,765

Nested Loop (cost=5.00..22.64 rows=1 width=141) (actual time=0.117..0.143 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=68447
39. 260.445 760.970 ↑ 5.0 2 7,765

Nested Loop (cost=4.85..20.85 rows=10 width=27) (actual time=0.050..0.098 rows=2 loops=7,765)

  • Output: ri.qty, ri.unit, ri."position", ri.product_id
  • Buffers: shared hit=50614
40. 181.238 380.485 ↑ 2.0 1 7,765

Nested Loop (cost=4.57..19.27 rows=2 width=16) (actual time=0.033..0.049 rows=1 loops=7,765)

  • Output: rg.id
  • Buffers: shared hit=32565
41. 85.415 85.415 ↑ 1.0 1 7,765

Index Scan using recipe_product_id_idx on product.recipe r (cost=0.28..8.29 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=7,765)

  • Output: r.id
  • Index Cond: (r.product_id = (__local_8__.*).id)
  • Filter: ((r.deleted_at IS NULL) AND ((r.type)::text = 'NORMAL'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=19856
42. 80.104 113.832 ↑ 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.019..0.027 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. 33.728 33.728 ↑ 2.0 1 4,216

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

  • Index Cond: (rg.recipe_id = r.id)
  • Buffers: shared hit=8432
44. 120.040 120.040 ↑ 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.020 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.006..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 : 4,621.033 ms