explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qh4a

Settings
# exclusive inclusive rows x rows loops node
1. 0.070 512.618 ↑ 1.0 1 1

Result (cost=381,577.12..381,577.13 rows=1 width=32) (actual time=512.617..512.618 rows=1 loops=1)

  • Output: COALESCE($51, '[]'::json)
  • Buffers: shared hit=97014
2.          

Initplan (for Result)

3. 2.272 512.548 ↑ 1.0 1 1

Aggregate (cost=381,577.10..381,577.11 rows=1 width=32) (actual time=512.548..512.548 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=97014
4. 0.019 0.104 ↑ 1.0 1 1

Sort (cost=11.72..11.73 rows=1 width=415) (actual time=0.103..0.104 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=10
5. 0.085 0.085 ↑ 1.0 1 1

Seq Scan on product.catalog __local_1__ (cost=0.00..11.71 rows=1 width=415) (actual time=0.085..0.085 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: 137
  • Buffers: shared hit=10
6.          

SubPlan (for Aggregate)

7. 0.006 0.037 ↑ 1.0 1 1

Result (cost=27,253.78..27,253.79 rows=1 width=32) (actual time=0.036..0.037 rows=1 loops=1)

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

Initplan (for Result)

9. 0.002 0.031 ↑ 1.0 1 1

Aggregate (cost=27,253.76..27,253.77 rows=1 width=32) (actual time=0.031..0.031 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.009 0.029 ↓ 0.0 0 1

Sort (cost=105.02..105.18 rows=65 width=124) (actual time=0.029..0.029 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.003 0.020 ↓ 0.0 0 1

Bitmap Heap Scan on product.catalog_item __local_3__ (cost=4.79..103.06 rows=65 width=124) (actual time=0.020..0.020 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 catalog_item_catalog_id_idx (cost=0.00..4.77 rows=65 width=0) (actual time=0.017..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..417.65 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..339.89 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=339.61..339.63 rows=1 width=32) (never executed)

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

Initplan (for Result)

24. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=339.59..339.60 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.56..10.56 rows=2 width=168) (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.55 rows=2 width=168) (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=164.45..164.46 rows=1 width=32) (never executed)

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

Initplan (for Result)

31. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=164.43..164.44 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=14.45..14.45 rows=3 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.30..14.42 rows=3 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.30 rows=3 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..49.97 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=41.65..41.66 rows=1 width=32) (never executed)

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

Initplan (for Result)

40. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=41.63..41.64 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.27..41.33 rows=24 width=48) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=0.27..18.14 rows=23 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.27..17.91 rows=23 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

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg (cost=0.27..8.29 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
  • Index Cond: (pmg.product_id = (__local_13__.*).id)
  • Filter: (pmg.deleted_at IS NULL)
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m (cost=0.00..9.14 rows=38 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=12.83..23.07 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=12.83..23.06 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

Hash Join (cost=12.56..21.70 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
  • Hash Cond: (m_1.target_product_id = ri.product_id)
49. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m_1 (cost=0.00..8.95 rows=49 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
  • Filter: (m_1.customer_visible AND (m_1.deleted_at IS NULL) AND ((m_1.operation)::text = 'REMOVE'::text))
50. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.48..12.48 rows=6 width=27) (never executed)

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

Nested Loop (cost=4.82..12.48 rows=6 width=27) (never executed)

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

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

  • Output: rg.id
53. 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))
54. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on product.recipe_group rg (cost=4.29..10.55 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)
55. 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)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using recipe_item_group_id_idx on product.recipe_item ri (cost=0.28..0.79 rows=3 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)
57. 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.35 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)
58. 0.000 0.000 ↓ 0.0 0

Result (cost=41.65..41.66 rows=1 width=32) (never executed)

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

Initplan (for Result)

60. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=41.63..41.64 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)))))
61. 0.000 0.000 ↓ 0.0 0

Append (cost=0.27..41.33 rows=24 width=48) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_1 (cost=0.27..18.14 rows=23 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
63. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.27..17.91 rows=23 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)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_2 (cost=0.27..8.29 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
  • Index Cond: (pmg_2.product_id = (__local_4__.*).id)
  • Filter: (pmg_2.deleted_at IS NULL)
65. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m_2 (cost=0.00..9.14 rows=38 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[])))
66. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_1 (cost=12.83..23.07 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
67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=12.83..23.06 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
68. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=12.56..21.70 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
  • Hash Cond: (m_3.target_product_id = ri_1.product_id)
69. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m_3 (cost=0.00..8.95 rows=49 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
  • Filter: (m_3.customer_visible AND (m_3.deleted_at IS NULL) AND ((m_3.operation)::text = 'REMOVE'::text))
70. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.48..12.48 rows=6 width=27) (never executed)

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

Nested Loop (cost=4.82..12.48 rows=6 width=27) (never executed)

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

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

  • Output: rg_1.id
73. 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))
74. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on product.recipe_group rg_1 (cost=4.29..10.55 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)
75. 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)
76. 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.79 rows=3 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)
77. 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.35 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)
78. 0.520 510.135 ↑ 1.0 1 1

Result (cost=354,311.55..354,311.56 rows=1 width=32) (actual time=510.134..510.135 rows=1 loops=1)

  • Output: json_build_object('data'::text, COALESCE($50, '[]'::json))
  • Buffers: shared hit=97002
79.          

Initplan (for Result)

80. 1.124 509.615 ↑ 1.0 1 1

Aggregate (cost=354,311.53..354,311.54 rows=1 width=32) (actual time=509.615..509.615 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=97002
81. 0.027 0.053 ↓ 1.1 14 1

Sort (cost=11.95..11.99 rows=13 width=415) (actual time=0.046..0.053 rows=14 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=10
82. 0.026 0.026 ↓ 1.1 14 1

Seq Scan on product.catalog __local_24__ (cost=0.00..11.71 rows=13 width=415) (actual time=0.004..0.026 rows=14 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=10
83.          

SubPlan (for Aggregate)

84. 0.364 508.438 ↑ 1.0 1 14

Result (cost=27,253.78..27,253.79 rows=1 width=32) (actual time=36.316..36.317 rows=1 loops=14)

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

Initplan (for Result)

86. 3.346 508.074 ↑ 1.0 1 14

Aggregate (cost=27,253.76..27,253.77 rows=1 width=32) (actual time=36.291..36.291 rows=1 loops=14)

  • 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=96992
87. 0.238 0.476 ↑ 4.6 14 14

Sort (cost=105.02..105.18 rows=65 width=124) (actual time=0.028..0.034 rows=14 loops=14)

  • 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=45
88. 0.140 0.238 ↑ 4.6 14 14

Bitmap Heap Scan on product.catalog_item __local_26__ (cost=4.79..103.06 rows=65 width=124) (actual time=0.013..0.017 rows=14 loops=14)

  • 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=17
  • Buffers: shared hit=45
89. 0.098 0.098 ↑ 4.6 14 14

Bitmap Index Scan on catalog_item_catalog_id_idx (cost=0.00..4.77 rows=65 width=0) (actual time=0.007..0.007 rows=14 loops=14)

  • Index Cond: (__local_26__.catalog_id = __local_24__.id)
  • Buffers: shared hit=28
90.          

SubPlan (for Aggregate)

91. 7.371 504.252 ↑ 1.0 1 189

Index Scan using product_pkey on product.product __local_27__ (cost=0.28..417.65 rows=1 width=32) (actual time=2.667..2.668 rows=1 loops=189)

  • 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=96947
92.          

SubPlan (for Index Scan)

93. 0.567 435.078 ↑ 1.0 1 189

Result (cost=27.77..27.79 rows=1 width=32) (actual time=2.302..2.302 rows=1 loops=189)

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

Initplan (for Result)

95. 3.402 434.511 ↑ 1.0 1 189

Aggregate (cost=27.75..27.76 rows=1 width=32) (actual time=2.299..2.299 rows=1 loops=189)

  • 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=91910
96. 431.109 431.109 ↑ 1,000.0 1 189

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

  • 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=91910
97. 26.460 26.460 ↓ 0.0 0 189

Function Scan on product.product_current_bundle_recipe_at_time __local_31__ (cost=0.25..339.89 rows=1 width=32) (actual time=0.140..0.140 rows=0 loops=189)

  • 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=794
98.          

SubPlan (for Function Scan)

99. 0.000 0.000 ↓ 0.0 0

Result (cost=339.61..339.63 rows=1 width=32) (never executed)

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

Initplan (for Result)

101. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=339.59..339.60 rows=1 width=32) (never executed)

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

Sort (cost=10.56..10.56 rows=2 width=168) (never executed)

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

Bitmap Heap Scan on product.recipe_group __local_33__ (cost=4.29..10.55 rows=2 width=168) (never executed)

  • 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))
104. 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_33__.recipe_id = __local_31__.id)
105.          

SubPlan (for Aggregate)

106. 0.000 0.000 ↓ 0.0 0

Result (cost=164.45..164.46 rows=1 width=32) (never executed)

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

Initplan (for Result)

108. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=164.43..164.44 rows=1 width=32) (never executed)

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

Sort (cost=14.45..14.45 rows=3 width=242) (never executed)

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

Bitmap Heap Scan on product.recipe_item __local_35__ (cost=4.30..14.42 rows=3 width=242) (never executed)

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

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

  • Index Cond: (__local_35__.group_id = __local_33__.id)
112.          

SubPlan (for Aggregate)

113. 0.000 0.000 ↓ 0.0 0

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

  • 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)
114.          

SubPlan (for Index Scan)

115. 0.000 0.000 ↓ 0.0 0

Result (cost=41.65..41.66 rows=1 width=32) (never executed)

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

Initplan (for Result)

117. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=41.63..41.64 rows=1 width=32) (never executed)

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

Append (cost=0.27..41.33 rows=24 width=48) (never executed)

119. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_2 (cost=0.27..18.14 rows=23 width=48) (never executed)

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

Nested Loop (cost=0.27..17.91 rows=23 width=127) (never executed)

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

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

  • 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
  • Index Cond: (pmg_4.product_id = (__local_36__.*).id)
  • Filter: (pmg_4.deleted_at IS NULL)
122. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m_4 (cost=0.00..9.14 rows=38 width=139) (never executed)

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

Subquery Scan on *SELECT* 2_2 (cost=12.83..23.07 rows=1 width=48) (never executed)

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

Nested Loop (cost=12.83..23.06 rows=1 width=125) (never executed)

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

Hash Join (cost=12.56..21.70 rows=1 width=141) (never executed)

  • 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
  • Hash Cond: (m_5.target_product_id = ri_2.product_id)
126. 0.000 0.000 ↓ 0.0 0

Seq Scan on product.modifier m_5 (cost=0.00..8.95 rows=49 width=130) (never executed)

  • 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
  • Filter: (m_5.customer_visible AND (m_5.deleted_at IS NULL) AND ((m_5.operation)::text = 'REMOVE'::text))
127. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.48..12.48 rows=6 width=27) (never executed)

  • Output: ri_2.qty, ri_2.unit, ri_2."position", ri_2.product_id
128. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.82..12.48 rows=6 width=27) (never executed)

  • Output: ri_2.qty, ri_2.unit, ri_2."position", ri_2.product_id
129. 0.000 0.000 ↓ 0.0 0

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

  • Output: rg_2.id
130. 0.000 0.000 ↓ 0.0 0

Function Scan on product.product_current_recipe_at_time r_2 (cost=0.25..0.27 rows=1 width=16) (never executed)

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

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

  • Output: rg_2.recipe_id, rg_2.id
  • Recheck Cond: (rg_2.recipe_id = r_2.id)
  • Filter: (rg_2.deleted_at IS NULL)
132. 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_2.recipe_id = r_2.id)
133. 0.000 0.000 ↓ 0.0 0

Index Scan using recipe_item_group_id_idx on product.recipe_item ri_2 (cost=0.28..0.79 rows=3 width=43) (never executed)

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

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

  • 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)
135. 0.756 35.343 ↑ 1.0 1 189

Result (cost=41.65..41.66 rows=1 width=32) (actual time=0.187..0.187 rows=1 loops=189)

  • Output: json_build_object('data'::text, COALESCE($43, '[]'::json))
  • Buffers: shared hit=3676
136.          

Initplan (for Result)

137. 1.323 34.587 ↑ 1.0 1 189

Aggregate (cost=41.63..41.64 rows=1 width=32) (actual time=0.182..0.183 rows=1 loops=189)

  • 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=3676
138. 0.189 33.264 ↑ 24.0 1 189

Append (cost=0.27..41.33 rows=24 width=48) (actual time=0.171..0.176 rows=1 loops=189)

  • Buffers: shared hit=3676
139. 0.000 2.646 ↓ 0.0 0 189

Subquery Scan on *SELECT* 1_3 (cost=0.27..18.14 rows=23 width=48) (actual time=0.014..0.014 rows=0 loops=189)

  • 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=826
140. 0.399 2.646 ↓ 0.0 0 189

Nested Loop (cost=0.27..17.91 rows=23 width=127) (actual time=0.014..0.014 rows=0 loops=189)

  • 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=826
141. 0.567 0.567 ↓ 0.0 0 189

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_6 (cost=0.27..8.29 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=189)

  • 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
  • Index Cond: (pmg_6.product_id = (__local_27__.*).id)
  • Filter: (pmg_6.deleted_at IS NULL)
  • Buffers: shared hit=434
142. 1.680 1.680 ↓ 0.0 0 56

Seq Scan on product.modifier m_6 (cost=0.00..9.14 rows=38 width=139) (actual time=0.030..0.030 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: 156
  • Buffers: shared hit=392
143. 0.189 30.429 ↑ 1.0 1 189

Subquery Scan on *SELECT* 2_3 (cost=12.83..23.07 rows=1 width=48) (actual time=0.156..0.161 rows=1 loops=189)

  • 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=2850
144. 0.599 30.240 ↑ 1.0 1 189

Nested Loop (cost=12.83..23.06 rows=1 width=125) (actual time=0.156..0.160 rows=1 loops=189)

  • 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=2850
145. 1.575 29.295 ↑ 1.0 1 189

Hash Join (cost=12.56..21.70 rows=1 width=141) (actual time=0.151..0.155 rows=1 loops=189)

  • 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
  • Hash Cond: (m_7.target_product_id = ri_3.product_id)
  • Buffers: shared hit=2398
146. 3.150 3.150 ↓ 1.8 86 105

Seq Scan on product.modifier m_7 (cost=0.00..8.95 rows=49 width=130) (actual time=0.003..0.030 rows=86 loops=105)

  • 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
  • Filter: (m_7.customer_visible AND (m_7.deleted_at IS NULL) AND ((m_7.operation)::text = 'REMOVE'::text))
  • Rows Removed by Filter: 68
  • Buffers: shared hit=732
147. 0.378 24.570 ↑ 3.0 2 189

Hash (cost=12.48..12.48 rows=6 width=27) (actual time=0.130..0.130 rows=2 loops=189)

  • Output: ri_3.qty, ri_3.unit, ri_3."position", ri_3.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1666
148. 0.204 24.192 ↑ 3.0 2 189

Nested Loop (cost=4.82..12.48 rows=6 width=27) (actual time=0.125..0.128 rows=2 loops=189)

  • Output: ri_3.qty, ri_3.unit, ri_3."position", ri_3.product_id
  • Buffers: shared hit=1666
149. 0.406 23.058 ↑ 2.0 1 189

Nested Loop (cost=4.54..10.83 rows=2 width=16) (actual time=0.121..0.122 rows=1 loops=189)

  • Output: rg_3.id
  • Buffers: shared hit=1107
150. 21.924 21.924 ↑ 1.0 1 189

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

  • 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=794
151. 0.416 0.728 ↑ 1.0 2 104

Bitmap Heap Scan on product.recipe_group rg_3 (cost=4.29..10.55 rows=2 width=32) (actual time=0.006..0.007 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
152. 0.312 0.312 ↑ 1.0 2 104

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

  • Index Cond: (rg_3.recipe_id = r_3.id)
  • Buffers: shared hit=208
153. 0.930 0.930 ↑ 1.5 2 186

Index Scan using recipe_item_group_id_idx on product.recipe_item ri_3 (cost=0.28..0.79 rows=3 width=43) (actual time=0.004..0.005 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
154. 0.346 0.346 ↑ 1.0 1 173

Index Scan using idx_product_modifier_group on product.product_modifier_group pmg_7 (cost=0.27..1.35 rows=1 width=16) (actual time=0.002..0.002 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
Planning time : 12.672 ms
Execution time : 513.561 ms