explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XmM5 : Optimization for: plan #T7ht

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,440.536 4,817.864 ↑ 1.0 1 1

Result (cost=582,124.38..582,124.39 rows=1 width=32) (actual time=4,817.841..4,817.864 rows=1 loops=1)

  • Output: COALESCE($55, '[]'::json)
  • Buffers: shared hit=99537
  • Functions: 366
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 37.670 ms, Inlining 18.535 ms, Optimization 2009.641 ms, Emission 1400.285 ms, Total 3466.131 ms
2.          

Initplan (for Result)

3. 5.636 1,377.328 ↑ 1.0 1 1

Aggregate (cost=582,124.36..582,124.37 rows=1 width=32) (actual time=1,377.320..1,377.328 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=99537
4. 0.072 0.140 ↑ 1.0 1 1

Sort (cost=6.55..6.55 rows=1 width=419) (actual time=0.131..0.140 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.068 0.068 ↑ 1.0 1 1

Seq Scan on product.catalog __local_1__ (cost=0.00..6.54 rows=1 width=419) (actual time=0.057..0.068 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.030 0.099 ↑ 1.0 1 1

Result (cost=41,579.32..41,579.33 rows=1 width=32) (actual time=0.090..0.099 rows=1 loops=1)

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

Initplan (for Result)

9. 0.019 0.069 ↑ 1.0 1 1

Aggregate (cost=41,579.30..41,579.31 rows=1 width=32) (actual time=0.060..0.069 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.017 0.050 ↓ 0.0 0 1

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.046..0.050 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.015 0.033 ↓ 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.030..0.033 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.015..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.25 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_at_time __local_8__ (cost=0.25..439.88 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_at_time(__local_4__.*, now())
  • Filter: (NOT (__local_8__.* IS NULL))
21.          

SubPlan (for Function Scan)

22. 0.000 0.000 ↓ 0.0 0

Result (cost=439.60..439.61 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.58..439.59 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.23..214.24 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.21..214.22 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.59 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.61 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.55..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_at_time r (cost=0.25..0.27 rows=1 width=16) (never executed)

  • Output: r.id
  • Function Call: product.product_current_recipe_at_time(__local_13__.*, now())
  • 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.61 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.55..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_at_time r_1 (cost=0.25..0.27 rows=1 width=16) (never executed)

  • Output: r_1.id
  • Function Call: product.product_current_recipe_at_time(__local_4__.*, now())
  • 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.364 1,371.453 ↑ 1.0 1 1

Result (cost=540,538.44..540,538.45 rows=1 width=32) (actual time=1,371.445..1,371.453 rows=1 loops=1)

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

Initplan (for Result)

78. 5.149 1,370.089 ↑ 1.0 1 1

Aggregate (cost=540,538.42..540,538.43 rows=1 width=32) (actual time=1,370.076..1,370.089 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=99530
79. 0.174 0.382 ↑ 1.0 13 1

Sort (cost=6.78..6.81 rows=13 width=419) (actual time=0.295..0.382 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.208 0.208 ↑ 1.0 13 1

Seq Scan on product.catalog __local_24__ (cost=0.00..6.54 rows=13 width=419) (actual time=0.134..0.208 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.443 1,364.558 ↑ 1.0 1 13

Result (cost=41,579.32..41,579.33 rows=1 width=32) (actual time=104.956..104.966 rows=1 loops=13)

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

Initplan (for Result)

84. 8.741 1,363.115 ↑ 1.0 1 13

Aggregate (cost=41,579.30..41,579.31 rows=1 width=32) (actual time=104.851..104.855 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=99525
85. 2.171 3.406 ↑ 5.9 14 13

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.186..0.262 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.079 1.235 ↑ 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.029..0.095 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.156 0.156 ↑ 5.9 14 13

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

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

SubPlan (for Aggregate)

89. 14.288 1,350.968 ↑ 1.0 1 188

Index Scan using product_pkey on product.product __local_27__ (cost=0.28..506.25 rows=1 width=32) (actual time=7.180..7.186 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=99483
90.          

SubPlan (for Index Scan)

91. 3.948 326.180 ↑ 1.0 1 188

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

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

Initplan (for Result)

93. 7.144 322.232 ↑ 1.0 1 188

Aggregate (cost=27.75..27.76 rows=1 width=32) (actual time=1.709..1.714 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. 315.088 315.088 ↑ 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.669..1.676 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. 52.994 887.360 ↓ 0.0 0 188

Function Scan on product.product_current_bundle_recipe_at_time __local_31__ (cost=0.25..439.88 rows=1 width=32) (actual time=4.718..4.720 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_at_time(__local_27__.*, now())
  • Filter: (NOT (__local_31__.* IS NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=40061
96.          

SubPlan (for Function Scan)

97. 2.337 834.366 ↑ 1.0 1 57

Result (cost=439.60..439.61 rows=1 width=32) (actual time=14.633..14.638 rows=1 loops=57)

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

Initplan (for Result)

99. 10.098 832.029 ↑ 1.0 1 57

Aggregate (cost=439.58..439.59 rows=1 width=32) (actual time=14.591..14.597 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.736 5.016 ↓ 1.5 3 57

Sort (cost=10.97..10.97 rows=2 width=158) (actual time=0.073..0.088 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.710 2.280 ↓ 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.024..0.040 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. 5.115 816.915 ↑ 1.0 1 165

Result (cost=214.23..214.24 rows=1 width=32) (actual time=4.946..4.951 rows=1 loops=165)

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

Initplan (for Result)

106. 33.502 811.800 ↑ 1.0 1 165

Aggregate (cost=214.21..214.22 rows=1 width=32) (actual time=4.915..4.920 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.685 24.090 ↓ 1.4 7 165

Sort (cost=21.15..21.17 rows=5 width=242) (actual time=0.111..0.146 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.590 9.405 ↓ 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.024..0.057 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.815 1.815 ↓ 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.011 rows=7 loops=165)

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

SubPlan (for Aggregate)

111. 46.176 754.208 ↑ 1.0 1 1,184

Index Scan using product_pkey on product.product __local_36__ (cost=0.28..38.59 rows=1 width=32) (actual time=0.631..0.637 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 708.032 ↑ 1.0 1 1,184

Result (cost=30.27..30.28 rows=1 width=32) (actual time=0.593..0.598 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 680.800 ↑ 1.0 1 1,184

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.570..0.575 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 658.304 ↓ 0.0 0 1,184

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

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

Subquery Scan on *SELECT* 1_2 (cost=0.00..14.13 rows=24 width=48) (actual time=0.062..0.062 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 62.752 ↓ 0.0 0 1,184

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.053..0.053 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. 30.784 30.784 ↓ 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.021..0.026 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 563.584 ↓ 0.0 0 1,184

Subquery Scan on *SELECT* 2_2 (cost=5.24..15.68 rows=1 width=48) (actual time=0.436..0.476 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. 19.109 548.192 ↓ 0.0 0 1,184

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.427..0.463 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 524.512 ↑ 1.0 1 1,184

Nested Loop (cost=4.97..14.61 rows=1 width=141) (actual time=0.414..0.443 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. 48.246 462.944 ↑ 5.0 2 1,184

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

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

Nested Loop (cost=4.55..11.24 rows=2 width=16) (actual time=0.309..0.330 rows=1 loops=1,184)

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

Function Scan on product.product_current_recipe_at_time r_2 (cost=0.25..0.27 rows=1 width=16) (actual time=0.278..0.281 rows=1 loops=1,184)

  • Output: r_2.id
  • Function Call: product.product_current_recipe_at_time(__local_36__.*, now())
  • Filter: ((r_2.deleted_at IS NULL) AND ((r_2.type)::text = 'NORMAL'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=14688
127. 16.207 23.884 ↑ 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.028 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. 23.978 23.978 ↑ 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.019 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.136 123.140 ↑ 1.0 1 188

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

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

Initplan (for Result)

134. 4.136 119.004 ↑ 1.0 1 188

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.628..0.633 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.948 114.868 ↑ 25.0 1 188

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

  • Buffers: shared hit=5562
136. 1.692 10.528 ↓ 0.0 0 188

Subquery Scan on *SELECT* 1_3 (cost=0.00..14.13 rows=24 width=48) (actual time=0.056..0.056 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.660 8.836 ↓ 0.0 0 188

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.047..0.047 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. 4.888 4.888 ↓ 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.022..0.026 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.022..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 100.392 ↑ 1.0 1 188

Subquery Scan on *SELECT* 2_3 (cost=5.24..15.68 rows=1 width=48) (actual time=0.471..0.534 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. 3.865 97.572 ↑ 1.0 1 188

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.461..0.519 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. 8.099 92.496 ↑ 1.0 1 188

Nested Loop (cost=4.97..14.61 rows=1 width=141) (actual time=0.444..0.492 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.750 80.464 ↑ 5.0 2 188

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

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

Nested Loop (cost=4.55..11.24 rows=2 width=16) (actual time=0.348..0.366 rows=1 loops=188)

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

Function Scan on product.product_current_recipe_at_time r_3 (cost=0.25..0.27 rows=1 width=16) (actual time=0.318..0.321 rows=1 loops=188)

  • Output: r_3.id
  • Function Call: product.product_current_recipe_at_time(__local_27__.*, now())
  • 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.392 3.536 ↑ 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.024..0.034 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. 1.144 1.144 ↑ 1.0 2 104

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

  • Index Cond: (rg_3.recipe_id = r_3.id)
  • Buffers: shared hit=208
148. 3.906 3.906 ↑ 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.009..0.021 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 : 4,861.046 ms