explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f7xV

Settings
# exclusive inclusive rows x rows loops node
1. 21,172.679 676,429.001 ↓ 240.0 73,928,777 1

Hash Join (cost=1,816.26..494,127.43 rows=308,053 width=116) (actual time=174,999.317..676,429.001 rows=73,928,777 loops=1)

  • Hash Cond: ((a.source_tag)::text = (f.tag)::text)
2. 20,007.321 655,256.309 ↓ 240.0 73,928,777 1

Hash Join (cost=1,784.66..489,860.10 rows=308,053 width=123) (actual time=174,999.295..655,256.309 rows=73,928,777 loops=1)

  • Hash Cond: (m.date_tag = j.id)
3. 21,318.039 635,248.379 ↓ 240.0 73,928,777 1

Hash Join (cost=1,703.37..485,543.07 rows=308,053 width=123) (actual time=174,998.673..635,248.379 rows=73,928,777 loops=1)

  • Hash Cond: (i.supplier_id = a.id)
4. 22,955.160 613,930.312 ↓ 240.0 73,928,777 1

Hash Join (cost=1,667.88..481,271.85 rows=308,053 width=102) (actual time=174,998.637..613,930.312 rows=73,928,777 loops=1)

  • Hash Cond: ((m.period_tag)::text = (b.tag)::text)
5. 17,579.474 590,975.143 ↓ 76.0 81,942,212 1

Merge Join (cost=1,666.76..474,147.01 rows=1,078,187 width=111) (actual time=174,998.621..590,975.143 rows=81,942,212 loops=1)

  • Merge Cond: ((e.tag)::text = (g.market_tag)::text)
6. 0.007 0.007 ↑ 2.9 17 1

Index Scan using uniq_dim_market_tag on dim_market e (cost=0.14..5.18 rows=49 width=17) (actual time=0.003..0.007 rows=17 loops=1)

7. 36,291.584 573,395.662 ↓ 76.0 81,942,212 1

Materialize (cost=1,666.62..47,738,274.10 rows=1,078,187 width=122) (actual time=174,998.597..573,395.662 rows=81,942,212 loops=1)

8. 4,117.979 537,104.078 ↓ 76.0 81,942,212 1

Nested Loop (cost=1,666.62..47,735,578.63 rows=1,078,187 width=122) (actual time=174,998.594..537,104.078 rows=81,942,212 loops=1)

9. 22,522.687 451,043.887 ↓ 76.0 81,942,212 1

Nested Loop (cost=1,666.48..46,360,890.18 rows=1,078,187 width=108) (actual time=174,998.589..451,043.887 rows=81,942,212 loops=1)

10. 10,632.266 346,578.988 ↓ 76.0 81,942,212 1

Nested Loop (cost=1,666.20..44,614,227.20 rows=1,078,187 width=80) (actual time=174,998.581..346,578.988 rows=81,942,212 loops=1)

11. 14,819.392 254,004.510 ↓ 76.0 81,942,212 1

Nested Loop (cost=1,666.06..43,044,566.59 rows=1,078,187 width=66) (actual time=174,998.530..254,004.510 rows=81,942,212 loops=1)

  • Join Filter: (i.market_id = g.id)
12. 0.006 0.038 ↑ 18.0 1 1

Nested Loop (cost=0.15..820.57 rows=18 width=22) (actual time=0.033..0.038 rows=1 loops=1)

  • Join Filter: ((g.report_tag)::text = (d.tag)::text)
  • Rows Removed by Join Filter: 24
13. 0.007 0.007 ↑ 54.4 25 1

Index Scan using uniq_dim_market_report on dim_market_report g (cost=0.15..796.22 rows=1,360 width=32) (actual time=0.004..0.007 rows=25 loops=1)

14. 0.010 0.025 ↑ 1.0 1 25

Materialize (cost=0.00..3.96 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=25)

15. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on dim_report d (cost=0.00..3.95 rows=1 width=18) (actual time=0.014..0.015 rows=1 loops=1)

  • Filter: ((name)::text = 'RSM'::text)
  • Rows Removed by Filter: 75
16. 24,131.901 239,185.080 ↓ 1.0 81,942,212 1

Materialize (cost=1,665.91..7,520,516.38 rows=81,942,208 width=52) (actual time=174,998.495..239,185.080 rows=81,942,212 loops=1)

17. 26,859.294 215,053.179 ↓ 1.0 81,942,212 1

Hash Join (cost=1,665.91..6,310,588.34 rows=81,942,208 width=52) (actual time=174,998.492..215,053.179 rows=81,942,212 loops=1)

  • Hash Cond: ((i.sku_id)::text = (k.utag)::text)
18. 188,183.744 188,183.744 ↓ 1.0 81,942,212 1

Seq Scan on fct_sku i (cost=0.00..5,182,217.08 rows=81,942,208 width=80) (actual time=174,988.121..188,183.744 rows=81,942,212 loops=1)

19. 5.388 10.141 ↑ 1.0 33,018 1

Hash (cost=1,253.18..1,253.18 rows=33,018 width=18) (actual time=10.141..10.141 rows=33,018 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2148kB
20. 4.753 4.753 ↑ 1.0 33,018 1

Seq Scan on dim_sku k (cost=0.00..1,253.18 rows=33,018 width=18) (actual time=0.012..4.753 rows=33,018 loops=1)

21. 81,942.212 81,942.212 ↑ 1.0 1 81,942,212

Index Scan using pk_dim_channel on dim_channel c (cost=0.14..1.45 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=81,942,212)

  • Index Cond: (id = i.channel_id)
22. 81,942.212 81,942.212 ↑ 1.0 1 81,942,212

Index Scan using pk_dim_metric on dim_metric l (cost=0.27..1.61 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=81,942,212)

  • Index Cond: (id = i.metric_id)
23. 81,942.212 81,942.212 ↑ 1.0 1 81,942,212

Index Scan using pk_dim_date_period on dim_date_period m (cost=0.15..1.27 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=81,942,212)

  • Index Cond: (id = i.date_id)
24. 0.004 0.009 ↑ 1.0 2 1

Hash (cost=1.09..1.09 rows=2 width=19) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on dim_period b (cost=0.00..1.09 rows=2 width=19) (actual time=0.004..0.005 rows=2 loops=1)

  • Filter: ((name)::text = ANY ('{Weekly,Monthly}'::text[]))
  • Rows Removed by Filter: 5
26. 0.002 0.028 ↑ 680.0 2 1

Hash (cost=18.49..18.49 rows=1,360 width=25) (actual time=0.028..0.028 rows=2 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
27. 0.010 0.026 ↑ 680.0 2 1

Nested Loop (cost=0.15..18.49 rows=1,360 width=25) (actual time=0.011..0.026 rows=2 loops=1)

28. 0.005 0.005 ↑ 1.0 11 1

Seq Scan on dim_supplier h (cost=0.00..1.11 rows=11 width=21) (actual time=0.004..0.005 rows=11 loops=1)

29. 0.011 0.011 ↓ 0.0 0 11

Index Scan using uniq_dim_supplier_source on dim_supplier_source a (cost=0.15..1.57 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=11)

  • Index Cond: ((supplier_tag)::text = (h.tag)::text)
30. 0.332 0.609 ↑ 1.0 2,191 1

Hash (cost=53.91..53.91 rows=2,191 width=8) (actual time=0.609..0.609 rows=2,191 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 118kB
31. 0.277 0.277 ↑ 1.0 2,191 1

Seq Scan on dim_date j (cost=0.00..53.91 rows=2,191 width=8) (actual time=0.005..0.277 rows=2,191 loops=1)

32. 0.005 0.013 ↑ 480.0 2 1

Hash (cost=19.60..19.60 rows=960 width=21) (actual time=0.013..0.013 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.008 0.008 ↑ 480.0 2 1

Seq Scan on dim_source f (cost=0.00..19.60 rows=960 width=21) (actual time=0.008..0.008 rows=2 loops=1)