explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 32IG

Settings
# exclusive inclusive rows x rows loops node
1. 431,872.281 610,597.582 ↓ 1.6 13,966,318 1

Sort (cost=3,347,229.62..3,369,598.97 rows=8,947,740 width=137) (actual time=477,510.707..610,597.582 rows=13,966,318 loops=1)

  • Sort Key: f.market_id, f.channel_id, f.sku_id, f.supplier_id, f.metric_id, f.share, d.id
  • Sort Method: external merge Disk: 2032848kB
2. 5,171.471 178,725.301 ↓ 1.6 13,966,318 1

Hash Join (cost=469.09..1,561,728.81 rows=8,947,740 width=137) (actual time=62.953..178,725.301 rows=13,966,318 loops=1)

  • Hash Cond: (f.market_id = mr.id)
3. 5,075.407 173,548.185 ↓ 1.6 13,966,318 1

Hash Join (cost=427.36..1,438,655.66 rows=8,947,740 width=124) (actual time=57.294..173,548.185 rows=13,966,318 loops=1)

  • Hash Cond: (f.date_id = dp.id)
4. 12,957.507 168,468.812 ↓ 1.1 13,966,318 1

Hash Join (cost=357.50..1,302,377.92 rows=12,526,835 width=115) (actual time=53.309..168,468.812 rows=13,966,318 loops=1)

  • Hash Cond: (f.metric_id = t.id)
5. 155,460.170 155,460.170 ↑ 1.0 49,560,306 1

Seq Scan on fct_sku_fr_mass f (cost=0.00..990,891.96 rows=49,562,696 width=47) (actual time=2.127..155,460.170 rows=49,560,306 loops=1)

6. 0.015 51.135 ↓ 2.2 50 1

Hash (cost=357.22..357.22 rows=23 width=72) (actual time=51.135..51.135 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
7. 0.009 51.120 ↓ 2.2 50 1

Subquery Scan on t (cost=356.87..357.22 rows=23 width=72) (actual time=51.098..51.120 rows=50 loops=1)

8. 0.011 51.111 ↓ 2.2 50 1

Unique (cost=356.87..356.99 rows=23 width=76) (actual time=51.096..51.111 rows=50 loops=1)

9. 0.107 51.100 ↓ 2.7 61 1

Sort (cost=356.87..356.93 rows=23 width=76) (actual time=51.096..51.100 rows=61 loops=1)

  • Sort Key: m1.metric, (length((m2.metric)::text)), m2.id
  • Sort Method: quicksort Memory: 33kB
10. 1.586 50.993 ↓ 2.7 61 1

Hash Join (cost=135.78..356.35 rows=23 width=76) (actual time=48.996..50.993 rows=61 loops=1)

  • Hash Cond: ((m1.form)::text = (m2.form)::text)
  • Join Filter: ((m2.metric)::text ~~ concat('%', m1.metric, '%'))
  • Rows Removed by Join Filter: 1433
11. 2.268 2.268 ↑ 1.9 50 1

Seq Scan on dim_metric m1 (cost=0.00..134.68 rows=97 width=42) (actual time=1.797..2.268 rows=50 loops=1)

  • Filter: ((NOT change) AND ((metric)::text ~ '%|Share|Index|Distribution'::text))
  • Rows Removed by Filter: 244
12. 0.097 47.139 ↑ 1.0 147 1

Hash (cost=133.94..133.94 rows=147 width=42) (actual time=47.139..47.139 rows=147 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
13. 47.042 47.042 ↑ 1.0 147 1

Seq Scan on dim_metric m2 (cost=0.00..133.94 rows=147 width=42) (actual time=0.011..47.042 rows=147 loops=1)

  • Filter: change
  • Rows Removed by Filter: 147
14. 0.048 3.966 ↑ 1.4 115 1

Hash (cost=67.90..67.90 rows=156 width=13) (actual time=3.966..3.966 rows=115 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.119 3.918 ↑ 1.4 115 1

Nested Loop (cost=1.46..67.90 rows=156 width=13) (actual time=1.492..3.918 rows=115 loops=1)

16. 0.090 1.384 ↑ 1.4 115 1

Hash Join (cost=1.18..9.75 rows=156 width=13) (actual time=0.572..1.384 rows=115 loops=1)

  • Hash Cond: ((dp.period_tag)::text = (p.tag)::text)
17. 1.275 1.275 ↑ 1.0 219 1

Seq Scan on dim_date_period dp (cost=0.00..6.19 rows=219 width=22) (actual time=0.534..1.275 rows=219 loops=1)

18. 0.011 0.019 ↑ 1.0 5 1

Hash (cost=1.11..1.11 rows=5 width=19) (actual time=0.019..0.019 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on dim_period p (cost=0.00..1.11 rows=5 width=19) (actual time=0.007..0.008 rows=5 loops=1)

  • Filter: ((name)::text = ANY ('{MAT,YTD,L6M,L3M,Month}'::text[]))
  • Rows Removed by Filter: 2
20. 2.415 2.415 ↑ 1.0 1 115

Index Only Scan using pk_dim_date on dim_date d (cost=0.28..0.36 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=115)

  • Index Cond: (id = dp.date_tag)
  • Heap Fetches: 0
21. 0.110 5.645 ↑ 1.0 457 1

Hash (cost=36.02..36.02 rows=457 width=17) (actual time=5.645..5.645 rows=457 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
22. 0.162 5.535 ↑ 1.0 457 1

Hash Join (cost=7.88..36.02 rows=457 width=17) (actual time=2.411..5.535 rows=457 loops=1)

  • Hash Cond: ((mr.report_tag)::text = (r.tag)::text)
23. 0.219 4.811 ↑ 1.0 457 1

Hash Join (cost=3.15..25.00 rows=457 width=27) (actual time=1.821..4.811 rows=457 loops=1)

  • Hash Cond: ((mr.market_tag)::text = (m.tag)::text)
24. 4.183 4.183 ↑ 1.0 457 1

Seq Scan on dim_market_report mr (cost=0.00..15.57 rows=457 width=32) (actual time=1.389..4.183 rows=457 loops=1)

25. 0.021 0.409 ↑ 1.0 51 1

Hash (cost=2.51..2.51 rows=51 width=23) (actual time=0.409..0.409 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.388 0.388 ↑ 1.0 51 1

Seq Scan on dim_market m (cost=0.00..2.51 rows=51 width=23) (actual time=0.006..0.388 rows=51 loops=1)

27. 0.023 0.562 ↑ 1.0 77 1

Hash (cost=3.77..3.77 rows=77 width=18) (actual time=0.562..0.562 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
28. 0.539 0.539 ↑ 1.0 77 1

Seq Scan on dim_report r (cost=0.00..3.77 rows=77 width=18) (actual time=0.006..0.539 rows=77 loops=1)