explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4HmW

Settings
# exclusive inclusive rows x rows loops node
1. 1.563 1,809.253 ↑ 686,313.7 836 1

Sort (cost=12,206,002,030.65..12,206,288,909.77 rows=573,758,230 width=858) (actual time=1,809.201..1,809.253 rows=836 loops=1)

  • Sort Key: _op.reference_number
  • Sort Method: quicksort Memory: 458kB
2.          

CTE _offers

3. 0.003 0.670 ↑ 1.0 5 1

Limit (cost=0.06..5.65 rows=5 width=16) (actual time=0.157..0.670 rows=5 loops=1)

4. 0.607 0.667 ↑ 159.2 5 1

Nested Loop (cost=0.06..890.96 rows=796 width=16) (actual time=0.156..0.667 rows=5 loops=1)

5. 0.030 0.030 ↑ 159.2 5 1

Seq Scan on offers o_1 (cost=0.00..777.69 rows=796 width=656) (actual time=0.013..0.030 rows=5 loops=1)

  • Filter: ((created_at > '2019-11-01 00:00:00-04'::timestamp with time zone) AND (offer_type = ANY ('{business_plan.premier,business_plan.preferred,business_plan.premier.historical,business_plan.preferred.historical}'::text[])))
  • Rows Removed by Filter: 8
6. 0.030 0.030 ↑ 1.0 1 5

Index Only Scan using users_pkey on users u (cost=0.06..0.14 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=5)

  • Index Cond: (id = (((o_1.properties -> 'owner'::text) ->> 'id'::text))::uuid)
  • Heap Fetches: 1
7.          

CTE _offers_plus

8. 5.319 93.212 ↓ 1.7 836 1

HashAggregate (cost=35.34..40.34 rows=500 width=1,034) (actual time=91.717..93.212 rows=836 loops=1)

  • Group Key: o_2.id, ((obj.offer_item ->> 'product_id'::text))::uuid, (((o_2.properties -> 'owner'::text) ->> 'id'::text))::uuid
9. 86.232 87.893 ↓ 1.7 836 1

Nested Loop (cost=0.06..25.09 rows=500 width=842) (actual time=0.452..87.893 rows=836 loops=1)

10. 0.012 0.726 ↑ 1.0 5 1

Nested Loop (cost=0.06..20.34 rows=5 width=778) (actual time=0.168..0.726 rows=5 loops=1)

11. 0.679 0.679 ↑ 1.0 5 1

CTE Scan on _offers _o (cost=0.00..0.03 rows=5 width=16) (actual time=0.160..0.679 rows=5 loops=1)

12. 0.035 0.035 ↑ 1.0 1 5

Index Scan using offers_pk on offers o_2 (cost=0.06..4.06 rows=1 width=778) (actual time=0.007..0.007 rows=1 loops=5)

  • Index Cond: (id = _o.id)
13. 0.935 0.935 ↓ 1.7 167 5

Function Scan on jsonb_array_elements obj (cost=0.00..0.30 rows=100 width=32) (actual time=0.164..0.187 rows=167 loops=5)

14.          

CTE _prod_to_sku

15. 21.120 149.248 ↑ 141.0 2,963 1

Merge Join (cost=10,411.36..12,923.10 rows=417,635 width=96) (actual time=123.837..149.248 rows=2,963 loops=1)

  • Merge Cond: (((p.query_properties ->> 'material_id'::text)) = ((s_2.query_external_identifiers ->> 'material_id'::text)))
16. 6.460 8.687 ↑ 1.0 2,963 1

Sort (cost=771.06..772.54 rows=2,963 width=431) (actual time=8.320..8.687 rows=2,963 loops=1)

  • Sort Key: ((p.query_properties ->> 'material_id'::text))
  • Sort Method: quicksort Memory: 1,713kB
17. 2.227 2.227 ↑ 1.0 2,963 1

Seq Scan on products p (cost=0.00..736.89 rows=2,963 width=431) (actual time=0.009..2.227 rows=2,963 loops=1)

18. 98.719 119.441 ↑ 1.0 28,190 1

Sort (cost=9,640.30..9,654.39 rows=28,190 width=702) (actual time=115.497..119.441 rows=28,190 loops=1)

  • Sort Key: ((s_2.query_external_identifiers ->> 'material_id'::text))
  • Sort Method: quicksort Memory: 29,422kB
19. 20.722 20.722 ↓ 1.0 28,192 1

Seq Scan on skus s_2 (cost=0.00..9,223.57 rows=28,190 width=702) (actual time=0.011..20.722 rows=28,192 loops=1)

20.          

CTE _customer_context

21. 12.849 30.271 ↑ 49.6 5 1

Unique (cost=1,961.47..1,964.57 rows=248 width=1,254) (actual time=17.355..30.271 rows=5 loops=1)

22. 15.540 17.422 ↓ 3.4 836 1

Sort (cost=1,961.47..1,961.60 rows=248 width=1,254) (actual time=17.354..17.422 rows=836 loops=1)

  • Sort Key: customer_context_versions_2.customer_context_id, customer_context_versions_2.version, customer_context_versions_2.id, customer_context_versions_2.customer_id, customer_context_versions_2.customer_label, customer_context_versions_2.customer_city, customer_context_versions_2.customer_state_province, customer_context_versions_2.channel_partner_id, customer_context_versions_2.customer_hierarchy_type, customer_context_versions_2.customer_hierarchy_level, customer_context_versions_2.channel_role, customer_context_versions_2.customer_children, customer_context_versions_2.descendant_customer_ids, customer_context_versions_2.customer_parent, customer_context_versions_2.ancestor_customer_ids, customer_context_versions_2.pay_to_customer_id, customer_context_versions_2.bagged_properties, customer_context_versions_2.query_properties, customer_context_versions_2.read_authorization, customer_context_versions_2.created_at, customer_context_versions_2.created_by, customer_context_versions_2.creator_impersonated_by, customer_context_versions_2.active_period, o_3.offer_id
  • Sort Method: quicksort Memory: 1,545kB
23. 0.098 1.882 ↓ 3.4 836 1

Nested Loop (cost=0.08..1,959.50 rows=248 width=1,254) (actual time=0.010..1.882 rows=836 loops=1)

24. 0.112 0.112 ↓ 1.7 836 1

CTE Scan on _offers_plus o_3 (cost=0.00..3.00 rows=500 width=32) (actual time=0.000..0.112 rows=836 loops=1)

25. 1.672 1.672 ↑ 1.0 1 836

Index Scan using customer_context_versions_ukey2 on customer_context_versions customer_context_versions_2 (cost=0.08..3.91 rows=1 width=1,238) (actual time=0.002..0.002 rows=1 loops=836)

  • Index Cond: (customer_context_id = o_3.earner_id)
26.          

CTE _customers

27. 0.140 30.440 ↑ 834.9 30 1

HashAggregate (cost=178.44..253.58 rows=25,048 width=32) (actual time=30.324..30.440 rows=30 loops=1)

  • Group Key: (unnest(_customer_context.descendant_customer_ids)), _customer_context.offer_id
28. 0.005 30.300 ↑ 715.7 35 1

Append (cost=0.00..153.39 rows=25,048 width=32) (actual time=17.365..30.300 rows=35 loops=1)

29. 0.015 30.292 ↑ 826.7 30 1

ProjectSet (cost=0.00..39.18 rows=24,800 width=32) (actual time=17.364..30.292 rows=30 loops=1)

30. 30.277 30.277 ↑ 49.6 5 1

CTE Scan on _customer_context (cost=0.00..1.49 rows=248 width=48) (actual time=17.358..30.277 rows=5 loops=1)

31. 0.003 0.003 ↑ 49.6 5 1

CTE Scan on _customer_context _customer_context_1 (cost=0.00..1.49 rows=248 width=32) (actual time=0.001..0.003 rows=5 loops=1)

32.          

CTE _summary

33. 0.002 271.333 ↓ 0.0 0 1

GroupAggregate (cost=3,853,602.70..4,313,803.70 rows=53,870,589 width=124) (actual time=271.333..271.333 rows=0 loops=1)

  • Group Key: c.customer_id, csal.market_year, pts_1.product_id, csal.sku_id, pts_1.bs_name
  • Filter: (sum(csal.quantity_sold) <> '0'::numeric)
34. 0.007 271.331 ↓ 0.0 0 1

Sort (cost=3,853,602.70..3,880,673.34 rows=54,141,295 width=97) (actual time=271.331..271.331 rows=0 loops=1)

  • Sort Key: c.customer_id, csal.market_year, pts_1.product_id, csal.sku_id, pts_1.bs_name
  • Sort Method: quicksort Memory: 25kB
35. 0.002 271.324 ↓ 0.0 0 1

Hash Left Join (cost=48,129.83..347,805.59 rows=54,141,295 width=97) (actual time=271.324..271.324 rows=0 loops=1)

  • Hash Cond: ((pts_1.bs_code = (bsmy.business_segment).code) AND (csal.market_year = bsmy.market_year))
36. 0.001 271.322 ↓ 0.0 0 1

Merge Left Join (cost=48,126.39..265,021.24 rows=54,141,295 width=129) (actual time=271.322..271.322 rows=0 loops=1)

  • Merge Cond: (csal.sku_id = pts_1.sku_id)
37. 0.003 271.321 ↓ 0.0 0 1

Sort (cost=37,822.54..37,987.38 rows=329,669 width=49) (actual time=271.321..271.321 rows=0 loops=1)

  • Sort Key: csal.sku_id
  • Sort Method: quicksort Memory: 25kB
38. 14.676 271.318 ↓ 0.0 0 1

Merge Join (cost=30,435.77..31,779.50 rows=329,669 width=49) (actual time=271.318..271.318 rows=0 loops=1)

  • Merge Cond: (c.customer_id = csal.customer_id)
39. 0.017 30.467 ↑ 834.9 30 1

Sort (cost=516.30..528.82 rows=25,048 width=16) (actual time=30.462..30.467 rows=30 loops=1)

  • Sort Key: c.customer_id
  • Sort Method: quicksort Memory: 26kB
40. 30.450 30.450 ↑ 834.9 30 1

CTE Scan on _customers c (cost=0.00..150.29 rows=25,048 width=16) (actual time=30.326..30.450 rows=30 loops=1)

41. 53.696 226.175 ↑ 1.0 168,988 1

Sort (cost=29,919.47..30,004.10 rows=169,257 width=49) (actual time=218.046..226.175 rows=168,988 loops=1)

  • Sort Key: csal.customer_id
  • Sort Method: quicksort Memory: 30,416kB
42. 172.479 172.479 ↓ 1.0 172,598 1

Seq Scan on customer_sales csal (cost=0.00..26,979.67 rows=169,257 width=49) (actual time=0.049..172.479 rows=172,598 loops=1)

  • Filter: (source = 'distributor_retail_sales'::text)
  • Rows Removed by Filter: 1,363,308
43. 0.000 0.000 ↓ 0.0 0

Sort (cost=10,303.84..10,512.66 rows=417,635 width=96) (never executed)

  • Sort Key: pts_1.sku_id
44. 0.000 0.000 ↓ 0.0 0

CTE Scan on _prod_to_sku pts_1 (cost=0.00..2,505.81 rows=417,635 width=96) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.39..3.39 rows=14 width=77) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Seq Scan on business_segment_market_years bsmy (cost=0.00..3.39 rows=14 width=77) (never executed)

  • Filter: (period @> CURRENT_DATE)
47.          

CTE _hierarchy

48. 0.346 503.252 ↑ 30.0 4 1

GroupAggregate (cost=2,398.17..2,398.77 rows=120 width=48) (actual time=502.959..503.252 rows=4 loops=1)

  • Group Key: _op_1.owner_id
49. 0.393 502.906 ↓ 7.0 836 1

Sort (cost=2,398.17..2,398.23 rows=120 width=48) (actual time=502.860..502.906 rows=836 loops=1)

  • Sort Key: _op_1.owner_id
  • Sort Method: quicksort Memory: 90kB
50. 470.433 502.513 ↓ 7.0 836 1

Nested Loop (cost=1,428.30..2,397.34 rows=120 width=48) (actual time=10.093..502.513 rows=836 loops=1)

  • Join Filter: ((lower((u_1.query_properties ->> 'sam_account_name'::text)) = lower(h_1.l2_manager_sam_id)) OR (lower((u_1.query_properties ->> 'sam_account_name'::text)) = lower(h_1.l3_manager_sam_id)))
  • Rows Removed by Join Filter: 358,644
51. 0.385 4.492 ↓ 1.7 836 1

Nested Loop (cost=0.06..879.00 rows=500 width=292) (actual time=0.011..4.492 rows=836 loops=1)

52. 2.435 2.435 ↓ 1.7 836 1

CTE Scan on _offers_plus _op_1 (cost=0.00..3.00 rows=500 width=16) (actual time=0.001..2.435 rows=836 loops=1)

53. 1.672 1.672 ↑ 1.0 1 836

Index Scan using users_pkey on users u_1 (cost=0.06..1.75 rows=1 width=292) (actual time=0.002..0.002 rows=1 loops=836)

  • Index Cond: (id = _op_1.owner_id)
54. 18.147 27.588 ↓ 17.9 430 836

Materialize (cost=1,428.24..1,428.35 rows=24 width=96) (actual time=0.011..0.033 rows=430 loops=836)

55. 0.087 9.441 ↓ 17.9 430 1

Subquery Scan on h_1 (cost=1,428.24..1,428.32 rows=24 width=96) (actual time=9.333..9.441 rows=430 loops=1)

56. 3.108 9.354 ↓ 17.9 430 1

Sort (cost=1,428.24..1,428.25 rows=24 width=1,200) (actual time=9.332..9.354 rows=430 loops=1)

  • Sort Key: _data_3._lineage
  • Sort Method: quicksort Memory: 138kB
57.          

CTE _traverse

58. 0.092 1.019 ↑ 9.6 492 1

Recursive Union (cost=0.00..1,076.01 rows=4,706 width=52) (actual time=0.012..1.019 rows=492 loops=1)

59. 0.091 0.091 ↑ 1.0 6 1

Seq Scan on zones (cost=0.00..92.48 rows=6 width=50) (actual time=0.010..0.091 rows=6 loops=1)

  • Filter: (parent_id IS NULL)
  • Rows Removed by Filter: 486
60. 0.348 0.836 ↑ 3.9 122 4

Hash Join (cost=0.57..95.53 rows=470 width=52) (actual time=0.080..0.209 rows=122 loops=4)

  • Hash Cond: (h.parent_id = t.id)
61. 0.320 0.320 ↑ 1.0 492 4

Seq Scan on zones h (cost=0.00..92.48 rows=492 width=62) (actual time=0.002..0.080 rows=492 loops=4)

62. 0.092 0.168 ↓ 2.0 123 4

Hash (cost=0.36..0.36 rows=60 width=52) (actual time=0.041..0.042 rows=123 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 69kB
63. 0.076 0.076 ↓ 2.0 123 4

WorkTable Scan on _traverse t (cost=0.00..0.36 rows=60 width=52) (actual time=0.001..0.019 rows=123 loops=4)

64.          

CTE _sorted

65. 3.243 4.952 ↑ 9.6 492 1

Sort (cost=182.35..184.70 rows=4,706 width=605) (actual time=4.923..4.952 rows=492 loops=1)

  • Sort Key: t_1._lineage
  • Sort Method: quicksort Memory: 503kB
66. 0.235 1.709 ↑ 9.6 492 1

Hash Join (cost=94.20..124.94 rows=4,706 width=605) (actual time=0.323..1.709 rows=492 loops=1)

  • Hash Cond: (t_1.id = z.id)
67. 1.174 1.174 ↑ 9.6 492 1

CTE Scan on _traverse t_1 (cost=0.00..28.24 rows=4,706 width=52) (actual time=0.013..1.174 rows=492 loops=1)

68. 0.141 0.300 ↑ 1.0 492 1

Hash (cost=92.48..92.48 rows=492 width=569) (actual time=0.300..0.300 rows=492 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 270kB
69. 0.159 0.159 ↑ 1.0 492 1

Seq Scan on zones z (cost=0.00..92.48 rows=492 width=569) (actual time=0.002..0.159 rows=492 loops=1)

70.          

CTE _data

71. 5.469 5.469 ↑ 9.6 492 1

CTE Scan on _sorted (cost=0.00..44.71 rows=4,706 width=356) (actual time=4.933..5.469 rows=492 loops=1)

72. 0.128 6.246 ↓ 17.9 430 1

Hash Left Join (cost=92.02..122.71 rows=24 width=1,200) (actual time=6.054..6.246 rows=430 loops=1)

  • Hash Cond: (_data_2.id = _data_3.parent_id)
73. 0.015 5.938 ↓ 1.8 42 1

Hash Left Join (cost=61.35..92.00 rows=24 width=80) (actual time=5.865..5.938 rows=42 loops=1)

  • Hash Cond: (_data_1.id = _data_2.parent_id)
74. 0.012 5.833 ↑ 1.0 24 1

Hash Left Join (cost=30.67..61.30 rows=24 width=16) (actual time=5.769..5.833 rows=24 loops=1)

  • Hash Cond: (_data.id = _data_1.parent_id)
75. 4.995 4.995 ↑ 4.0 6 1

CTE Scan on _data (cost=0.00..30.59 rows=24 width=16) (actual time=4.936..4.995 rows=6 loops=1)

  • Filter: (_level = 0)
  • Rows Removed by Filter: 486
76. 0.006 0.826 ↑ 1.0 24 1

Hash (cost=30.59..30.59 rows=24 width=32) (actual time=0.826..0.826 rows=24 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
77. 0.820 0.820 ↑ 1.0 24 1

CTE Scan on _data _data_1 (cost=0.00..30.59 rows=24 width=32) (actual time=0.006..0.820 rows=24 loops=1)

  • Filter: (_level = 1)
  • Rows Removed by Filter: 468
78. 0.010 0.090 ↓ 1.5 37 1

Hash (cost=30.59..30.59 rows=24 width=96) (actual time=0.090..0.090 rows=37 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
79. 0.080 0.080 ↓ 1.5 37 1

CTE Scan on _data _data_2 (cost=0.00..30.59 rows=24 width=96) (actual time=0.003..0.080 rows=37 loops=1)

  • Filter: (_level = 2)
  • Rows Removed by Filter: 455
80. 0.078 0.180 ↓ 17.7 425 1

Hash (cost=30.59..30.59 rows=24 width=80) (actual time=0.180..0.180 rows=425 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 70kB
81. 0.102 0.102 ↓ 17.7 425 1

CTE Scan on _data _data_3 (cost=0.00..30.59 rows=24 width=80) (actual time=0.002..0.102 rows=425 loops=1)

  • Filter: (_level = 3)
  • Rows Removed by Filter: 67
82. 442.160 1,807.690 ↑ 686,313.7 836 1

Nested Loop (cost=470,621.93..11,874,004,167.39 rows=573,758,230 width=858) (actual time=1,359.084..1,807.690 rows=836 loops=1)

83. 0.929 1,361.350 ↑ 684,597.9 836 1

Merge Left Join (cost=470,605.20..2,761,399.89 rows=572,323,844 width=1,094) (actual time=1,358.287..1,361.350 rows=836 loops=1)

  • Merge Cond: (_op.product_id = pts.product_id)
84. 1.227 1,208.261 ↑ 327.8 836 1

Merge Left Join (cost=460,301.36..461,667.70 rows=274,078 width=1,078) (actual time=1,206.631..1,208.261 rows=836 loops=1)

  • Merge Cond: (_op.product_id = ppp.product_id)
85. 1.090 877.768 ↑ 323.0 836 1

Sort (cost=457,680.64..457,815.65 rows=270,028 width=1,046) (actual time=877.559..877.768 rows=836 loops=1)

  • Sort Key: _op.product_id
  • Sort Method: quicksort Memory: 1,710kB
86. 0.346 876.678 ↑ 323.0 836 1

Hash Right Join (cost=34.05..364,468.59 rows=270,028 width=1,046) (actual time=876.342..876.678 rows=836 loops=1)

  • Hash Cond: (s.product_id = _op.product_id)
87. 271.335 271.335 ↓ 0.0 0 1

CTE Scan on _summary s (cost=0.00..323,223.53 rows=53,870,589 width=48) (actual time=271.335..271.335 rows=0 loops=1)

88. 0.566 604.997 ↓ 836.0 836 1

Hash (cost=34.05..34.05 rows=1 width=1,014) (actual time=604.997..604.997 rows=836 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 944kB
89. 1.011 604.431 ↓ 836.0 836 1

Nested Loop Left Join (cost=16.89..34.05 rows=1 width=1,014) (actual time=594.961..604.431 rows=836 loops=1)

  • Join Filter: (_op.owner_id = _hier.owner_id)
  • Rows Removed by Join Filter: 2,508
90. 0.984 100.148 ↓ 836.0 836 1

Nested Loop (cost=16.89..32.91 rows=1 width=998) (actual time=91.777..100.148 rows=836 loops=1)

91. 0.000 95.820 ↓ 836.0 836 1

Nested Loop Left Join (cost=0.17..12.18 rows=1 width=496) (actual time=91.747..95.820 rows=836 loops=1)

92. 0.387 94.195 ↓ 836.0 836 1

Nested Loop Left Join (cost=0.08..8.09 rows=1 width=480) (actual time=91.738..94.195 rows=836 loops=1)

93. 92.136 92.136 ↓ 836.0 836 1

CTE Scan on _offers_plus _op (cost=0.00..4.00 rows=1 width=464) (actual time=91.723..92.136 rows=836 loops=1)

  • Filter: (((earner_type = 'CustomerContext'::text) OR (earner_type = 'Customer'::text)) AND ((payee_type = 'CustomerContext'::text) OR (payee_type = 'Customer'::text)))
94. 1.672 1.672 ↑ 1.0 1 836

Index Scan using customer_context_versions_ukey2 on customer_context_versions (cost=0.08..4.09 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=836)

  • Index Cond: (customer_context_id = _op.earner_id)
95. 1.672 1.672 ↓ 0.0 0 836

Index Scan using customer_context_versions_ukey2 on customer_context_versions customer_context_versions_1 (cost=0.08..4.09 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=836)

  • Index Cond: (customer_context_id = _op.payee_id)
96. 0.836 3.344 ↑ 1.0 1 836

Bitmap Heap Scan on customers ce (cost=16.73..20.73 rows=1 width=582) (actual time=0.004..0.004 rows=1 loops=836)

  • Recheck Cond: ((id = customer_context_versions.customer_id) OR (id = _op.earner_id))
  • Filter: (((_op.earner_type = 'CustomerContext'::text) AND (id = customer_context_versions.customer_id)) OR ((_op.earner_type = 'Customer'::text) AND (id = _op.earner_id)))
  • Heap Blocks: exact=836
97. 0.836 2.508 ↓ 0.0 0 836

BitmapOr (cost=16.73..16.73 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=836)

98. 0.836 0.836 ↑ 1.0 1 836

Bitmap Index Scan on customers_pkey (cost=0.00..0.10 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=836)

  • Index Cond: (id = customer_context_versions.customer_id)
99. 0.836 0.836 ↓ 0.0 0 836

Bitmap Index Scan on customers_pkey (cost=0.00..2.09 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=836)

  • Index Cond: (id = _op.earner_id)
100. 503.272 503.272 ↑ 30.0 4 836

CTE Scan on _hierarchy _hier (cost=0.00..0.72 rows=120 width=48) (actual time=0.602..0.602 rows=4 loops=836)

101. 0.726 329.266 ↓ 12.1 2,454 1

Sort (cost=2,620.73..2,620.83 rows=203 width=48) (actual time=329.062..329.266 rows=2,454 loops=1)

  • Sort Key: ppp.product_id
  • Sort Method: quicksort Memory: 189kB
102. 0.177 328.540 ↓ 8.9 1,798 1

Subquery Scan on ppp (cost=2,617.10..2,619.17 rows=203 width=48) (actual time=327.449..328.540 rows=1,798 loops=1)

103. 0.302 328.363 ↓ 8.9 1,798 1

Hash Left Join (cost=2,617.10..2,618.56 rows=203 width=112) (actual time=327.448..328.363 rows=1,798 loops=1)

  • Hash Cond: (_from_skus.product_id = o.product_id)
104.          

CTE _from_skus

105. 1.774 325.762 ↓ 9.1 1,795 1

Unique (cost=2,611.68..2,611.87 rows=198 width=112) (actual time=322.473..325.762 rows=1,795 loops=1)

106. 12.370 323.988 ↓ 106.1 21,014 1

Sort (cost=2,611.68..2,611.77 rows=198 width=112) (actual time=322.472..323.988 rows=21,014 loops=1)

  • Sort Key: prioritized_sku_prices.product_id, prioritized_sku_prices.price DESC
  • Sort Method: quicksort Memory: 2,437kB
107. 3.752 311.618 ↓ 106.1 21,014 1

Subquery Scan on prioritized_sku_prices (cost=2,608.76..2,610.16 rows=198 width=112) (actual time=303.650..311.618 rows=21,014 loops=1)

  • Filter: (prioritized_sku_prices.product_id IS NOT NULL)
108. 3.062 307.866 ↓ 105.6 21,014 1

Unique (cost=2,608.76..2,609.57 rows=199 width=361) (actual time=303.647..307.866 rows=21,014 loops=1)

109.          

CTE _sorted

110. 21.261 220.170 ↓ 9.1 22,171 1

WindowAgg (cost=2,476.63..2,488.77 rows=2,429 width=333) (actual time=195.523..220.170 rows=22,171 loops=1)

111. 17.387 198.909 ↓ 9.1 22,171 1

Sort (cost=2,476.63..2,477.84 rows=2,429 width=325) (actual time=195.511..198.909 rows=22,171 loops=1)

  • Sort Key: prioritized_prices.sku_id, prioritized_prices.start_date
  • Sort Method: quicksort Memory: 6,658kB
112. 5.911 181.522 ↓ 9.1 22,171 1

Subquery Scan on prioritized_prices (cost=2,379.74..2,449.31 rows=2,429 width=325) (actual time=167.756..181.522 rows=22,171 loops=1)

  • Filter: (prioritized_prices.price_type <> 'unpublished_price'::text)
  • Rows Removed by Filter: 1,012
113. 6.590 175.611 ↓ 9.5 23,183 1

Unique (cost=2,379.74..2,440.77 rows=2,441 width=353) (actual time=167.752..175.611 rows=23,183 loops=1)

114.          

CTE _sorted

115. 22.619 126.358 ↑ 1.0 24,409 1

WindowAgg (cost=1,560.21..1,731.07 rows=24,409 width=186) (actual time=102.247..126.358 rows=24,409 loops=1)

116. 38.359 103.739 ↑ 1.0 24,409 1

Sort (cost=1,560.21..1,572.41 rows=24,409 width=185) (actual time=102.235..103.739 rows=24,409 loops=1)

  • Sort Key: all_approved_sku_prices.material_id, all_approved_sku_prices.market_year, all_approved_sku_prices.customer_id, all_approved_sku_prices.start_date, all_approved_sku_prices.priority
  • Sort Method: quicksort Memory: 7,255kB
117. 20.636 65.380 ↑ 1.0 24,409 1

WindowAgg (cost=1,057.99..1,204.45 rows=24,409 width=185) (actual time=43.445..65.380 rows=24,409 loops=1)

118. 38.056 44.744 ↑ 1.0 24,409 1

Sort (cost=1,057.99..1,070.20 rows=24,409 width=177) (actual time=43.431..44.744 rows=24,409 loops=1)

  • Sort Key: all_approved_sku_prices.material_id, all_approved_sku_prices.market_year, all_approved_sku_prices.customer_id, all_approved_sku_prices.start_date
  • Sort Method: quicksort Memory: 7,255kB
119. 6.688 6.688 ↑ 1.0 24,409 1

Seq Scan on all_approved_sku_prices (cost=0.00..702.23 rows=24,409 width=177) (actual time=0.015..6.688 rows=24,409 loops=1)

120.          

CTE _final

121. 141.802 141.802 ↑ 1.0 24,409 1

CTE Scan on _sorted _sorted_1 (cost=0.00..146.45 rows=24,409 width=353) (actual time=102.251..141.802 rows=24,409 loops=1)

122. 16.860 169.021 ↑ 1.0 24,409 1

Sort (cost=502.22..514.42 rows=24,409 width=353) (actual time=167.750..169.021 rows=24,409 loops=1)

  • Sort Key: _final.material_id, _final.market_year, _final.customer_id, _final.start_date, _final.priority
  • Sort Method: quicksort Memory: 7,255kB
123. 152.161 152.161 ↑ 1.0 24,409 1

CTE Scan on _final (cost=0.00..146.45 rows=24,409 width=353) (actual time=102.254..152.161 rows=24,409 loops=1)

124.          

CTE _enriched

125. 9.235 271.374 ↓ 9.1 22,171 1

Merge Left Join (cost=83.78..95.16 rows=2,429 width=361) (actual time=259.714..271.374 rows=22,171 loops=1)

  • Merge Cond: ((p1.sku_id = p2.sku_id) AND (((p1.sort_order + 1)) = p2.sort_order))
126. 12.799 250.385 ↓ 9.1 22,171 1

Sort (cost=41.89..43.11 rows=2,429 width=333) (actual time=249.139..250.385 rows=22,171 loops=1)

  • Sort Key: p1.sku_id, ((p1.sort_order + 1))
  • Sort Method: quicksort Memory: 6,659kB
127. 237.586 237.586 ↓ 9.1 22,171 1

CTE Scan on _sorted p1 (cost=0.00..14.57 rows=2,429 width=333) (actual time=195.528..237.586 rows=22,171 loops=1)

128. 6.918 11.754 ↓ 9.1 22,171 1

Sort (cost=41.89..43.11 rows=2,429 width=60) (actual time=10.566..11.754 rows=22,171 loops=1)

  • Sort Key: p2.sku_id, p2.sort_order
  • Sort Method: quicksort Memory: 2,501kB
129. 4.836 4.836 ↓ 9.1 22,171 1

CTE Scan on _sorted p2 (cost=0.00..14.57 rows=2,429 width=60) (actual time=0.002..4.836 rows=22,171 loops=1)

130. 10.920 304.804 ↓ 27.4 22,169 1

Sort (cost=24.83..25.23 rows=810 width=361) (actual time=303.646..304.804 rows=22,169 loops=1)

  • Sort Key: _enriched.sku_id, _enriched.start_date DESC
  • Sort Method: quicksort Memory: 3,886kB
131. 293.884 293.884 ↓ 27.4 22,169 1

CTE Scan on _enriched (cost=0.00..17.00 rows=810 width=361) (actual time=259.721..293.884 rows=22,169 loops=1)

  • Filter: (start_date <= CURRENT_DATE)
  • Rows Removed by Filter: 2
132.          

CTE _overrides

133. 0.024 0.024 ↓ 3.6 18 1

Seq Scan on prioritized_product_price_overrides (cost=0.00..1.01 rows=5 width=96) (actual time=0.021..0.024 rows=18 loops=1)

134. 0.477 328.053 ↓ 8.9 1,798 1

Hash Right Join (cost=4.16..5.46 rows=203 width=48) (actual time=327.429..328.053 rows=1,798 loops=1)

  • Hash Cond: (s_1.product_id = _from_skus.product_id)
135. 0.161 0.161 ↓ 9.1 1,795 1

CTE Scan on _from_skus s_1 (cost=0.00..1.19 rows=198 width=48) (actual time=0.000..0.161 rows=1,795 loops=1)

136. 0.258 327.415 ↓ 8.9 1,798 1

Hash (cost=3.45..3.45 rows=203 width=16) (actual time=327.415..327.415 rows=1,798 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 101kB
137. 0.804 327.157 ↓ 8.9 1,798 1

HashAggregate (cost=2.23..2.84 rows=203 width=16) (actual time=326.952..327.157 rows=1,798 loops=1)

  • Group Key: _from_skus.product_id
138. 0.115 326.353 ↓ 8.9 1,813 1

Append (cost=0.00..2.13 rows=203 width=16) (actual time=322.478..326.353 rows=1,813 loops=1)

139. 326.202 326.202 ↓ 9.1 1,795 1

CTE Scan on _from_skus (cost=0.00..1.19 rows=198 width=16) (actual time=322.476..326.202 rows=1,795 loops=1)

140. 0.036 0.036 ↓ 3.6 18 1

CTE Scan on _overrides (cost=0.00..0.03 rows=5 width=16) (actual time=0.024..0.036 rows=18 loops=1)

141. 0.004 0.008 ↓ 3.6 18 1

Hash (cost=0.03..0.03 rows=5 width=32) (actual time=0.008..0.008 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
142. 0.004 0.004 ↓ 3.6 18 1

CTE Scan on _overrides o (cost=0.00..0.03 rows=5 width=32) (actual time=0.001..0.004 rows=18 loops=1)

143. 1.637 152.160 ↑ 115.8 3,605 1

Sort (cost=10,303.84..10,512.66 rows=417,635 width=48) (actual time=151.647..152.160 rows=3,605 loops=1)

  • Sort Key: pts.product_id
  • Sort Method: quicksort Memory: 328kB
144. 150.523 150.523 ↑ 141.0 2,963 1

CTE Scan on _prod_to_sku pts (cost=0.00..2,505.81 rows=417,635 width=48) (actual time=123.841..150.523 rows=2,963 loops=1)

145. 1.672 4.180 ↑ 1.0 1 836

Bitmap Heap Scan on customers cp (cost=16.73..20.73 rows=1 width=582) (actual time=0.005..0.005 rows=1 loops=836)

  • Recheck Cond: ((id = customer_context_versions_1.pay_to_customer_id) OR (id = _op.payee_id))
  • Filter: (((_op.payee_type = 'CustomerContext'::text) AND (id = customer_context_versions_1.pay_to_customer_id)) OR ((_op.payee_type = 'Customer'::text) AND (id = _op.payee_id)))
  • Heap Blocks: exact=836
146. 0.000 2.508 ↓ 0.0 0 836

BitmapOr (cost=16.73..16.73 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=836)

147. 0.000 0.000 ↓ 0.0 0 836

Bitmap Index Scan on customers_pkey (cost=0.00..0.10 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=836)

  • Index Cond: (id = customer_context_versions_1.pay_to_customer_id)
148. 2.508 2.508 ↑ 1.0 1 836

Bitmap Index Scan on customers_pkey (cost=0.00..2.09 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=836)

  • Index Cond: (id = _op.payee_id)
Planning time : 5.279 ms
Execution time : 1,814.271 ms