explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kO1a

Settings
# exclusive inclusive rows x rows loops node
1. 5,783.614 994,438.260 ↑ 888.0 9,672 1

GroupAggregate (cost=10,684,370.98..11,607,710.41 rows=8,589,204 width=198) (actual time=986,441.003..994,438.260 rows=9,672 loops=1)

  • Group Key: f.org_key, f.store_key, f.group_key
2. 12,561.000 988,654.646 ↓ 1.0 8,592,480 1

Sort (cost=10,684,370.98..10,705,843.99 rows=8,589,204 width=95) (actual time=986,426.613..988,654.646 rows=8,592,480 loops=1)

  • Sort Key: f.org_key, f.store_key, f.group_key
  • Sort Method: external merge Disk: 456,016kB
3. 1,505.460 976,093.646 ↓ 1.0 8,592,480 1

Subquery Scan on f (cost=8,030,480.70..8,374,048.86 rows=8,589,204 width=95) (actual time=965,743.410..976,093.646 rows=8,592,480 loops=1)

4. 6,713.986 974,588.186 ↓ 1.0 8,592,480 1

WindowAgg (cost=8,030,480.70..8,288,156.82 rows=8,589,204 width=107) (actual time=965,743.406..974,588.186 rows=8,592,480 loops=1)

5. 13,282.549 967,874.200 ↓ 1.0 8,592,480 1

Sort (cost=8,030,480.70..8,051,953.71 rows=8,589,204 width=87) (actual time=965,740.703..967,874.200 rows=8,592,480 loops=1)

  • Sort Key: sc.org_key, sc.store_key, sk.option_key
  • Sort Method: external merge Disk: 312,472kB
6. 3,334.021 954,591.651 ↓ 1.0 8,592,480 1

Hash Left Join (cost=4,997,602.83..5,808,232.58 rows=8,589,204 width=87) (actual time=928,730.866..954,591.651 rows=8,592,480 loops=1)

  • Hash Cond: ((sk.org_key = nv.org_key) AND (st.store_key = nv.store_key) AND (sk.sku_key = nv.sku_key))
7. 5,875.346 35,250.753 ↓ 1.0 8,592,480 1

Hash Left Join (cost=792,252.29..1,535,242.06 rows=8,589,204 width=51) (actual time=12,722.536..35,250.753 rows=8,592,480 loops=1)

  • Hash Cond: ((sk.org_key = ss.org_key) AND (st.store_key = ss.store_key) AND (sk.sku_key = ss.sku_key))
8. 7,720.843 17,634.097 ↓ 1.0 8,592,480 1

Hash Left Join (cost=104,878.20..644,783.99 rows=8,589,204 width=35) (actual time=980.954..17,634.097 rows=8,592,480 loops=1)

  • Hash Cond: ((sk.org_key = p.org_key) AND (st.store_key = p.store_key) AND (sk.sku_key = p.sku_key))
9. 5,882.346 9,171.165 ↓ 1.0 8,592,480 1

Hash Left Join (cost=35,178.46..382,115.26 rows=8,589,204 width=30) (actual time=238.112..9,171.165 rows=8,592,480 loops=1)

  • Hash Cond: ((sk.org_key = sc.org_key) AND (st.store_key = sc.store_key) AND (sk.sku_key = sc.sku_key))
10. 1,595.747 3,114.717 ↓ 1.0 8,592,480 1

Hash Join (cost=18,102.26..194,955.06 rows=8,589,204 width=22) (actual time=63.387..3,114.717 rows=8,592,480 loops=1)

  • Hash Cond: (sk.org_key = st.org_key)
11. 290.935 1,518.912 ↓ 1.0 110,160 1

Hash Right Join (cost=18,090.38..98,039.34 rows=110,118 width=20) (actual time=63.314..1,518.912 rows=110,160 loops=1)

  • Hash Cond: ((ap.org_key = sk.org_key) AND (ap.option_key = sk.option_key))
12. 84.357 1,165.612 ↓ 2.9 13,654 1

Subquery Scan on ap (cost=0.42..78,674.69 rows=4,645 width=12) (actual time=0.032..1,165.612 rows=13,654 loops=1)

  • Filter: (ap.rn = 1)
  • Rows Removed by Filter: 915,330
13. 573.050 1,081.255 ↑ 1.0 928,984 1

WindowAgg (cost=0.42..67,062.39 rows=928,984 width=24) (actual time=0.030..1,081.255 rows=928,984 loops=1)

14. 508.205 508.205 ↑ 1.0 928,984 1

Index Scan using options_weekly_org_key_option_key_year_week_idx on options_weekly (cost=0.42..46,160.25 rows=928,984 width=16) (actual time=0.016..508.205 rows=928,984 loops=1)

15. 25.649 62.365 ↓ 1.0 110,160 1

Hash (cost=15,900.18..15,900.18 rows=110,118 width=16) (actual time=62.364..62.365 rows=110,160 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,606kB
16. 36.716 36.716 ↓ 1.0 110,160 1

Seq Scan on skus sk (cost=0.00..15,900.18 rows=110,118 width=16) (actual time=0.008..36.716 rows=110,160 loops=1)

17. 0.016 0.058 ↑ 1.0 78 1

Hash (cost=10.91..10.91 rows=78 width=4) (actual time=0.058..0.058 rows=78 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 0.042 0.042 ↑ 1.0 78 1

Seq Scan on stores st (cost=0.00..10.91 rows=78 width=4) (actual time=0.011..0.042 rows=78 loops=1)

  • Filter: (store_status <> 'closed_store'::text)
  • Rows Removed by Filter: 74
19. 122.453 174.102 ↑ 1.1 325,553 1

Hash (cost=8,881.80..8,881.80 rows=366,080 width=12) (actual time=174.102..174.102 rows=325,553 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,782kB
20. 51.649 51.649 ↑ 1.1 325,553 1

Seq Scan on stock_current sc (cost=0.00..8,881.80 rows=366,080 width=12) (actual time=0.008..51.649 rows=325,553 loops=1)

21. 475.269 742.089 ↑ 1.0 1,617,154 1

Hash (cost=33,502.54..33,502.54 rows=1,617,154 width=13) (actual time=742.089..742.089 rows=1,617,154 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 3,394kB
22. 266.820 266.820 ↑ 1.0 1,617,154 1

Seq Scan on predictions_skus_n4w p (cost=0.00..33,502.54 rows=1,617,154 width=13) (actual time=0.010..266.820 rows=1,617,154 loops=1)

23. 57.705 11,741.310 ↑ 1.2 178,340 1

Hash (cost=682,447.50..682,447.50 rows=210,891 width=24) (actual time=11,741.310..11,741.310 rows=178,340 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,939kB
24. 28.824 11,683.605 ↑ 1.2 178,340 1

Subquery Scan on ss (cost=674,919.71..682,447.50 rows=210,891 width=24) (actual time=11,428.763..11,683.605 rows=178,340 loops=1)

25. 128.487 11,654.781 ↑ 1.2 178,340 1

GroupAggregate (cost=674,919.71..680,338.59 rows=210,891 width=24) (actual time=11,428.762..11,654.781 rows=178,340 loops=1)

  • Group Key: stock_sales.org_key, stock_sales.store_key, stock_sales.sku_key
26. 325.679 11,526.294 ↓ 1.7 378,297 1

Sort (cost=674,919.71..675,471.37 rows=220,665 width=12) (actual time=11,428.750..11,526.294 rows=378,297 loops=1)

  • Sort Key: stock_sales.org_key, stock_sales.store_key, stock_sales.sku_key
  • Sort Method: external merge Disk: 8,176kB
27. 11,200.615 11,200.615 ↓ 1.7 378,297 1

Seq Scan on stock_sales (cost=0.00..651,561.03 rows=220,665 width=12) (actual time=3.302..11,200.615 rows=378,297 loops=1)

  • Filter: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))
  • Rows Removed by Filter: 21,446,949
28. 38.805 916,006.877 ↓ 112.0 38,078 1

Hash (cost=4,205,344.59..4,205,344.59 rows=340 width=48) (actual time=916,006.877..916,006.877 rows=38,078 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,595kB
29. 6.306 915,968.072 ↓ 112.0 38,078 1

Subquery Scan on nv (cost=4,205,133.06..4,205,344.59 rows=340 width=48) (actual time=915,612.264..915,968.072 rows=38,078 loops=1)

30. 35.509 915,961.766 ↓ 112.0 38,078 1

GroupAggregate (cost=4,205,133.06..4,205,341.19 rows=340 width=48) (actual time=915,612.260..915,961.766 rows=38,078 loops=1)

  • Group Key: s1.org_key, s1.store_key, s1.sku_key
31. 164.342 915,926.257 ↓ 12.6 42,815 1

GroupAggregate (cost=4,205,133.06..4,205,260.48 rows=3,398 width=52) (actual time=915,610.504..915,926.257 rows=42,815 loops=1)

  • Group Key: s1.org_key, s1.store_key, s1.sku_key, s1.date_key
32. 794.327 915,761.915 ↓ 120.9 410,684 1

Sort (cost=4,205,133.06..4,205,141.55 rows=3,398 width=20) (actual time=915,608.906..915,761.915 rows=410,684 loops=1)

  • Sort Key: s1.org_key, s1.store_key, s1.sku_key, s1.date_key
  • Sort Method: external merge Disk: 11,152kB
33. 183,652.582 914,967.588 ↓ 120.9 410,684 1

Nested Loop (cost=3,369.24..4,204,933.75 rows=3,398 width=20) (actual time=79.112..914,967.588 rows=410,684 loops=1)

  • Join Filter: ((s1.org_key = o.org_key) AND (c.date_key <= COALESCE(stock_sales_2.date_key, (now())::date)))
  • Rows Removed by Join Filter: 4,344,417
34. 0.000 66,600.926 ↓ 102,531.9 221,571,360 1

Gather (cost=3,367.68..4,164,458.73 rows=2,161 width=26) (actual time=49.949..66,600.926 rows=221,571,360 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
35. 27,075.825 132,945.057 ↓ 82,063.5 73,857,120 3 / 3

Hash Join (cost=2,367.68..4,163,242.63 rows=900 width=26) (actual time=43.052..132,945.057 rows=73,857,120 loops=3)

  • Hash Cond: ((o.calendar_key = c.calendar_key) AND (sp.date_key = c.date_key))
36. 24,408.341 105,828.993 ↓ 1,230.9 73,857,120 3 / 3

Hash Join (cost=40.68..4,160,600.62 rows=60,002 width=28) (actual time=2.365..105,828.993 rows=73,857,120 loops=3)

  • Hash Cond: (sp.org_key = o.org_key)
37. 33,236.546 81,420.642 ↓ 1.5 73,857,120 3 / 3

Hash Join (cost=12.01..4,029,173.77 rows=49,802,146 width=22) (actual time=2.330..81,420.642 rows=73,857,120 loops=3)

  • Hash Cond: ((sp.org_key = st_1.org_key) AND (sp.store_key = st_1.store_key))
38. 48,184.020 48,184.020 ↑ 1.3 81,071,733 3 / 3

Parallel Seq Scan on predictions_skus_daily sp (cost=0.00..3,472,810.40 rows=104,379,840 width=16) (actual time=2.168..48,184.020 rows=81,071,733 loops=3)

39. 0.017 0.076 ↑ 1.0 72 3 / 3

Hash (cost=10.91..10.91 rows=73 width=6) (actual time=0.076..0.076 rows=72 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
40. 0.059 0.059 ↑ 1.0 72 3 / 3

Seq Scan on stores st_1 (cost=0.00..10.91 rows=73 width=6) (actual time=0.016..0.059 rows=72 loops=3)

  • Filter: ((NOT one_stock) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 80
41. 0.003 0.010 ↑ 830.0 1 3 / 3

Hash (cost=18.30..18.30 rows=830 width=6) (actual time=0.010..0.010 rows=1 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.007 0.007 ↑ 830.0 1 3 / 3

Seq Scan on organisations o (cost=0.00..18.30 rows=830 width=6) (actual time=0.007..0.007 rows=1 loops=3)

43. 24.242 40.239 ↑ 1.0 66,840 3 / 3

Hash (cost=1,324.40..1,324.40 rows=66,840 width=6) (actual time=40.239..40.239 rows=66,840 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 3,505kB
44. 15.997 15.997 ↑ 1.0 66,840 3 / 3

Seq Scan on calendar c (cost=0.00..1,324.40 rows=66,840 width=6) (actual time=0.008..15.997 rows=66,840 loops=3)

45. 0.000 664,714.080 ↓ 0.0 0 221,571,360

Nested Loop Left Join (cost=1.56..18.71 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=221,571,360)

46. 154,662.959 664,714.080 ↓ 0.0 0 221,571,360

Nested Loop (cost=1.00..13.23 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=221,571,360)

  • Join Filter: (c.date_key > s1.date_key)
  • Rows Removed by Join Filter: 0
47. 443,142.720 443,142.720 ↓ 0.0 0 221,571,360

Index Only Scan using stock_sales_stock_outs_idx on stock_sales s1 (cost=0.44..7.74 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=221,571,360)

  • Index Cond: ((org_key = sp.org_key) AND (store_key = sp.store_key) AND (sku_key = sp.sku_key) AND (date_key > (now() - '28 days'::interval)))
  • Heap Fetches: 9,558,343
48. 9,558.343 66,908.401 ↑ 1.0 1 9,558,343

Subquery Scan on sdc (cost=0.56..5.48 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=9,558,343)

  • Filter: (sdc.stock_end > 0)
  • Rows Removed by Filter: 0
49. 0.000 57,350.058 ↑ 1.0 1 9,558,343

Limit (cost=0.56..5.46 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=9,558,343)

50. 57,350.058 57,350.058 ↑ 2.0 1 9,558,343

Index Scan Backward using stock_sales_org_key_store_key_sku_key_date_key_idx on stock_sales stock_sales_1 (cost=0.56..10.36 rows=2 width=24) (actual time=0.006..0.006 rows=1 loops=9,558,343)

  • Index Cond: ((org_key = s1.org_key) AND (store_key = st_1.dc_key) AND (sku_key = s1.sku_key) AND (date_key <= c.date_key))
51. 0.000 14,265.303 ↓ 0.0 0 4,755,101

Limit (cost=0.56..5.46 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=4,755,101)

52. 14,265.303 14,265.303 ↓ 0.0 0 4,755,101

Index Only Scan using stock_sales_org_key_store_key_sku_key_date_key_idx on stock_sales stock_sales_2 (cost=0.56..10.36 rows=2 width=24) (actual time=0.003..0.003 rows=0 loops=4,755,101)

  • Index Cond: ((org_key = s1.org_key) AND (store_key = s1.store_key) AND (sku_key = s1.sku_key) AND (date_key > s1.date_key))
  • Heap Fetches: 2,052,365