explain.depesz.com

PostgreSQL's explain analyze made readable

Result: woNr

Settings
# exclusive inclusive rows x rows loops node
1. 3,555.533 4,882.969 ↑ 1.0 1 1

Result (cost=664,720.11..664,720.12 rows=1 width=32) (actual time=4,882.960..4,882.969 rows=1 loops=1)

  • Output: COALESCE($55, '[]'::json)
  • Buffers: shared hit=85663
  • Functions: 392
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 34.190 ms, Inlining 17.543 ms, Optimization 2075.202 ms, Emission 1460.485 ms, Total 3587.420 ms
2.          

Initplan (for Result)

3. 6.634 1,327.436 ↑ 1.0 1 1

Aggregate (cost=664,720.09..664,720.10 rows=1 width=32) (actual time=1,327.427..1,327.436 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=85663
4. 0.059 0.131 ↑ 1.0 1 1

Sort (cost=6.55..6.55 rows=1 width=419) (actual time=0.122..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.072 0.072 ↑ 1.0 1 1

Seq Scan on product.catalog __local_1__ (cost=0.00..6.54 rows=1 width=419) (actual time=0.062..0.072 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.024 0.109 ↑ 1.0 1 1

Result (cost=47,479.01..47,479.03 rows=1 width=32) (actual time=0.101..0.109 rows=1 loops=1)

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

Initplan (for Result)

9. 0.016 0.085 ↑ 1.0 1 1

Aggregate (cost=47,478.99..47,479.00 rows=1 width=32) (actual time=0.076..0.085 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.014 0.069 ↓ 0.0 0 1

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.065..0.069 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.013 0.055 ↓ 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.051..0.055 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.042 0.042 ↓ 0.0 0 1

Bitmap Index Scan on idx_catalog_item (cost=0.00..4.90 rows=82 width=0) (actual time=0.038..0.042 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..578.19 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=11.31..11.32 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=11.29..11.30 rows=1 width=32) (never executed)

  • Output: json_agg(to_json(ROW(to_json(ROW(('NORMAL'::character varying), (price.product_normal_price((__local_4__.*).group_id, (__local_4__.*).site_id, (__local_4__.*).id, 'SALES'::character varying, strcurrency.strcurrency, 0)), strcurrency.strcurrency, (NULL::text))), to_json(json_build_object('@priceType'::text, ('NORMAL'::character varying), '@price'::text, ((price.product_normal_price((__local_4__.*).group_id, (__local_4__.*).site_id, (__local_4__.*).id, 'SALES'::character varying, strcurrency.strcurrency, 0)))::text, '@currency'::text, strcurrency.strcurrency, '@offerNumber'::text, (NULL::text))))))
19. 0.000 0.000 ↓ 0.0 0

Append (cost=0.25..11.25 rows=2 width=76) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Function Scan on core.get_site_home_currency strcurrency (cost=0.25..0.51 rows=1 width=128) (never executed)

  • Output: 'NORMAL'::character varying, price.product_normal_price((__local_4__.*).group_id, (__local_4__.*).site_id, (__local_4__.*).id, 'SALES'::character varying, strcurrency.strcurrency, 0), strcurrency.strcurrency, NULL::text
  • Function Call: core.get_site_home_currency((__local_4__.*).site_id)
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..10.72 rows=1 width=23) (never executed)

  • Output: o.type, op.price, op.currency, o.offer_no
  • Inner Unique: true
  • Join Filter: (o.id = op.offer_id)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on price.offer o (cost=0.00..5.52 rows=1 width=29) (never executed)

  • Output: o.type, o.offer_no, o.id
  • Filter: ((o.deleted_at IS NULL) AND ((o.group_id = (__local_4__.*).group_id) OR ((o.group_id IS NULL) AND ((__local_4__.*).group_id IS NULL))) AND ((o.site_id = (__local_4__.*).site_id) OR ((o.site_id IS NULL) AND ((__local_4__.*).site_id IS NULL))) AND (o.valid_during @> (now())::date))
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on price.offer_price op (cost=0.00..5.17 rows=2 width=26) (never executed)

  • Output: op.price, op.currency, op.offer_id
  • Filter: ((op.deleted_at IS NULL) AND (op.product_id = (__local_4__.*).id) AND ((op.offer_price_type)::text = 'PRICE'::text))
24. 0.000 0.000 ↓ 0.0 0

Function Scan on product.product_current_bundle_recipe_at_time __local_8__ (cost=0.25..520.25 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))
25.          

SubPlan (for Function Scan)

26. 0.000 0.000 ↓ 0.0 0

Result (cost=519.97..519.98 rows=1 width=32) (never executed)

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

Initplan (for Result)

28. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=519.95..519.96 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))))))
29. 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
30. 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))
31. 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)
32.          

SubPlan (for Aggregate)

33. 0.000 0.000 ↓ 0.0 0

Result (cost=254.42..254.43 rows=1 width=32) (never executed)

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

Initplan (for Result)

35. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=254.40..254.41 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))))))
36. 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
37. 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))
38. 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)
39.          

SubPlan (for Aggregate)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using product_pkey on product.product __local_13__ (cost=0.28..46.62 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)
41.          

SubPlan (for Index Scan)

42. 0.000 0.000 ↓ 0.0 0

Result (cost=38.31..38.32 rows=1 width=32) (never executed)

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

Initplan (for Result)

44. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=38.29..38.30 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)))))
45. 0.000 0.000 ↓ 0.0 0

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

46. 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
47. 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)
48. 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))
49. 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[])))
50. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=5.27..23.72 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
51. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.27..23.71 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
52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.00..22.64 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
53. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.85..20.86 rows=10 width=27) (never executed)

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

Nested Loop (cost=4.57..19.28 rows=2 width=16) (never executed)

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

Index Scan using recipe_product_id_idx on product.recipe r (cost=0.28..8.30 rows=1 width=16) (never executed)

  • Output: r.id
  • Index Cond: (r.product_id = (__local_13__.*).id)
  • Filter: ((r.deleted_at IS NULL) AND ((r.type)::text = 'NORMAL'::text) AND COALESCE(((now())::date <@ r.valid_during), true))
56. 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)
57. 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)
58. 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)
59. 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))
60. 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)
61. 0.000 0.000 ↓ 0.0 0

Result (cost=38.31..38.32 rows=1 width=32) (never executed)

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

Initplan (for Result)

63. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=38.29..38.30 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)))))
64. 0.000 0.000 ↓ 0.0 0

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

65. 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
66. 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)
67. 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))
68. 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[])))
69. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_1 (cost=5.27..23.72 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
70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.27..23.71 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
71. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.00..22.64 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
72. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.85..20.86 rows=10 width=27) (never executed)

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

Nested Loop (cost=4.57..19.28 rows=2 width=16) (never executed)

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

Index Scan using recipe_product_id_idx on product.recipe r_1 (cost=0.28..8.30 rows=1 width=16) (never executed)

  • Output: r_1.id
  • Index Cond: (r_1.product_id = (__local_4__.*).id)
  • Filter: ((r_1.deleted_at IS NULL) AND ((r_1.type)::text = 'NORMAL'::text) AND COALESCE(((now())::date <@ r_1.valid_during), true))
75. 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)
76. 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)
77. 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)
78. 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))
79. 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)
80. 2.564 1,320.562 ↑ 1.0 1 1

Result (cost=617,234.47..617,234.48 rows=1 width=32) (actual time=1,320.552..1,320.562 rows=1 loops=1)

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

Initplan (for Result)

82. 7.027 1,317.998 ↑ 1.0 1 1

Aggregate (cost=617,234.45..617,234.46 rows=1 width=32) (actual time=1,317.988..1,317.998 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=85656
83. 0.138 0.220 ↑ 1.0 13 1

Sort (cost=6.78..6.81 rows=13 width=419) (actual time=0.151..0.220 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
84. 0.082 0.082 ↑ 1.0 13 1

Seq Scan on product.catalog __local_24__ (cost=0.00..6.54 rows=13 width=419) (actual time=0.021..0.082 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
85.          

SubPlan (for Aggregate)

86. 1.781 1,310.751 ↑ 1.0 1 13

Result (cost=47,479.01..47,479.03 rows=1 width=32) (actual time=100.822..100.827 rows=1 loops=13)

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

Initplan (for Result)

88. 7.734 1,308.970 ↑ 1.0 1 13

Aggregate (cost=47,478.99..47,479.00 rows=1 width=32) (actual time=100.686..100.690 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=85651
89. 2.704 3.848 ↑ 5.9 14 13

Sort (cost=65.01..65.21 rows=82 width=124) (actual time=0.225..0.296 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
90. 0.988 1.144 ↑ 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.025..0.088 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
91. 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
92.          

SubPlan (for Aggregate)

93. 13.912 1,297.388 ↑ 1.0 1 188

Index Scan using product_pkey on product.product __local_27__ (cost=0.28..578.19 rows=1 width=32) (actual time=6.895..6.901 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=85609
94.          

SubPlan (for Index Scan)

95. 4.512 698.796 ↑ 1.0 1 188

Result (cost=11.31..11.32 rows=1 width=32) (actual time=3.713..3.717 rows=1 loops=188)

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

Initplan (for Result)

97. 6.768 694.284 ↑ 1.0 1 188

Aggregate (cost=11.29..11.30 rows=1 width=32) (actual time=3.688..3.693 rows=1 loops=188)

  • Output: json_agg(to_json(ROW(to_json(ROW(('NORMAL'::character varying), (price.product_normal_price((__local_27__.*).group_id, (__local_27__.*).site_id, (__local_27__.*).id, 'SALES'::character varying, strcurrency_1.strcurrency, 0)), strcurrency_1.strcurrency, (NULL::text))), to_json(json_build_object('@priceType'::text, ('NORMAL'::character varying), '@price'::text, ((price.product_normal_price((__local_27__.*).group_id, (__local_27__.*).site_id, (__local_27__.*).id, 'SALES'::character varying, strcurrency_1.strcurrency, 0)))::text, '@currency'::text, strcurrency_1.strcurrency, '@offerNumber'::text, (NULL::text))))))
  • Buffers: shared hit=54577
98. 5.264 687.516 ↑ 2.0 1 188

Append (cost=0.25..11.25 rows=2 width=76) (actual time=0.079..3.657 rows=1 loops=188)

  • Buffers: shared hit=54577
99. 13.536 13.536 ↑ 1.0 1 188

Function Scan on core.get_site_home_currency strcurrency_1 (cost=0.25..0.51 rows=1 width=128) (actual time=0.067..0.072 rows=1 loops=188)

  • Output: 'NORMAL'::character varying, price.product_normal_price((__local_27__.*).group_id, (__local_27__.*).site_id, (__local_27__.*).id, 'SALES'::character varying, strcurrency_1.strcurrency, 0), strcurrency_1.strcurrency, NULL::text
  • Function Call: core.get_site_home_currency((__local_27__.*).site_id)
  • Buffers: shared hit=564
100. 228.796 668.716 ↓ 0.0 0 188

Nested Loop (cost=0.00..10.72 rows=1 width=23) (actual time=2.700..3.557 rows=0 loops=188)

  • Output: o_1.type, op_1.price, op_1.currency, o_1.offer_no
  • Inner Unique: true
  • Join Filter: (o_1.id = op_1.offer_id)
  • Rows Removed by Join Filter: 49
  • Buffers: shared hit=54013
101. 100.580 100.580 ↓ 95.0 95 188

Seq Scan on price.offer o_1 (cost=0.00..5.52 rows=1 width=29) (actual time=0.009..0.535 rows=95 loops=188)

  • Output: o_1.type, o_1.offer_no, o_1.id
  • Filter: ((o_1.deleted_at IS NULL) AND ((o_1.group_id = (__local_27__.*).group_id) OR ((o_1.group_id IS NULL) AND ((__local_27__.*).group_id IS NULL))) AND ((o_1.site_id = (__local_27__.*).site_id) OR ((o_1.site_id IS NULL) AND ((__local_27__.*).site_id IS NULL))) AND (o_1.valid_during @> (now())::date))
  • Rows Removed by Filter: 17
  • Buffers: shared hit=564
102. 339.340 339.340 ↑ 2.0 1 17,860

Seq Scan on price.offer_price op_1 (cost=0.00..5.17 rows=2 width=26) (actual time=0.015..0.019 rows=1 loops=17,860)

  • Output: op_1.price, op_1.currency, op_1.offer_id
  • Filter: ((op_1.deleted_at IS NULL) AND (op_1.product_id = (__local_27__.*).id) AND ((op_1.offer_price_type)::text = 'PRICE'::text))
  • Rows Removed by Filter: 144
  • Buffers: shared hit=53449
103. 30.354 522.264 ↓ 0.0 0 188

Function Scan on product.product_current_bundle_recipe_at_time __local_31__ (cost=0.25..520.25 rows=1 width=32) (actual time=2.776..2.778 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=26999
104.          

SubPlan (for Function Scan)

105. 2.166 491.910 ↑ 1.0 1 57

Result (cost=519.97..519.98 rows=1 width=32) (actual time=8.626..8.630 rows=1 loops=57)

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

Initplan (for Result)

107. 8.556 489.744 ↑ 1.0 1 57

Aggregate (cost=519.95..519.96 rows=1 width=32) (actual time=8.588..8.592 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=25979
108. 2.394 4.503 ↓ 1.5 3 57

Sort (cost=10.97..10.97 rows=2 width=158) (actual time=0.066..0.079 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
109. 1.596 2.109 ↓ 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.037 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
110. 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.008..0.009 rows=3 loops=57)

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

SubPlan (for Aggregate)

112. 4.455 476.685 ↑ 1.0 1 165

Result (cost=254.42..254.43 rows=1 width=32) (actual time=2.884..2.889 rows=1 loops=165)

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

Initplan (for Result)

114. 32.003 472.230 ↑ 1.0 1 165

Aggregate (cost=254.40..254.41 rows=1 width=32) (actual time=2.857..2.862 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=25806
115. 13.035 22.275 ↓ 1.4 7 165

Sort (cost=21.15..21.17 rows=5 width=242) (actual time=0.102..0.135 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
116. 7.590 9.240 ↓ 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.022..0.056 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
117. 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
118.          

SubPlan (for Aggregate)

119. 42.624 417.952 ↑ 1.0 1 1,184

Index Scan using product_pkey on product.product __local_36__ (cost=0.28..46.62 rows=1 width=32) (actual time=0.348..0.353 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=25297
120.          

SubPlan (for Index Scan)

121. 24.864 375.328 ↑ 1.0 1 1,184

Result (cost=38.31..38.32 rows=1 width=32) (actual time=0.312..0.317 rows=1 loops=1,184)

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

Initplan (for Result)

123. 22.496 350.464 ↑ 1.0 1 1,184

Aggregate (cost=38.29..38.30 rows=1 width=32) (actual time=0.291..0.296 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=21745
124. 21.312 327.968 ↓ 0.0 0 1,184

Append (cost=0.00..37.97 rows=25 width=48) (actual time=0.235..0.277 rows=0 loops=1,184)

  • Buffers: shared hit=21745
125. 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
126. 17.960 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
127. 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
128. 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
129. 14.208 235.616 ↓ 0.0 0 1,184

Subquery Scan on *SELECT* 2_2 (cost=5.27..23.72 rows=1 width=48) (actual time=0.161..0.199 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=14681
130. 17.925 221.408 ↓ 0.0 0 1,184

Nested Loop (cost=5.27..23.71 rows=1 width=125) (actual time=0.152..0.187 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=14681
131. 40.640 198.912 ↑ 1.0 1 1,184

Nested Loop (cost=5.00..22.64 rows=1 width=141) (actual time=0.139..0.168 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=12971
132. 44.694 137.344 ↑ 5.0 2 1,184

Nested Loop (cost=4.85..20.86 rows=10 width=27) (actual time=0.056..0.116 rows=2 loops=1,184)

  • Output: ri_2.qty, ri_2.unit, ri_2."position", ri_2.product_id
  • Buffers: shared hit=9621
133. 31.102 68.672 ↑ 2.0 1 1,184

Nested Loop (cost=4.57..19.28 rows=2 width=16) (actual time=0.037..0.058 rows=1 loops=1,184)

  • Output: rg_2.id
  • Buffers: shared hit=5828
134. 15.392 15.392 ↑ 1.0 1 1,184

Index Scan using recipe_product_id_idx on product.recipe r_2 (cost=0.28..8.30 rows=1 width=16) (actual time=0.009..0.013 rows=1 loops=1,184)

  • Output: r_2.id
  • Index Cond: (r_2.product_id = (__local_36__.*).id)
  • Filter: ((r_2.deleted_at IS NULL) AND ((r_2.type)::text = 'NORMAL'::text) AND COALESCE(((now())::date <@ r_2.valid_during), true))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3248
135. 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
136. 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
137. 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.008..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
138. 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
139. 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
140. 4.136 62.416 ↑ 1.0 1 188

Result (cost=38.31..38.32 rows=1 width=32) (actual time=0.328..0.332 rows=1 loops=188)

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

Initplan (for Result)

142. 4.136 58.280 ↑ 1.0 1 188

Aggregate (cost=38.29..38.30 rows=1 width=32) (actual time=0.306..0.310 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=3469
143. 3.572 54.144 ↑ 25.0 1 188

Append (cost=0.00..37.97 rows=25 width=48) (actual time=0.224..0.288 rows=1 loops=188)

  • Buffers: shared hit=3469
144. 1.692 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
145. 2.848 9.024 ↓ 0.0 0 188

Nested Loop (cost=0.00..13.89 rows=24 width=127) (actual time=0.048..0.048 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
146. 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
147. 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
148. 2.820 39.856 ↑ 1.0 1 188

Subquery Scan on *SELECT* 2_3 (cost=5.27..23.72 rows=1 width=48) (actual time=0.152..0.212 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=2493
149. 3.677 37.036 ↑ 1.0 1 188

Nested Loop (cost=5.27..23.71 rows=1 width=125) (actual time=0.142..0.197 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=2493
150. 7.032 32.148 ↑ 1.0 1 188

Nested Loop (cost=5.00..22.64 rows=1 width=141) (actual time=0.126..0.171 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=2041
151. 7.560 21.620 ↑ 5.0 2 188

Nested Loop (cost=4.85..20.86 rows=10 width=27) (actual time=0.054..0.115 rows=2 loops=188)

  • Output: ri_3.qty, ri_3.unit, ri_3."position", ri_3.product_id
  • Buffers: shared hit=1409
152. 4.880 10.340 ↑ 2.0 1 188

Nested Loop (cost=4.57..19.28 rows=2 width=16) (actual time=0.036..0.055 rows=1 loops=188)

  • Output: rg_3.id
  • Buffers: shared hit=850
153. 2.444 2.444 ↑ 1.0 1 188

Index Scan using recipe_product_id_idx on product.recipe r_3 (cost=0.28..8.30 rows=1 width=16) (actual time=0.009..0.013 rows=1 loops=188)

  • Output: r_3.id
  • Index Cond: (r_3.product_id = (__local_27__.*).id)
  • Filter: ((r_3.deleted_at IS NULL) AND ((r_3.type)::text = 'NORMAL'::text) AND COALESCE(((now())::date <@ r_3.valid_during), true))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=537
154. 2.184 3.016 ↑ 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.029 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
155. 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
156. 3.720 3.720 ↑ 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.020 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
157. 3.496 3.496 ↓ 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.006..0.008 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
158. 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,923.857 ms