explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pIq4 : Optimization for: Optimization for: plan #T7ht; plan #XmM5

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,489.190 5,017.572 ↑ 1.0 1 1

Result (cost=582,089.94..582,089.95 rows=1 width=32) (actual time=5,017.563..5,017.572 rows=1 loops=1)

  • Output: COALESCE($55, '[]'::json)
  • Buffers: shared hit=99543
  • Functions: 360
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 60.002 ms, Inlining 18.402 ms, Optimization 2062.870 ms, Emission 1404.423 ms, Total 3545.697 ms
2.          

Initplan (for Result)

3. 5.155 1,528.382 ↑ 1.0 1 1

Aggregate (cost=582,089.92..582,089.93 rows=1 width=32) (actual time=1,528.373..1,528.382 rows=1 loops=1)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_1__.id), 'id'::text, __local_1__.id, 'names'::text, __local_1__.names, 'position'::text, __local_1__."position", '@catalogItems'::text, (SubPlan 15), '@childCatalogs'::text, (SubPlan 32))))))
  • Buffers: shared hit=99543
4. 0.065 0.128 ↑ 1.0 1 1

Sort (cost=6.55..6.55 rows=1 width=419) (actual time=0.117..0.128 rows=1 loops=1)

  • Output: __local_1__.id, NULL::uuid, NULL::uuid, NULL::uuid, __local_1__.names, NULL::character varying(40), __local_1__."position", NULL::jsonb, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_1__.deleted_at, NULL::uuid
  • Sort Key: __local_1__."position", __local_1__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5
5. 0.063 0.063 ↑ 1.0 1 1

Seq Scan on product.catalog __local_1__ (cost=0.00..6.54 rows=1 width=419) (actual time=0.052..0.063 rows=1 loops=1)

  • Output: __local_1__.id, NULL::uuid, NULL::uuid, NULL::uuid, __local_1__.names, NULL::character varying(40), __local_1__."position", NULL::jsonb, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_1__.deleted_at, NULL::uuid
  • Filter: ((__local_1__.deleted_at IS NULL) AND (__local_1__.traits ? 'SelfServiceProductCatalog'::text))
  • Rows Removed by Filter: 136
  • Buffers: shared hit=5
6.          

SubPlan (for Aggregate)

7. 0.028 0.093 ↑ 1.0 1 1

Result (cost=41,576.86..41,576.87 rows=1 width=32) (actual time=0.084..0.093 rows=1 loops=1)

  • Output: json_build_object('data'::text, COALESCE($25, '[]'::json))
  • Buffers: shared hit=2
8.          

Initplan (for Result)

9. 0.017 0.065 ↑ 1.0 1 1

Aggregate (cost=41,576.84..41,576.85 rows=1 width=32) (actual time=0.056..0.065 rows=1 loops=1)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_3__.id), 'position'::text, __local_3__."position", '@product'::text, (SubPlan 13))))))
  • Buffers: shared hit=2
10. 0.016 0.048 ↓ 0.0 0 1

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.044..0.048 rows=0 loops=1)

  • Output: __local_3__.id, NULL::uuid, __local_3__.product_id, __local_3__."position", NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid
  • Sort Key: __local_3__."position", __local_3__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
11. 0.014 0.032 ↓ 0.0 0 1

Bitmap Heap Scan on product.catalog_item __local_3__ (cost=4.92..62.40 rows=82 width=124) (actual time=0.028..0.032 rows=0 loops=1)

  • Output: __local_3__.id, NULL::uuid, __local_3__.product_id, __local_3__."position", NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid
  • Recheck Cond: (__local_3__.catalog_id = __local_1__.id)
  • Filter: ((__local_1__.deleted_at IS NOT NULL) OR (__local_3__.deleted_at IS NULL))
  • Buffers: shared hit=2
12. 0.018 0.018 ↓ 0.0 0 1

Bitmap Index Scan on idx_catalog_item (cost=0.00..4.90 rows=82 width=0) (actual time=0.014..0.018 rows=0 loops=1)

  • Index Cond: (__local_3__.catalog_id = __local_1__.id)
  • Buffers: shared hit=2
13.          

SubPlan (for Aggregate)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using product_pkey on product.product __local_4__ (cost=0.28..506.22 rows=1 width=32) (never executed)

  • Output: json_build_object('__identifiers'::text, json_build_array(__local_4__.id), 'id'::text, __local_4__.id, 'productNumber'::text, __local_4__.product_number, 'names'::text, __local_4__.names, 'printOrderReceipt'::text, __local_4__.print_order_receipt, 'brandTexts'::text, __local_4__.brand_texts, 'foodSafetyTexts'::text, __local_4__.food_safety_texts, '@currentPrices'::text, (SubPlan 2), '@currentBundleRecipe'::text, (SubPlan 10), '@availableModifiers'::text, (SubPlan 12))
  • Index Cond: (__local_4__.id = __local_3__.product_id)
15.          

SubPlan (for Index Scan)

16. 0.000 0.000 ↓ 0.0 0

Result (cost=27.77..27.79 rows=1 width=32) (never executed)

  • Output: json_build_object('data'::text, COALESCE($1, '[]'::json))
17.          

Initplan (for Result)

18. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=27.75..27.76 rows=1 width=32) (never executed)

  • Output: json_agg(to_json(ROW(to_json(ROW(__local_6__.price_type, __local_6__.price, __local_6__.currency, __local_6__.offer_number)), to_json(json_build_object('@priceType'::text, __local_6__.price_type, '@price'::text, (__local_6__.price)::text, '@currency'::text, __local_6__.currency, '@offerNumber'::text, __local_6__.offer_number)))))
19. 0.000 0.000 ↓ 0.0 0

Function Scan on product.product_current_prices __local_6__ (cost=0.25..10.25 rows=1,000 width=128) (never executed)

  • Output: __local_6__.price_type, __local_6__.price, __local_6__.currency, __local_6__.offer_number
  • Function Call: product.product_current_prices(__local_4__.*)
20. 0.000 0.000 ↓ 0.0 0

Function Scan on product.product_current_bundle_recipe __local_8__ (cost=0.25..439.85 rows=1 width=32) (never executed)

  • Output: CASE WHEN (__local_8__.* IS NULL) THEN NULL::json ELSE json_build_object('__identifiers'::text, json_build_array(__local_8__.id), 'id'::text, __local_8__.id, 'type'::text, __local_8__.type, '@recipeGroups'::text, (SubPlan 9)) END
  • Function Call: product.product_current_bundle_recipe(__local_4__.*)
  • Filter: (NOT (__local_8__.* IS NULL))
21.          

SubPlan (for Function Scan)

22. 0.000 0.000 ↓ 0.0 0

Result (cost=439.57..439.58 rows=1 width=32) (never executed)

  • Output: json_build_object('data'::text, COALESCE($14, '[]'::json))
23.          

Initplan (for Result)

24. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=439.55..439.56 rows=1 width=32) (never executed)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_10__.id), 'names'::text, __local_10__.names, 'position'::text, __local_10__."position", 'choiceRange'::text, CASE WHEN (__local_10__.choice_range IS NULL) THEN NULL::json ELSE json_build_object('start', CASE WHEN (lower(__local_10__.choice_range) IS NULL) THEN NULL::json ELSE json_build_object('value', (lower(__local_10__.choice_range))::text, 'inclusive', lower_inc(__local_10__.choice_range)) END, 'end', CASE WHEN (upper(__local_10__.choice_range) IS NULL) THEN NULL::json ELSE json_build_object('value', (upper(__local_10__.choice_range))::text, 'inclusive', upper_inc(__local_10__.choice_range)) END) END, '@recipeItemsByGroupId'::text, (SubPlan 7))))))
25. 0.000 0.000 ↓ 0.0 0

Sort (cost=10.97..10.97 rows=2 width=158) (never executed)

  • Output: __local_10__.id, NULL::uuid, __local_10__.names, __local_10__."position", NULL::boolean, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_10__.deleted_at, NULL::uuid, __local_10__.choice_range
  • Sort Key: __local_10__."position", __local_10__.id
26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on product.recipe_group __local_10__ (cost=4.29..10.96 rows=2 width=158) (never executed)

  • Output: __local_10__.id, NULL::uuid, __local_10__.names, __local_10__."position", NULL::boolean, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_10__.deleted_at, NULL::uuid, __local_10__.choice_range
  • Recheck Cond: (__local_10__.recipe_id = __local_8__.id)
  • Filter: ((__local_8__.deleted_at IS NOT NULL) OR (__local_10__.deleted_at IS NULL))
27. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (__local_10__.recipe_id = __local_8__.id)
28.          

SubPlan (for Aggregate)

29. 0.000 0.000 ↓ 0.0 0

Result (cost=214.22..214.23 rows=1 width=32) (never executed)

  • Output: json_build_object('data'::text, COALESCE($11, '[]'::json))
30.          

Initplan (for Result)

31. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=214.20..214.21 rows=1 width=32) (never executed)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_12__.id), 'position'::text, __local_12__."position", '@product'::text, (SubPlan 5))))))
32. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.15..21.17 rows=5 width=242) (never executed)

  • Output: __local_12__.id, NULL::uuid, __local_12__.product_id, __local_12__."position", 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_12__."position", __local_12__.id
33. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on product.recipe_item __local_12__ (cost=4.32..21.09 rows=5 width=242) (never executed)

  • Output: __local_12__.id, NULL::uuid, __local_12__.product_id, __local_12__."position", 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_12__.group_id = __local_10__.id)
  • Filter: ((__local_10__.deleted_at IS NOT NULL) OR (__local_12__.deleted_at IS NULL))
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_recipe_item (cost=0.00..4.32 rows=5 width=0) (never executed)

  • Index Cond: (__local_12__.group_id = __local_10__.id)
35.          

SubPlan (for Aggregate)

36. 0.000 0.000 ↓ 0.0 0

Index Scan using product_pkey on product.product __local_13__ (cost=0.28..38.58 rows=1 width=32) (never executed)

  • Output: json_build_object('__identifiers'::text, json_build_array(__local_13__.id), 'id'::text, __local_13__.id, 'productNumber'::text, __local_13__.product_number, 'names'::text, __local_13__.names, '@availableModifiers'::text, (SubPlan 4))
  • Index Cond: (__local_13__.id = __local_12__.product_id)
37.          

SubPlan (for Index Scan)

38. 0.000 0.000 ↓ 0.0 0

Result (cost=30.27..30.28 rows=1 width=32) (never executed)

  • Output: json_build_object('data'::text, COALESCE($7, '[]'::json))
39.          

Initplan (for Result)

40. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=30.25..30.26 rows=1 width=32) (never executed)

  • 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)))))
41. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..29.93 rows=25 width=48) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=0.00..14.13 rows=24 width=48) (never executed)

  • 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
43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..13.89 rows=24 width=127) (never executed)

  • 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)
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.product_modifier_group pmg (cost=0.00..7.19 rows=1 width=16) (never executed)

  • 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_13__.*).id))
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m (cost=0.00..6.20 rows=40 width=139) (never executed)

  • 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[])))
46. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=5.24..15.68 rows=1 width=48) (never executed)

  • 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
47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.24..15.67 rows=1 width=125) (never executed)

  • Output: m_1.id, m_1.names, m_1.target_product_id, m_1.operation, ri.qty, ri.unit, ri."position
  • Inner Unique: true
48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.97..14.60 rows=1 width=141) (never executed)

  • 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
49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.83..12.82 rows=10 width=27) (never executed)

  • Output: ri.qty, ri.unit, ri."position", ri.product_id
50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.54..11.24 rows=2 width=16) (never executed)

  • Output: rg.id
51. 0.000 0.000 ↓ 0.0 0

Function Scan on product.product_current_recipe r (cost=0.25..0.26 rows=1 width=16) (never executed)

  • Output: r.id
  • Function Call: product.product_current_recipe(__local_13__.*)
  • Filter: ((r.deleted_at IS NULL) AND ((r.type)::text = 'NORMAL'::text))
52. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on product.recipe_group rg (cost=4.29..10.96 rows=2 width=32) (never executed)

  • Output: rg.recipe_id, rg.id
  • Recheck Cond: (rg.recipe_id = r.id)
  • Filter: (rg.deleted_at IS NULL)
53. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (rg.recipe_id = r.id)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using recipe_item_group_id_idx on product.recipe_item ri (cost=0.28..0.74 rows=5 width=43) (never executed)

  • 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)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using modifier_target_product_id_idx on product.modifier m_1 (cost=0.14..0.17 rows=1 width=130) (never executed)

  • 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))
56. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_1 (cost=0.27..1.06 rows=1 width=16) (never executed)

  • 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_13__.*).id) AND (pmg_1.modifier_group_id = m_1.modifier_group_id))
  • Filter: (pmg_1.deleted_at IS NULL)
57. 0.000 0.000 ↓ 0.0 0

Result (cost=30.27..30.28 rows=1 width=32) (never executed)

  • Output: json_build_object('data'::text, COALESCE($21, '[]'::json))
58.          

Initplan (for Result)

59. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=30.25..30.26 rows=1 width=32) (never executed)

  • Output: json_agg(to_json(ROW(to_json((ROW("*SELECT* 1_1".id, "*SELECT* 1_1".names, "*SELECT* 1_1".target_product_id, "*SELECT* 1_1".operation, "*SELECT* 1_1".qty, "*SELECT* 1_1".unit, "*SELECT* 1_1"."?column?"))), to_json(json_build_object('@id'::text, "*SELECT* 1_1".id)))))
60. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..29.93 rows=25 width=48) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_1 (cost=0.00..14.13 rows=24 width=48) (never executed)

  • Output: ROW("*SELECT* 1_1".id, "*SELECT* 1_1".names, "*SELECT* 1_1".target_product_id, "*SELECT* 1_1".operation, "*SELECT* 1_1".qty, "*SELECT* 1_1".unit, "*SELECT* 1_1"."?column?"), "*SELECT* 1_1".id
62. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..13.89 rows=24 width=127) (never executed)

  • Output: m_2.id, m_2.names, m_2.target_product_id, m_2.operation, m_2.qty, m_2.unit, 0
  • Join Filter: (m_2.modifier_group_id = pmg_2.modifier_group_id)
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.product_modifier_group pmg_2 (cost=0.00..7.19 rows=1 width=16) (never executed)

  • Output: pmg_2.id, pmg_2.product_id, pmg_2.modifier_group_id, pmg_2.created_at, pmg_2.created_by, pmg_2.updated_at, pmg_2.updated_by, pmg_2.deleted_at, pmg_2.deleted_by
  • Filter: ((pmg_2.deleted_at IS NULL) AND (pmg_2.product_id = (__local_4__.*).id))
64. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m_2 (cost=0.00..6.20 rows=40 width=139) (never executed)

  • Output: m_2.id, m_2.names, m_2.traits, m_2.created_at, m_2.created_by, m_2.updated_at, m_2.updated_by, m_2.deleted_at, m_2.deleted_by, m_2.modifier_group_id, m_2.target_product_id, m_2.operation, m_2.qty, m_2.unit, m_2.customer_visible
  • Filter: (m_2.customer_visible AND (m_2.deleted_at IS NULL) AND ((m_2.operation)::text = ANY ('{INFO,ADD,REPLACE}'::text[])))
65. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_1 (cost=5.24..15.68 rows=1 width=48) (never executed)

  • Output: ROW("*SELECT* 2_1".id, "*SELECT* 2_1".names, "*SELECT* 2_1".target_product_id, "*SELECT* 2_1".operation, "*SELECT* 2_1".qty, "*SELECT* 2_1".unit, "*SELECT* 2_1"."position"), "*SELECT* 2_1".id
66. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.24..15.67 rows=1 width=125) (never executed)

  • Output: m_3.id, m_3.names, m_3.target_product_id, m_3.operation, ri_1.qty, ri_1.unit, ri_1."position
  • Inner Unique: true
67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.97..14.60 rows=1 width=141) (never executed)

  • Output: m_3.id, m_3.names, m_3.target_product_id, m_3.operation, m_3.modifier_group_id, ri_1.qty, ri_1.unit, ri_1."position
68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.83..12.82 rows=10 width=27) (never executed)

  • Output: ri_1.qty, ri_1.unit, ri_1."position", ri_1.product_id
69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.54..11.24 rows=2 width=16) (never executed)

  • Output: rg_1.id
70. 0.000 0.000 ↓ 0.0 0

Function Scan on product.product_current_recipe r_1 (cost=0.25..0.26 rows=1 width=16) (never executed)

  • Output: r_1.id
  • Function Call: product.product_current_recipe(__local_4__.*)
  • Filter: ((r_1.deleted_at IS NULL) AND ((r_1.type)::text = 'NORMAL'::text))
71. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on product.recipe_group rg_1 (cost=4.29..10.96 rows=2 width=32) (never executed)

  • Output: rg_1.recipe_id, rg_1.id
  • Recheck Cond: (rg_1.recipe_id = r_1.id)
  • Filter: (rg_1.deleted_at IS NULL)
72. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (rg_1.recipe_id = r_1.id)
73. 0.000 0.000 ↓ 0.0 0

Index Scan using recipe_item_group_id_idx on product.recipe_item ri_1 (cost=0.28..0.74 rows=5 width=43) (never executed)

  • Output: ri_1.qty, ri_1.unit, ri_1."position", ri_1.group_id, ri_1.product_id
  • Index Cond: (ri_1.group_id = rg_1.id)
  • Filter: (ri_1.deleted_at IS NULL)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using modifier_target_product_id_idx on product.modifier m_3 (cost=0.14..0.17 rows=1 width=130) (never executed)

  • Output: m_3.id, m_3.names, m_3.traits, m_3.created_at, m_3.created_by, m_3.updated_at, m_3.updated_by, m_3.deleted_at, m_3.deleted_by, m_3.modifier_group_id, m_3.target_product_id, m_3.operation, m_3.qty, m_3.unit, m_3.customer_visible
  • Index Cond: (m_3.target_product_id = ri_1.product_id)
  • Filter: (m_3.customer_visible AND (m_3.deleted_at IS NULL) AND ((m_3.operation)::text = 'REMOVE'::text))
75. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_3 (cost=0.27..1.06 rows=1 width=16) (never executed)

  • Output: pmg_3.id, pmg_3.product_id, pmg_3.modifier_group_id, pmg_3.created_at, pmg_3.created_by, pmg_3.updated_at, pmg_3.updated_by, pmg_3.deleted_at, pmg_3.deleted_by
  • Index Cond: ((pmg_3.product_id = (__local_4__.*).id) AND (pmg_3.modifier_group_id = m_3.modifier_group_id))
  • Filter: (pmg_3.deleted_at IS NULL)
76. 1.504 1,523.006 ↑ 1.0 1 1

Result (cost=540,506.46..540,506.47 rows=1 width=32) (actual time=1,522.958..1,523.006 rows=1 loops=1)

  • Output: json_build_object('data'::text, COALESCE($54, '[]'::json))
  • Buffers: shared hit=99536
77.          

Initplan (for Result)

78. 5.794 1,521.502 ↑ 1.0 1 1

Aggregate (cost=540,506.44..540,506.45 rows=1 width=32) (actual time=1,521.490..1,521.502 rows=1 loops=1)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_24__.id), 'id'::text, __local_24__.id, 'names'::text, __local_24__.names, 'position'::text, __local_24__."position", '@catalogItems'::text, (SubPlan 30))))))
  • Buffers: shared hit=99536
79. 0.153 0.246 ↑ 1.0 13 1

Sort (cost=6.78..6.81 rows=13 width=419) (actual time=0.172..0.246 rows=13 loops=1)

  • Output: __local_24__.id, NULL::uuid, NULL::uuid, NULL::uuid, __local_24__.names, NULL::character varying(40), __local_24__."position", NULL::jsonb, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_24__.deleted_at, NULL::uuid
  • Sort Key: __local_24__."position", __local_24__.id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=5
80. 0.093 0.093 ↑ 1.0 13 1

Seq Scan on product.catalog __local_24__ (cost=0.00..6.54 rows=13 width=419) (actual time=0.024..0.093 rows=13 loops=1)

  • Output: __local_24__.id, NULL::uuid, NULL::uuid, NULL::uuid, __local_24__.names, NULL::character varying(40), __local_24__."position", NULL::jsonb, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_24__.deleted_at, NULL::uuid
  • Filter: (((__local_1__.deleted_at IS NOT NULL) OR (__local_24__.deleted_at IS NULL)) AND (__local_24__.parent_id = __local_1__.id))
  • Rows Removed by Filter: 124
  • Buffers: shared hit=5
81.          

SubPlan (for Aggregate)

82. 1.651 1,515.462 ↑ 1.0 1 13

Result (cost=41,576.86..41,576.87 rows=1 width=32) (actual time=116.568..116.574 rows=1 loops=13)

  • Output: json_build_object('data'::text, COALESCE($51, '[]'::json))
  • Buffers: shared hit=99531
83.          

Initplan (for Result)

84. 8.687 1,513.811 ↑ 1.0 1 13

Aggregate (cost=41,576.84..41,576.85 rows=1 width=32) (actual time=116.443..116.447 rows=1 loops=13)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_26__.id), 'position'::text, __local_26__."position", '@product'::text, (SubPlan 28))))))
  • Buffers: shared hit=99531
85. 2.093 3.380 ↑ 5.9 14 13

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.186..0.260 rows=14 loops=13)

  • Output: __local_26__.id, NULL::uuid, __local_26__.product_id, __local_26__."position", NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid
  • Sort Key: __local_26__."position", __local_26__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=42
86. 1.144 1.287 ↑ 5.9 14 13

Bitmap Heap Scan on product.catalog_item __local_26__ (cost=4.92..62.40 rows=82 width=124) (actual time=0.026..0.099 rows=14 loops=13)

  • Output: __local_26__.id, NULL::uuid, __local_26__.product_id, __local_26__."position", NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid
  • Recheck Cond: (__local_26__.catalog_id = __local_24__.id)
  • Filter: ((__local_24__.deleted_at IS NOT NULL) OR (__local_26__.deleted_at IS NULL))
  • Heap Blocks: exact=16
  • Buffers: shared hit=42
87. 0.143 0.143 ↑ 5.9 14 13

Bitmap Index Scan on idx_catalog_item (cost=0.00..4.90 rows=82 width=0) (actual time=0.011..0.011 rows=14 loops=13)

  • Index Cond: (__local_26__.catalog_id = __local_24__.id)
  • Buffers: shared hit=26
88.          

SubPlan (for Aggregate)

89. 15.040 1,501.744 ↑ 1.0 1 188

Index Scan using product_pkey on product.product __local_27__ (cost=0.28..506.22 rows=1 width=32) (actual time=7.981..7.988 rows=1 loops=188)

  • Output: json_build_object('__identifiers'::text, json_build_array(__local_27__.id), 'id'::text, __local_27__.id, 'productNumber'::text, __local_27__.product_number, 'names'::text, __local_27__.names, 'printOrderReceipt'::text, __local_27__.print_order_receipt, 'brandTexts'::text, __local_27__.brand_texts, 'foodSafetyTexts'::text, __local_27__.food_safety_texts, '@currentPrices'::text, (SubPlan 17), '@currentBundleRecipe'::text, (SubPlan 25), '@availableModifiers'::text, (SubPlan 27))
  • Index Cond: (__local_27__.id = __local_26__.product_id)
  • Buffers: shared hit=99489
90.          

SubPlan (for Index Scan)

91. 4.324 325.428 ↑ 1.0 1 188

Result (cost=27.77..27.79 rows=1 width=32) (actual time=1.727..1.731 rows=1 loops=188)

  • Output: json_build_object('data'::text, COALESCE($27, '[]'::json))
  • Buffers: shared hit=53296
92.          

Initplan (for Result)

93. 6.956 321.104 ↑ 1.0 1 188

Aggregate (cost=27.75..27.76 rows=1 width=32) (actual time=1.704..1.708 rows=1 loops=188)

  • Output: json_agg(to_json(ROW(to_json(ROW(__local_29__.price_type, __local_29__.price, __local_29__.currency, __local_29__.offer_number)), to_json(json_build_object('@priceType'::text, __local_29__.price_type, '@price'::text, (__local_29__.price)::text, '@currency'::text, __local_29__.currency, '@offerNumber'::text, __local_29__.offer_number)))))
  • Buffers: shared hit=53296
94. 314.148 314.148 ↑ 1,000.0 1 188

Function Scan on product.product_current_prices __local_29__ (cost=0.25..10.25 rows=1,000 width=128) (actual time=1.664..1.671 rows=1 loops=188)

  • Output: __local_29__.price_type, __local_29__.price, __local_29__.currency, __local_29__.offer_number
  • Function Call: product.product_current_prices(__local_27__.*)
  • Buffers: shared hit=53296
95. 73.529 1,019.900 ↓ 0.0 0 188

Function Scan on product.product_current_bundle_recipe __local_31__ (cost=0.25..439.85 rows=1 width=32) (actual time=5.423..5.425 rows=0 loops=188)

  • Output: CASE WHEN (__local_31__.* IS NULL) THEN NULL::json ELSE json_build_object('__identifiers'::text, json_build_array(__local_31__.id), 'id'::text, __local_31__.id, 'type'::text, __local_31__.type, '@recipeGroups'::text, (SubPlan 24)) END
  • Function Call: product.product_current_bundle_recipe(__local_27__.*)
  • Filter: (NOT (__local_31__.* IS NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=40067
96.          

SubPlan (for Function Scan)

97. 2.508 946.371 ↑ 1.0 1 57

Result (cost=439.57..439.58 rows=1 width=32) (actual time=16.599..16.603 rows=1 loops=57)

  • Output: json_build_object('data'::text, COALESCE($40, '[]'::json))
  • Buffers: shared hit=37419
98.          

Initplan (for Result)

99. 9.516 943.863 ↑ 1.0 1 57

Aggregate (cost=439.55..439.56 rows=1 width=32) (actual time=16.554..16.559 rows=1 loops=57)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_33__.id), 'names'::text, __local_33__.names, 'position'::text, __local_33__."position", 'choiceRange'::text, CASE WHEN (__local_33__.choice_range IS NULL) THEN NULL::json ELSE json_build_object('start', CASE WHEN (lower(__local_33__.choice_range) IS NULL) THEN NULL::json ELSE json_build_object('value', (lower(__local_33__.choice_range))::text, 'inclusive', lower_inc(__local_33__.choice_range)) END, 'end', CASE WHEN (upper(__local_33__.choice_range) IS NULL) THEN NULL::json ELSE json_build_object('value', (upper(__local_33__.choice_range))::text, 'inclusive', upper_inc(__local_33__.choice_range)) END) END, '@recipeItemsByGroupId'::text, (SubPlan 22))))))
  • Buffers: shared hit=37419
100. 2.679 4.902 ↓ 1.5 3 57

Sort (cost=10.97..10.97 rows=2 width=158) (actual time=0.071..0.086 rows=3 loops=57)

  • Output: __local_33__.id, NULL::uuid, __local_33__.names, __local_33__."position", NULL::boolean, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_33__.deleted_at, NULL::uuid, __local_33__.choice_range
  • Sort Key: __local_33__."position", __local_33__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=173
101. 1.653 2.223 ↓ 1.5 3 57

Bitmap Heap Scan on product.recipe_group __local_33__ (cost=4.29..10.96 rows=2 width=158) (actual time=0.025..0.039 rows=3 loops=57)

  • Output: __local_33__.id, NULL::uuid, __local_33__.names, __local_33__."position", NULL::boolean, NULL::timestamp with time zone, NULL::uuid, NULL::timestamp with time zone, NULL::uuid, __local_33__.deleted_at, NULL::uuid, __local_33__.choice_range
  • Recheck Cond: (__local_33__.recipe_id = __local_31__.id)
  • Filter: ((__local_31__.deleted_at IS NOT NULL) OR (__local_33__.deleted_at IS NULL))
  • Heap Blocks: exact=59
  • Buffers: shared hit=173
102. 0.570 0.570 ↓ 1.5 3 57

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (actual time=0.010..0.010 rows=3 loops=57)

  • Index Cond: (__local_33__.recipe_id = __local_31__.id)
  • Buffers: shared hit=114
103.          

SubPlan (for Aggregate)

104. 4.950 929.445 ↑ 1.0 1 165

Result (cost=214.22..214.23 rows=1 width=32) (actual time=5.628..5.633 rows=1 loops=165)

  • Output: json_build_object('data'::text, COALESCE($37, '[]'::json))
  • Buffers: shared hit=37246
105.          

Initplan (for Result)

106. 33.882 924.495 ↑ 1.0 1 165

Aggregate (cost=214.20..214.21 rows=1 width=32) (actual time=5.599..5.603 rows=1 loops=165)

  • Output: json_agg(to_json(ROW(to_json(json_build_object('__identifiers'::text, json_build_array(__local_35__.id), 'position'::text, __local_35__."position", '@product'::text, (SubPlan 20))))))
  • Buffers: shared hit=37246
107. 14.355 23.925 ↓ 1.4 7 165

Sort (cost=21.15..21.17 rows=5 width=242) (actual time=0.110..0.145 rows=7 loops=165)

  • Output: __local_35__.id, NULL::uuid, __local_35__.product_id, __local_35__."position", 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_35__."position", __local_35__.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=509
108. 7.920 9.570 ↓ 1.4 7 165

Bitmap Heap Scan on product.recipe_item __local_35__ (cost=4.32..21.09 rows=5 width=242) (actual time=0.023..0.058 rows=7 loops=165)

  • Output: __local_35__.id, NULL::uuid, __local_35__.product_id, __local_35__."position", 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_35__.group_id = __local_33__.id)
  • Filter: ((__local_33__.deleted_at IS NOT NULL) OR (__local_35__.deleted_at IS NULL))
  • Heap Blocks: exact=175
  • Buffers: shared hit=509
109. 1.650 1.650 ↓ 1.4 7 165

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

  • Index Cond: (__local_35__.group_id = __local_33__.id)
  • Buffers: shared hit=334
110.          

SubPlan (for Aggregate)

111. 47.360 866.688 ↑ 1.0 1 1,184

Index Scan using product_pkey on product.product __local_36__ (cost=0.28..38.58 rows=1 width=32) (actual time=0.726..0.732 rows=1 loops=1,184)

  • Output: json_build_object('__identifiers'::text, json_build_array(__local_36__.id), 'id'::text, __local_36__.id, 'productNumber'::text, __local_36__.product_number, 'names'::text, __local_36__.names, '@availableModifiers'::text, (SubPlan 19))
  • Index Cond: (__local_36__.id = __local_35__.product_id)
  • Buffers: shared hit=36737
112.          

SubPlan (for Index Scan)

113. 27.232 819.328 ↑ 1.0 1 1,184

Result (cost=30.27..30.28 rows=1 width=32) (actual time=0.687..0.692 rows=1 loops=1,184)

  • Output: json_build_object('data'::text, COALESCE($33, '[]'::json))
  • Buffers: shared hit=33185
114.          

Initplan (for Result)

115. 22.496 792.096 ↑ 1.0 1 1,184

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.664..0.669 rows=1 loops=1,184)

  • Output: json_agg(to_json(ROW(to_json((ROW("*SELECT* 1_2".id, "*SELECT* 1_2".names, "*SELECT* 1_2".target_product_id, "*SELECT* 1_2".operation, "*SELECT* 1_2".qty, "*SELECT* 1_2".unit, "*SELECT* 1_2"."?column?"))), to_json(json_build_object('@id'::text, "*SELECT* 1_2".id)))))
  • Buffers: shared hit=33185
116. 21.312 769.600 ↓ 0.0 0 1,184

Append (cost=0.00..29.93 rows=25 width=48) (actual time=0.607..0.650 rows=0 loops=1,184)

  • Buffers: shared hit=33185
117. 10.656 74.592 ↓ 0.0 0 1,184

Subquery Scan on *SELECT* 1_2 (cost=0.00..14.13 rows=24 width=48) (actual time=0.063..0.063 rows=0 loops=1,184)

  • Output: ROW("*SELECT* 1_2".id, "*SELECT* 1_2".names, "*SELECT* 1_2".target_product_id, "*SELECT* 1_2".operation, "*SELECT* 1_2".qty, "*SELECT* 1_2".unit, "*SELECT* 1_2"."?column?"), "*SELECT* 1_2".id
  • Buffers: shared hit=7064
118. 19.746 63.936 ↓ 0.0 0 1,184

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.054..0.054 rows=0 loops=1,184)

  • Output: m_4.id, m_4.names, m_4.target_product_id, m_4.operation, m_4.qty, m_4.unit, 0
  • Join Filter: (m_4.modifier_group_id = pmg_4.modifier_group_id)
  • Buffers: shared hit=7064
119. 31.968 31.968 ↓ 0.0 0 1,184

Seq Scan on product.product_modifier_group pmg_4 (cost=0.00..7.19 rows=1 width=16) (actual time=0.022..0.027 rows=0 loops=1,184)

  • Output: pmg_4.id, pmg_4.product_id, pmg_4.modifier_group_id, pmg_4.created_at, pmg_4.created_by, pmg_4.updated_at, pmg_4.updated_by, pmg_4.deleted_at, pmg_4.deleted_by
  • Filter: ((pmg_4.deleted_at IS NULL) AND (pmg_4.product_id = (__local_36__.*).id))
  • Rows Removed by Filter: 255
  • Buffers: shared hit=4736
120. 12.222 12.222 ↓ 0.0 0 582

Seq Scan on product.modifier m_4 (cost=0.00..6.20 rows=40 width=139) (actual time=0.021..0.021 rows=0 loops=582)

  • Output: m_4.id, m_4.names, m_4.traits, m_4.created_at, m_4.created_by, m_4.updated_at, m_4.updated_by, m_4.deleted_at, m_4.deleted_by, m_4.modifier_group_id, m_4.target_product_id, m_4.operation, m_4.qty, m_4.unit, m_4.customer_visible
  • Filter: (m_4.customer_visible AND (m_4.deleted_at IS NULL) AND ((m_4.operation)::text = ANY ('{INFO,ADD,REPLACE}'::text[])))
  • Rows Removed by Filter: 160
  • Buffers: shared hit=2328
121. 15.392 673.696 ↓ 0.0 0 1,184

Subquery Scan on *SELECT* 2_2 (cost=5.24..15.68 rows=1 width=48) (actual time=0.530..0.569 rows=0 loops=1,184)

  • Output: ROW("*SELECT* 2_2".id, "*SELECT* 2_2".names, "*SELECT* 2_2".target_product_id, "*SELECT* 2_2".operation, "*SELECT* 2_2".qty, "*SELECT* 2_2".unit, "*SELECT* 2_2"."position"), "*SELECT* 2_2".id
  • Buffers: shared hit=26121
122. 20.293 658.304 ↓ 0.0 0 1,184

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.520..0.556 rows=0 loops=1,184)

  • Output: m_5.id, m_5.names, m_5.target_product_id, m_5.operation, ri_2.qty, ri_2.unit, ri_2."position
  • Inner Unique: true
  • Buffers: shared hit=26121
123. 40.640 633.440 ↑ 1.0 1 1,184

Nested Loop (cost=4.97..14.60 rows=1 width=141) (actual time=0.507..0.535 rows=1 loops=1,184)

  • Output: m_5.id, m_5.names, m_5.target_product_id, m_5.operation, m_5.modifier_group_id, ri_2.qty, ri_2.unit, ri_2."position
  • Buffers: shared hit=24411
124. 45.800 571.872 ↑ 5.0 2 1,184

Nested Loop (cost=4.83..12.82 rows=10 width=27) (actual time=0.420..0.483 rows=2 loops=1,184)

  • Output: ri_2.qty, ri_2.unit, ri_2."position", ri_2.product_id
  • Buffers: shared hit=21061
125. 35.647 500.832 ↑ 2.0 1 1,184

Nested Loop (cost=4.54..11.24 rows=2 width=16) (actual time=0.401..0.423 rows=1 loops=1,184)

  • Output: rg_2.id
  • Buffers: shared hit=17268
126. 440.448 440.448 ↑ 1.0 1 1,184

Function Scan on product.product_current_recipe r_2 (cost=0.25..0.26 rows=1 width=16) (actual time=0.369..0.372 rows=1 loops=1,184)

  • Output: r_2.id
  • Function Call: product.product_current_recipe(__local_36__.*)
  • Filter: ((r_2.deleted_at IS NULL) AND ((r_2.type)::text = 'NORMAL'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=14688
127. 17.060 24.737 ↑ 2.0 1 853

Bitmap Heap Scan on product.recipe_group rg_2 (cost=4.29..10.96 rows=2 width=32) (actual time=0.021..0.029 rows=1 loops=853)

  • Output: rg_2.recipe_id, rg_2.id
  • Recheck Cond: (rg_2.recipe_id = r_2.id)
  • Filter: (rg_2.deleted_at IS NULL)
  • Heap Blocks: exact=874
  • Buffers: shared hit=2580
128. 7.677 7.677 ↑ 2.0 1 853

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=853)

  • Index Cond: (rg_2.recipe_id = r_2.id)
  • Buffers: shared hit=1706
129. 25.240 25.240 ↑ 2.5 2 1,262

Index Scan using recipe_item_group_id_idx on product.recipe_item ri_2 (cost=0.28..0.74 rows=5 width=43) (actual time=0.009..0.020 rows=2 loops=1,262)

  • Output: ri_2.qty, ri_2.unit, ri_2."position", ri_2.group_id, ri_2.product_id
  • Index Cond: (ri_2.group_id = rg_2.id)
  • Filter: (ri_2.deleted_at IS NULL)
  • Buffers: shared hit=3793
130. 20.928 20.928 ↓ 0.0 0 2,616

Index Scan using modifier_target_product_id_idx on product.modifier m_5 (cost=0.14..0.17 rows=1 width=130) (actual time=0.006..0.008 rows=0 loops=2,616)

  • Output: m_5.id, m_5.names, m_5.traits, m_5.created_at, m_5.created_by, m_5.updated_at, m_5.updated_by, m_5.deleted_at, m_5.deleted_by, m_5.modifier_group_id, m_5.target_product_id, m_5.operation, m_5.qty, m_5.unit, m_5.customer_visible
  • Index Cond: (m_5.target_product_id = ri_2.product_id)
  • Filter: (m_5.customer_visible AND (m_5.deleted_at IS NULL) AND ((m_5.operation)::text = 'REMOVE'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3350
131. 4.571 4.571 ↑ 1.0 1 653

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_5 (cost=0.27..1.06 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=653)

  • Output: pmg_5.id, pmg_5.product_id, pmg_5.modifier_group_id, pmg_5.created_at, pmg_5.created_by, pmg_5.updated_at, pmg_5.updated_by, pmg_5.deleted_at, pmg_5.deleted_by
  • Index Cond: ((pmg_5.product_id = (__local_36__.*).id) AND (pmg_5.modifier_group_id = m_5.modifier_group_id))
  • Filter: (pmg_5.deleted_at IS NULL)
  • Buffers: shared hit=1710
132. 4.512 141.376 ↑ 1.0 1 188

Result (cost=30.27..30.28 rows=1 width=32) (actual time=0.748..0.752 rows=1 loops=188)

  • Output: json_build_object('data'::text, COALESCE($47, '[]'::json))
  • Buffers: shared hit=5562
133.          

Initplan (for Result)

134. 4.324 136.864 ↑ 1.0 1 188

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.724..0.728 rows=1 loops=188)

  • Output: json_agg(to_json(ROW(to_json((ROW("*SELECT* 1_3".id, "*SELECT* 1_3".names, "*SELECT* 1_3".target_product_id, "*SELECT* 1_3".operation, "*SELECT* 1_3".qty, "*SELECT* 1_3".unit, "*SELECT* 1_3"."?column?"))), to_json(json_build_object('@id'::text, "*SELECT* 1_3".id)))))
  • Buffers: shared hit=5562
135. 3.760 132.540 ↑ 25.0 1 188

Append (cost=0.00..29.93 rows=25 width=48) (actual time=0.636..0.705 rows=1 loops=188)

  • Buffers: shared hit=5562
136. 1.880 11.280 ↓ 0.0 0 188

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

  • Output: ROW("*SELECT* 1_3".id, "*SELECT* 1_3".names, "*SELECT* 1_3".target_product_id, "*SELECT* 1_3".operation, "*SELECT* 1_3".qty, "*SELECT* 1_3".unit, "*SELECT* 1_3"."?column?"), "*SELECT* 1_3".id
  • Buffers: shared hit=976
137. 2.848 9.400 ↓ 0.0 0 188

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.050..0.050 rows=0 loops=188)

  • Output: m_6.id, m_6.names, m_6.target_product_id, m_6.operation, m_6.qty, m_6.unit, 0
  • Join Filter: (m_6.modifier_group_id = pmg_6.modifier_group_id)
  • Buffers: shared hit=976
138. 5.264 5.264 ↓ 0.0 0 188

Seq Scan on product.product_modifier_group pmg_6 (cost=0.00..7.19 rows=1 width=16) (actual time=0.024..0.028 rows=0 loops=188)

  • Output: pmg_6.id, pmg_6.product_id, pmg_6.modifier_group_id, pmg_6.created_at, pmg_6.created_by, pmg_6.updated_at, pmg_6.updated_by, pmg_6.deleted_at, pmg_6.deleted_by
  • Filter: ((pmg_6.deleted_at IS NULL) AND (pmg_6.product_id = (__local_27__.*).id))
  • Rows Removed by Filter: 255
  • Buffers: shared hit=752
139. 1.288 1.288 ↓ 0.0 0 56

Seq Scan on product.modifier m_6 (cost=0.00..6.20 rows=40 width=139) (actual time=0.023..0.023 rows=0 loops=56)

  • Output: m_6.id, m_6.names, m_6.traits, m_6.created_at, m_6.created_by, m_6.updated_at, m_6.updated_by, m_6.deleted_at, m_6.deleted_by, m_6.modifier_group_id, m_6.target_product_id, m_6.operation, m_6.qty, m_6.unit, m_6.customer_visible
  • Filter: (m_6.customer_visible AND (m_6.deleted_at IS NULL) AND ((m_6.operation)::text = ANY ('{INFO,ADD,REPLACE}'::text[])))
  • Rows Removed by Filter: 160
  • Buffers: shared hit=224
140. 2.820 117.500 ↑ 1.0 1 188

Subquery Scan on *SELECT* 2_3 (cost=5.24..15.68 rows=1 width=48) (actual time=0.561..0.625 rows=1 loops=188)

  • Output: ROW("*SELECT* 2_3".id, "*SELECT* 2_3".names, "*SELECT* 2_3".target_product_id, "*SELECT* 2_3".operation, "*SELECT* 2_3".qty, "*SELECT* 2_3".unit, "*SELECT* 2_3"."position"), "*SELECT* 2_3".id
  • Buffers: shared hit=4586
141. 4.053 114.680 ↑ 1.0 1 188

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.550..0.610 rows=1 loops=188)

  • Output: m_7.id, m_7.names, m_7.target_product_id, m_7.operation, ri_3.qty, ri_3.unit, ri_3."position
  • Inner Unique: true
  • Buffers: shared hit=4586
142. 7.723 109.416 ↑ 1.0 1 188

Nested Loop (cost=4.97..14.60 rows=1 width=141) (actual time=0.532..0.582 rows=1 loops=188)

  • Output: m_7.id, m_7.names, m_7.target_product_id, m_7.operation, m_7.modifier_group_id, ri_3.qty, ri_3.unit, ri_3."position
  • Buffers: shared hit=4134
143. 7.754 97.760 ↑ 5.0 2 188

Nested Loop (cost=4.83..12.82 rows=10 width=27) (actual time=0.457..0.520 rows=2 loops=188)

  • Output: ri_3.qty, ri_3.unit, ri_3."position", ri_3.product_id
  • Buffers: shared hit=3502
144. 5.048 85.728 ↑ 2.0 1 188

Nested Loop (cost=4.54..11.24 rows=2 width=16) (actual time=0.438..0.456 rows=1 loops=188)

  • Output: rg_3.id
  • Buffers: shared hit=2943
145. 77.456 77.456 ↑ 1.0 1 188

Function Scan on product.product_current_recipe r_3 (cost=0.25..0.26 rows=1 width=16) (actual time=0.409..0.412 rows=1 loops=188)

  • Output: r_3.id
  • Function Call: product.product_current_recipe(__local_27__.*)
  • Filter: ((r_3.deleted_at IS NULL) AND ((r_3.type)::text = 'NORMAL'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2630
146. 2.288 3.224 ↑ 1.0 2 104

Bitmap Heap Scan on product.recipe_group rg_3 (cost=4.29..10.96 rows=2 width=32) (actual time=0.023..0.031 rows=2 loops=104)

  • Output: rg_3.recipe_id, rg_3.id
  • Recheck Cond: (rg_3.recipe_id = r_3.id)
  • Filter: (rg_3.deleted_at IS NULL)
  • Heap Blocks: exact=105
  • Buffers: shared hit=313
147. 0.936 0.936 ↑ 1.0 2 104

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=104)

  • Index Cond: (rg_3.recipe_id = r_3.id)
  • Buffers: shared hit=208
148. 4.278 4.278 ↑ 2.5 2 186

Index Scan using recipe_item_group_id_idx on product.recipe_item ri_3 (cost=0.28..0.74 rows=5 width=43) (actual time=0.010..0.023 rows=2 loops=186)

  • Output: ri_3.qty, ri_3.unit, ri_3."position", ri_3.group_id, ri_3.product_id
  • Index Cond: (ri_3.group_id = rg_3.id)
  • Filter: (ri_3.deleted_at IS NULL)
  • Buffers: shared hit=559
149. 3.933 3.933 ↓ 0.0 0 437

Index Scan using modifier_target_product_id_idx on product.modifier m_7 (cost=0.14..0.17 rows=1 width=130) (actual time=0.007..0.009 rows=0 loops=437)

  • Output: m_7.id, m_7.names, m_7.traits, m_7.created_at, m_7.created_by, m_7.updated_at, m_7.updated_by, m_7.deleted_at, m_7.deleted_by, m_7.modifier_group_id, m_7.target_product_id, m_7.operation, m_7.qty, m_7.unit, m_7.customer_visible
  • Index Cond: (m_7.target_product_id = ri_3.product_id)
  • Filter: (m_7.customer_visible AND (m_7.deleted_at IS NULL) AND ((m_7.operation)::text = 'REMOVE'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=632
150. 1.211 1.211 ↑ 1.0 1 173

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_7 (cost=0.27..1.06 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=173)

  • Output: pmg_7.id, pmg_7.product_id, pmg_7.modifier_group_id, pmg_7.created_at, pmg_7.created_by, pmg_7.updated_at, pmg_7.updated_by, pmg_7.deleted_at, pmg_7.deleted_by
  • Index Cond: ((pmg_7.product_id = (__local_27__.*).id) AND (pmg_7.modifier_group_id = m_7.modifier_group_id))
  • Filter: (pmg_7.deleted_at IS NULL)
  • Buffers: shared hit=452
Execution time : 5,084.378 ms