explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XzjD

Settings
# exclusive inclusive rows x rows loops node
1. 19,240.961 19,240.961 ↑ 5.0 115,713 1

CTE Scan on subscription_fact_intermediate (cost=311,437,903.93..311,449,387.19 rows=574,163 width=724) (actual time=12,790.599..19,240.961 rows=115,713 loops=1)

2.          

CTE skutoexclude

3. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on orders_ordersettings (cost=0.00..12.66 rows=1,000 width=29) (actual time=0.012..0.012 rows=0 loops=1)

  • Filter: ((key)::text = 'order_milk_estimation_sku_ids_to_include'::text)
  • Rows Removed by Filter: 53
4.          

CTE skutoinclude

5. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on orders_ordersettings orders_ordersettings_1 (cost=0.00..12.66 rows=1,000 width=29) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: ((key)::text = 'order_milk_estimation_sku_ids_to_exclude'::text)
  • Rows Removed by Filter: 53
6.          

CTE bizcategorytoexclude

7. 0.040 0.040 ↑ 1,000.0 1 1

Seq Scan on orders_ordersettings orders_ordersettings_2 (cost=0.00..12.66 rows=1,000 width=29) (actual time=0.022..0.040 rows=1 loops=1)

  • Filter: ((key)::text = 'order_milk_estimation_biz_category_ids_to_include'::text)
  • Rows Removed by Filter: 52
8.          

CTE milkskuids

9. 0.100 5.865 ↑ 37.5 171 1

HashSetOp Except (cost=484.98..597.58 rows=6,408 width=4) (actual time=5.830..5.865 rows=171 loops=1)

10. 0.061 5.765 ↑ 43.3 171 1

Append (cost=484.98..579.06 rows=7,408 width=4) (actual time=5.576..5.765 rows=171 loops=1)

11. 0.072 5.694 ↑ 37.5 171 1

Result (cost=484.98..549.06 rows=6,408 width=4) (actual time=5.574..5.694 rows=171 loops=1)

12. 0.104 5.622 ↑ 37.5 171 1

HashAggregate (cost=484.98..549.06 rows=6,408 width=4) (actual time=5.574..5.622 rows=171 loops=1)

  • Group Key: sf.sku_id
13. 0.061 5.518 ↑ 37.5 171 1

Append (cost=27.00..468.96 rows=6,408 width=4) (actual time=0.253..5.518 rows=171 loops=1)

14. 2.741 5.434 ↑ 31.6 171 1

Hash Join (cost=27.00..384.88 rows=5,408 width=4) (actual time=0.253..5.434 rows=171 loops=1)

  • Hash Cond: (sf.biz_category_l1_id = bizcategorytoexclude.biz_category_id)
15. 2.644 2.644 ↓ 1.0 10,834 1

Seq Scan on sku_fact sf (cost=0.00..209.16 rows=10,816 width=8) (actual time=0.006..2.644 rows=10,834 loops=1)

16. 0.001 0.049 ↑ 200.0 1 1

Hash (cost=24.50..24.50 rows=200 width=4) (actual time=0.049..0.049 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.007 0.048 ↑ 200.0 1 1

HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.047..0.048 rows=1 loops=1)

  • Group Key: bizcategorytoexclude.biz_category_id
18. 0.041 0.041 ↑ 1,000.0 1 1

CTE Scan on bizcategorytoexclude (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.023..0.041 rows=1 loops=1)

19. 0.023 0.023 ↓ 0.0 0 1

CTE Scan on skutoexclude (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.023..0.023 rows=0 loops=1)

20. 0.000 0.010 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.00..30.00 rows=1,000 width=4) (actual time=0.010..0.010 rows=0 loops=1)

21. 0.010 0.010 ↓ 0.0 0 1

CTE Scan on skutoinclude (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.010..0.010 rows=0 loops=1)

22.          

CTE customer_price_segment

23. 76.906 569.310 ↓ 67.3 159,195 1

Subquery Scan on primary_seg (cost=396,590.73..411,956.99 rows=2,364 width=8) (actual time=279.875..569.310 rows=159,195 loops=1)

  • Filter: (primary_seg.rank_order = 1)
  • Rows Removed by Filter: 22,449
24. 144.402 492.404 ↑ 2.6 181,644 1

WindowAgg (cost=396,590.73..406,046.89 rows=472,808 width=12) (actual time=279.869..492.404 rows=181,644 loops=1)

25. 205.582 348.002 ↑ 2.6 181,644 1

Sort (cost=396,590.73..397,772.75 rows=472,808 width=12) (actual time=279.851..348.002 rows=181,644 loops=1)

  • Sort Key: cust_seg.customer_id, price_seg.rank
  • Sort Method: external merge Disk: 3,896kB
26. 80.328 142.420 ↑ 2.6 181,644 1

Nested Loop (cost=0.56..343,944.96 rows=472,808 width=12) (actual time=0.038..142.420 rows=181,644 loops=1)

27. 0.016 0.016 ↑ 1.2 4 1

Seq Scan on pricing_pricingsegmentregistry price_seg (cost=0.00..1.36 rows=5 width=8) (actual time=0.012..0.016 rows=4 loops=1)

  • Filter: active
  • Rows Removed by Filter: 36
28. 62.076 62.076 ↑ 2.1 45,411 4

Index Scan using segmentation_customersegmentmapping_3e8e3332 on segmentation_customersegmentmapping cust_seg (cost=0.56..67,843.10 rows=94,562 width=8) (actual time=0.020..15.519 rows=45,411 loops=4)

  • Index Cond: (segment_id = price_seg.segment_id)
29.          

CTE subscription_fact_intermediate

30. 1,016.279 19,099.547 ↑ 5.0 115,713 1

Merge Join (cost=1,200,589.39..311,025,311.37 rows=574,163 width=58) (actual time=12,790.599..19,099.547 rows=115,713 loops=1)

  • Merge Cond: ((subscription.sku_id = hsku.sku_id) AND (address.hub_id = hsku.hub_id))
31. 2,828.628 10,917.742 ↓ 1.7 746,042 1

Sort (cost=416,339.70..417,434.76 rows=438,025 width=50) (actual time=10,587.157..10,917.742 rows=746,042 loops=1)

  • Sort Key: subscription.sku_id, address.hub_id
  • Sort Method: external merge Disk: 42,408kB
32. 562.565 8,089.114 ↓ 1.7 746,042 1

Merge Left Join (cost=352,445.43..360,322.32 rows=438,025 width=50) (actual time=6,181.538..8,089.114 rows=746,042 loops=1)

  • Merge Cond: ((subscription.sku_id = segment_discount.sku_id) AND (customer_price_segment.segment_id = segment_discount.segment_id))
33. 1,569.180 6,759.516 ↓ 1.7 732,656 1

Sort (cost=306,737.35..307,832.41 rows=438,025 width=40) (actual time=6,181.527..6,759.516 rows=732,656 loops=1)

  • Sort Key: subscription.sku_id, customer_price_segment.segment_id
  • Sort Method: external merge Disk: 40,512kB
34. 469.078 5,190.336 ↓ 1.7 732,656 1

Hash Right Join (cost=249,466.26..253,712.47 rows=438,025 width=40) (actual time=4,337.163..5,190.336 rows=732,656 loops=1)

  • Hash Cond: (customer_price_segment.customer_id = subscription.customer_id)
35. 664.342 664.342 ↓ 67.3 159,195 1

CTE Scan on customer_price_segment (cost=0.00..47.28 rows=2,364 width=8) (actual time=279.878..664.342 rows=159,195 loops=1)

36. 355.881 4,056.916 ↓ 1.7 732,656 1

Hash (cost=240,567.95..240,567.95 rows=438,025 width=36) (actual time=4,056.916..4,056.916 rows=732,656 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,756kB
37. 910.920 3,701.035 ↓ 1.7 732,656 1

Hash Join (cost=169,562.83..240,567.95 rows=438,025 width=36) (actual time=2,072.476..3,701.035 rows=732,656 loops=1)

  • Hash Cond: (subscription.address_id = address.id)
38. 723.389 805.219 ↓ 1.0 732,667 1

Bitmap Heap Scan on subscriptions_subscription subscription (cost=23,293.35..68,600.97 rows=730,650 width=32) (actual time=87.181..805.219 rows=732,667 loops=1)

  • Recheck Cond: (cancel_date_time IS NULL)
  • Filter: (start_date <= (('now'::cstring)::date + '7 days'::interval))
  • Rows Removed by Filter: 72
  • Heap Blocks: exact=28,102
39. 81.830 81.830 ↑ 1.0 734,757 1

Bitmap Index Scan on index_customer_cancel_date_subscription (cost=0.00..23,110.69 rows=734,881 width=0) (actual time=81.830..81.830 rows=734,757 loops=1)

  • Index Cond: (cancel_date_time IS NULL)
40. 681.970 1,984.896 ↑ 1.0 1,961,041 1

Hash (cost=114,040.40..114,040.40 rows=1,964,406 width=8) (actual time=1,984.896..1,984.896 rows=1,961,041 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 3,418kB
41. 1,302.926 1,302.926 ↑ 1.0 1,961,041 1

Seq Scan on address_v3_addressv3cached address (cost=0.00..114,040.40 rows=1,964,406 width=8) (actual time=0.007..1,302.926 rows=1,961,041 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1,313,451
42. 138.173 767.033 ↓ 1.0 335,413 1

Materialize (cost=45,708.08..47,331.47 rows=324,677 width=22) (actual time=450.536..767.033 rows=335,413 loops=1)

43. 498.008 628.860 ↓ 1.0 324,798 1

Sort (cost=45,708.08..46,519.77 rows=324,677 width=22) (actual time=450.532..628.860 rows=324,798 loops=1)

  • Sort Key: segment_discount.sku_id, segment_discount.segment_id
  • Sort Method: external merge Disk: 10,168kB
44. 130.852 130.852 ↓ 1.0 324,798 1

Seq Scan on pricing_segmentskupricing segment_discount (cost=0.00..9,325.60 rows=324,677 width=22) (actual time=0.010..130.852 rows=324,798 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 167,240
45. 376.076 3,115.571 ↓ 1.1 940,958 1

Materialize (cost=783,751.71..788,083.85 rows=866,428 width=20) (actual time=2,203.288..3,115.571 rows=940,958 loops=1)

46. 1,367.286 2,739.495 ↑ 1.0 834,757 1

Sort (cost=783,751.71..785,917.78 rows=866,428 width=20) (actual time=2,203.284..2,739.495 rows=834,757 loops=1)

  • Sort Key: hsku.sku_id, hsku.hub_id
  • Sort Method: external merge Disk: 21,088kB
47. 590.802 1,372.209 ↑ 1.0 853,821 1

Hash Join (cost=660.11..680,531.96 rows=866,428 width=20) (actual time=13.404..1,372.209 rows=853,821 loops=1)

  • Hash Cond: (hsku.sku_id = products_sku.id)
48. 768.030 768.030 ↑ 2.0 867,186 1

Seq Scan on products_hubsku hsku (cost=0.00..664,709.96 rows=1,732,696 width=16) (actual time=0.009..768.030 rows=867,186 loops=1)

49. 3.263 13.377 ↓ 2.0 10,663 1

Hash (cost=592.44..592.44 rows=5,414 width=4) (actual time=13.377..13.377 rows=10,663 loops=1)

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 503kB
50. 4.058 10.114 ↓ 2.0 10,663 1

Index Only Scan using products_sku_pkey on products_sku (cost=146.97..592.44 rows=5,414 width=4) (actual time=6.134..10.114 rows=10,663 loops=1)

  • Filter: (NOT (hashed SubPlan 8))
  • Rows Removed by Filter: 171
  • Heap Fetches: 58
51.          

SubPlan (for Index Only Scan)

52. 0.121 6.056 ↑ 1.2 171 1

HashAggregate (cost=144.18..146.18 rows=200 width=4) (actual time=6.018..6.056 rows=171 loops=1)

  • Group Key: milkskuids.sku_id
53. 5.935 5.935 ↑ 37.5 171 1

CTE Scan on milkskuids (cost=0.00..128.16 rows=6,408 width=4) (actual time=5.833..5.935 rows=171 loops=1)

54.          

SubPlan (for Merge Join)

55. 694.278 2,661.399 ↑ 1.0 1 115,713

Aggregate (cost=269.76..269.77 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=115,713)

56. 1,967.121 1,967.121 ↑ 6.9 11 115,713

Index Scan using subscriptions_balancesummary_ef42673f on subscriptions_balancesummary summary (cost=0.44..269.57 rows=76 width=4) (actual time=0.005..0.017 rows=11 loops=115,713)

  • Index Cond: (subscription_id = subscription.id)
57. 578.565 1,388.556 ↑ 1.0 1 115,713

Aggregate (cost=269.76..269.77 rows=1 width=3) (actual time=0.012..0.012 rows=1 loops=115,713)

58. 809.991 809.991 ↑ 6.9 11 115,713

Index Scan using subscriptions_balancesummary_ef42673f on subscriptions_balancesummary summary_1 (cost=0.44..269.57 rows=76 width=3) (actual time=0.002..0.007 rows=11 loops=115,713)

  • Index Cond: (subscription_id = subscription.id)
Planning time : 2.504 ms
Execution time : 19,288.656 ms