explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T7ht

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,565.269 4,633.552 ↑ 1.0 1 1

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

  • Output: COALESCE($55, '[]'::json)
  • Buffers: shared hit=87511
  • Functions: 366
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 32.207 ms, Inlining 17.105 ms, Optimization 2051.506 ms, Emission 1493.742 ms, Total 3594.560 ms
2.          

Initplan (for Result)

3. 7.289 1,068.283 ↑ 1.0 1 1

Aggregate (cost=582,124.36..582,124.37 rows=1 width=32) (actual time=1,068.275..1,068.283 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=87511
4. 0.067 0.131 ↑ 1.0 1 1

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

Seq Scan on product.catalog __local_1__ (cost=0.00..6.54 rows=1 width=419) (actual time=0.054..0.064 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.000 0.088 ↑ 1.0 1 1

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

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

Initplan (for Result)

9. 0.046 0.092 ↑ 1.0 1 1

Aggregate (cost=41,579.30..41,579.31 rows=1 width=32) (actual time=0.054..0.092 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.015 0.046 ↓ 0.0 0 1

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.042..0.046 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.031 ↓ 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.031 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.017 0.017 ↓ 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.017 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. 2.958 1,060.775 ↑ 1.0 1 1

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

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

Initplan (for Result)

78. 6.152 1,057.817 ↑ 1.0 1 1

Aggregate (cost=540,538.42..540,538.43 rows=1 width=32) (actual time=1,057.809..1,057.817 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=87504
79. 0.189 0.277 ↑ 1.0 13 1

Sort (cost=6.78..6.81 rows=13 width=419) (actual time=0.210..0.277 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.088 0.088 ↑ 1.0 13 1

Seq Scan on product.catalog __local_24__ (cost=0.00..6.54 rows=13 width=419) (actual time=0.025..0.088 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.274 1,051.388 ↑ 1.0 1 13

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

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

Initplan (for Result)

84. 7.723 1,050.114 ↑ 1.0 1 13

Aggregate (cost=41,579.30..41,579.31 rows=1 width=32) (actual time=80.774..80.778 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=87499
85. 2.015 3.315 ↑ 5.9 14 13

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.185..0.255 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.300 ↑ 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.030..0.100 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.221 0.221 ↑ 5.9 14 13

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

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

SubPlan (for Aggregate)

89. 12.784 1,039.076 ↑ 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=5.522..5.527 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=87457
90.          

SubPlan (for Index Scan)

91. 4.136 317.908 ↑ 1.0 1 188

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

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

Initplan (for Result)

93. 6.580 313.772 ↑ 1.0 1 188

Aggregate (cost=27.75..27.76 rows=1 width=32) (actual time=1.664..1.669 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. 307.192 307.192 ↑ 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.627..1.634 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. 23.406 628.860 ↓ 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=3.344..3.345 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=29645
96.          

SubPlan (for Function Scan)

97. 2.280 605.454 ↑ 1.0 1 57

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

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

Initplan (for Result)

99. 9.006 603.174 ↑ 1.0 1 57

Aggregate (cost=439.58..439.59 rows=1 width=32) (actual time=10.578..10.582 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=28619
100. 2.565 4.788 ↓ 1.5 3 57

Sort (cost=10.97..10.97 rows=2 width=158) (actual time=0.068..0.084 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.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.023..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.513 0.513 ↓ 1.5 3 57

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

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

SubPlan (for Aggregate)

104. 4.455 589.380 ↑ 1.0 1 165

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

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

Initplan (for Result)

106. 32.577 584.925 ↑ 1.0 1 165

Aggregate (cost=214.21..214.22 rows=1 width=32) (actual time=3.541..3.545 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=28446
107. 14.355 23.100 ↓ 1.4 7 165

Sort (cost=21.15..21.17 rows=5 width=242) (actual time=0.106..0.140 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.260 8.745 ↓ 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.021..0.053 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.485 1.485 ↓ 1.4 7 165

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

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

SubPlan (for Aggregate)

111. 44.992 529.248 ↑ 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.442..0.447 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=27937
112.          

SubPlan (for Index Scan)

113. 26.048 484.256 ↑ 1.0 1 1,184

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

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

Initplan (for Result)

115. 22.496 458.208 ↑ 1.0 1 1,184

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.383..0.387 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=24385
116. 18.944 435.712 ↓ 0.0 0 1,184

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

  • Buffers: shared hit=24385
117. 10.656 71.040 ↓ 0.0 0 1,184

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

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.051..0.051 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. 29.600 29.600 ↓ 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.025 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. 11.640 11.640 ↓ 0.0 0 582

Seq Scan on product.modifier m_4 (cost=0.00..6.20 rows=40 width=139) (actual time=0.020..0.020 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. 13.024 345.728 ↓ 0.0 0 1,184

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

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.245..0.281 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=17321
123. 40.640 309.024 ↑ 1.0 1 1,184

Nested Loop (cost=4.97..14.61 rows=1 width=141) (actual time=0.233..0.261 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=15611
124. 47.062 247.456 ↑ 5.0 2 1,184

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

  • Output: ri_2.qty, ri_2.unit, ri_2."position", ri_2.product_id
  • Buffers: shared hit=12261
125. 33.470 176.416 ↑ 2.0 1 1,184

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

  • Output: rg_2.id
  • Buffers: shared hit=8468
126. 120.768 120.768 ↑ 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.099..0.102 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=5888
127. 16.207 22.178 ↑ 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.019..0.026 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. 5.971 5.971 ↑ 2.0 1 853

Bitmap Index Scan on idx_recipe_group (cost=0.00..4.29 rows=2 width=0) (actual time=0.007..0.007 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.700 79.524 ↑ 1.0 1 188

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

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

Initplan (for Result)

134. 4.136 74.824 ↑ 1.0 1 188

Aggregate (cost=30.25..30.26 rows=1 width=32) (actual time=0.394..0.398 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=3952
135. 3.384 70.688 ↑ 25.0 1 188

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

  • Buffers: shared hit=3952
136. 1.504 10.716 ↓ 0.0 0 188

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

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.049..0.049 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.023..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.344 1.344 ↓ 0.0 0 56

Seq Scan on product.modifier m_6 (cost=0.00..6.20 rows=40 width=139) (actual time=0.024..0.024 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.632 56.588 ↑ 1.0 1 188

Subquery Scan on *SELECT* 2_3 (cost=5.24..15.68 rows=1 width=48) (actual time=0.239..0.301 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=2976
141. 3.850 53.956 ↑ 1.0 1 188

Nested Loop (cost=5.24..15.67 rows=1 width=125) (actual time=0.230..0.287 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=2976
142. 7.347 49.068 ↑ 1.0 1 188

Nested Loop (cost=4.97..14.61 rows=1 width=141) (actual time=0.215..0.261 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=2524
143. 7.370 37.788 ↑ 5.0 2 188

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

  • Output: ri_3.qty, ri_3.unit, ri_3."position", ri_3.product_id
  • Buffers: shared hit=1892
144. 4.776 26.884 ↑ 2.0 1 188

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

  • Output: rg_3.id
  • Buffers: shared hit=1333
145. 18.988 18.988 ↑ 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.098..0.101 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=1020
146. 2.288 3.120 ↑ 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.021..0.030 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.832 0.832 ↑ 1.0 2 104

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

  • Index Cond: (rg_3.recipe_id = r_3.id)
  • Buffers: shared hit=208
148. 3.534 3.534 ↑ 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.008..0.019 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.038 1.038 ↑ 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.006..0.006 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,671.347 ms