explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gAAe

Settings
# exclusive inclusive rows x rows loops node
1. 5,884.713 1,107,036.880 ↑ 888.0 9,672 1

GroupAggregate (cost=10,685,037.51..11,608,376.94 rows=8,589,204 width=198) (actual time=1,098,934.461..1,107,036.880 rows=9,672 loops=1)

  • Group Key: f.org_key, f.store_key, f.group_key
2. 12,595.255 1,101,152.167 ↓ 1.0 8,592,480 1

Sort (cost=10,685,037.51..10,706,510.52 rows=8,589,204 width=95) (actual time=1,098,914.497..1,101,152.167 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,489.401 1,088,556.912 ↓ 1.0 8,592,480 1

Subquery Scan on f (cost=8,031,147.23..8,374,715.39 rows=8,589,204 width=95) (actual time=1,078,166.423..1,088,556.912 rows=8,592,480 loops=1)

4. 6,764.376 1,087,067.511 ↓ 1.0 8,592,480 1

WindowAgg (cost=8,031,147.23..8,288,823.35 rows=8,589,204 width=107) (actual time=1,078,166.419..1,087,067.511 rows=8,592,480 loops=1)

5. 13,671.804 1,080,303.135 ↓ 1.0 8,592,480 1

Sort (cost=8,031,147.23..8,052,620.24 rows=8,589,204 width=87) (actual time=1,078,164.355..1,080,303.135 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,303.323 1,066,631.331 ↓ 1.0 8,592,480 1

Hash Left Join (cost=4,998,259.36..5,808,899.11 rows=8,589,204 width=87) (actual time=1,038,341.243..1,066,631.331 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. 6,040.275 36,943.216 ↓ 1.0 8,592,480 1

Hash Left Join (cost=792,582.21..1,535,581.98 rows=8,589,204 width=51) (actual time=11,955.213..36,943.216 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,685.208 20,341.744 ↓ 1.0 8,592,480 1

Hash Left Join (cost=104,878.20..644,783.99 rows=8,589,204 width=35) (actual time=1,393.948..20,341.744 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,797.149 11,569.259 ↓ 1.0 8,592,480 1

Hash Left Join (cost=35,178.46..382,115.26 rows=8,589,204 width=30) (actual time=306.453..11,569.259 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,631.126 5,625.344 ↓ 1.0 8,592,480 1

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

  • Hash Cond: (sk.org_key = st.org_key)
11. 343.387 3,994.021 ↓ 1.0 110,160 1

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

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

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

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

WindowAgg (cost=0.42..67,062.39 rows=928,984 width=24) (actual time=2.114..3,497.556 rows=928,984 loops=1)

14. 2,902.214 2,902.214 ↑ 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=1.712..2,902.214 rows=928,984 loops=1)

15. 27.067 65.777 ↓ 1.0 110,160 1

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

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,606kB
16. 38.710 38.710 ↓ 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..38.710 rows=110,160 loops=1)

17. 0.015 0.197 ↑ 1.0 78 1

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

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

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

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

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,782kB
20. 55.962 55.962 ↑ 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.009..55.962 rows=325,553 loops=1)

21. 504.804 1,087.277 ↑ 1.0 1,617,154 1

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

  • Buckets: 131,072 Batches: 32 Memory Usage: 3,394kB
22. 582.473 582.473 ↑ 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.007..582.473 rows=1,617,154 loops=1)

23. 49.931 10,561.197 ↑ 1.2 178,340 1

Hash (cost=682,738.93..682,738.93 rows=212,519 width=24) (actual time=10,561.197..10,561.197 rows=178,340 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,939kB
24. 28.565 10,511.266 ↑ 1.2 178,340 1

Subquery Scan on ss (cost=675,151.81..682,738.93 rows=212,519 width=24) (actual time=10,259.535..10,511.266 rows=178,340 loops=1)

25. 128.148 10,482.701 ↑ 1.2 178,340 1

GroupAggregate (cost=675,151.81..680,613.74 rows=212,519 width=24) (actual time=10,259.534..10,482.701 rows=178,340 loops=1)

  • Group Key: stock_sales.org_key, stock_sales.store_key, stock_sales.sku_key
26. 322.183 10,354.553 ↓ 1.7 378,297 1

Sort (cost=675,151.81..675,707.93 rows=222,449 width=12) (actual time=10,259.524..10,354.553 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. 10,032.370 10,032.370 ↓ 1.7 378,297 1

Seq Scan on stock_sales (cost=0.00..651,590.38 rows=222,449 width=12) (actual time=2.280..10,032.370 rows=378,297 loops=1)

  • Filter: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))
  • Rows Removed by Filter: 21,446,949
28. 30.085 1,026,384.792 ↓ 111.0 38,078 1

Hash (cost=4,205,671.15..4,205,671.15 rows=343 width=48) (actual time=1,026,384.792..1,026,384.792 rows=38,078 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,595kB
29. 6.418 1,026,354.707 ↓ 111.0 38,078 1

Subquery Scan on nv (cost=4,205,457.81..4,205,671.15 rows=343 width=48) (actual time=1,025,997.622..1,026,354.707 rows=38,078 loops=1)

30. 34.501 1,026,348.289 ↓ 111.0 38,078 1

GroupAggregate (cost=4,205,457.81..4,205,667.72 rows=343 width=48) (actual time=1,025,997.618..1,026,348.289 rows=38,078 loops=1)

  • Group Key: s1.org_key, s1.store_key, s1.sku_key
31. 164.823 1,026,313.788 ↓ 12.5 42,815 1

GroupAggregate (cost=4,205,457.81..4,205,586.33 rows=3,427 width=52) (actual time=1,025,996.426..1,026,313.788 rows=42,815 loops=1)

  • Group Key: s1.org_key, s1.store_key, s1.sku_key, s1.date_key
32. 788.539 1,026,148.965 ↓ 119.8 410,684 1

Sort (cost=4,205,457.81..4,205,466.38 rows=3,427 width=20) (actual time=1,025,994.787..1,026,148.965 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. 70,478.015 1,025,360.426 ↓ 119.8 410,684 1

Nested Loop (cost=3,369.38..4,205,256.60 rows=3,427 width=20) (actual time=45.994..1,025,360.426 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 68,596.971 ↓ 102,531.9 221,571,360 1

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

  • Workers Planned: 2
  • Workers Launched: 2
35. 27,116.089 135,261.824 ↓ 82,063.5 73,857,120 3 / 3

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

  • Hash Cond: ((o.calendar_key = c.calendar_key) AND (sp.date_key = c.date_key))
36. 23,693.034 108,090.121 ↓ 1,230.9 73,857,120 3 / 3

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

  • Hash Cond: (sp.org_key = o.org_key)
37. 33,323.206 84,397.077 ↓ 1.5 73,857,120 3 / 3

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

  • Hash Cond: ((sp.org_key = st_1.org_key) AND (sp.store_key = st_1.store_key))
38. 51,073.797 51,073.797 ↑ 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=0.005..51,073.797 rows=81,071,733 loops=3)

39. 0.019 0.074 ↑ 1.0 72 3 / 3

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

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

Seq Scan on stores st_1 (cost=0.00..10.91 rows=73 width=6) (actual time=0.013..0.055 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.009..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. 35.638 55.614 ↑ 1.0 66,840 3 / 3

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 3,505kB
44. 19.976 19.976 ↑ 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.009..19.976 rows=66,840 loops=3)

45. 0.000 886,285.440 ↓ 0.0 0 221,571,360

Nested Loop Left Join (cost=1.69..18.86 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=221,571,360)

46. 154,662.959 886,285.440 ↓ 0.0 0 221,571,360

Nested Loop (cost=1.13..13.38 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=221,571,360)

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

Index Scan using stock_sales_org_key_store_key_sku_key_date_key_idx on stock_sales s1 (cost=0.57..7.89 rows=1 width=12) (actual time=0.003..0.003 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)))
  • Filter: (stock_end <= 0)
  • Rows Removed by Filter: 0
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