explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yO7I

Settings
# exclusive inclusive rows x rows loops node
1. 101,804.146 135,544.094 ↓ 1.6 13,966,318 1

Sort (cost=4,375,781.84..4,397,177.67 rows=8,558,330 width=137) (actual time=117,765.204..135,544.094 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: 2032752kB
2. 2,407.493 33,739.948 ↓ 1.6 13,966,318 1

Hash Join (cost=2,721.38..1,547,450.80 rows=8,558,330 width=137) (actual time=10.872..33,739.948 rows=13,966,318 loops=1)

  • Hash Cond: (f.market_id = mr.id)
3. 2,418.624 31,332.192 ↓ 1.6 13,966,318 1

Hash Join (cost=2,691.97..1,429,744.35 rows=8,558,330 width=124) (actual time=10.602..31,332.192 rows=13,966,318 loops=1)

  • Hash Cond: (f.date_id = dp.id)
4. 5,479.866 28,905.919 ↓ 1.2 13,966,318 1

Hash Join (cost=108.79..1,296,647.38 rows=11,981,661 width=115) (actual time=2.809..28,905.919 rows=13,966,318 loops=1)

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

Seq Scan on fct_sku_fr_mass f (cost=0.00..990,870.08 rows=49,560,508 width=47) (actual time=0.424..23,423.678 rows=49,560,306 loops=1)

6. 0.008 2.375 ↓ 2.3 50 1

Hash (cost=108.51..108.51 rows=22 width=72) (actual time=2.375..2.375 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
7. 0.007 2.367 ↓ 2.3 50 1

Subquery Scan on t (cost=108.18..108.51 rows=22 width=72) (actual time=2.351..2.367 rows=50 loops=1)

8. 0.006 2.360 ↓ 2.3 50 1

Unique (cost=108.18..108.29 rows=22 width=76) (actual time=2.350..2.360 rows=50 loops=1)

9. 0.070 2.354 ↓ 2.8 61 1

Sort (cost=108.18..108.24 rows=22 width=76) (actual time=2.350..2.354 rows=61 loops=1)

  • Sort Key: m1.metric, (length((m2.metric)::text)), m2.id
  • Sort Method: quicksort Memory: 33kB
10. 1.407 2.284 ↓ 2.8 61 1

Hash Join (cost=12.78..107.69 rows=22 width=76) (actual time=0.951..2.284 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. 0.516 0.516 ↑ 1.9 50 1

Seq Scan on dim_metric m1 (cost=0.00..11.68 rows=94 width=42) (actual time=0.222..0.516 rows=50 loops=1)

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

Hash (cost=10.94..10.94 rows=147 width=42) (actual time=0.361..0.361 rows=147 loops=1)

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

Seq Scan on dim_metric m2 (cost=0.00..10.94 rows=147 width=42) (actual time=0.015..0.332 rows=147 loops=1)

  • Filter: change
  • Rows Removed by Filter: 147
14. 0.215 7.649 ↑ 26.0 1,285 1

Hash (cost=2,166.13..2,166.13 rows=33,364 width=13) (actual time=7.649..7.649 rows=1,285 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 569kB
15. 0.210 7.434 ↑ 26.0 1,285 1

Hash Join (cost=82.47..2,166.13 rows=33,364 width=13) (actual time=0.563..7.434 rows=1,285 loops=1)

  • Hash Cond: (dp.date_tag = d.id)
16. 2.785 6.699 ↑ 26.0 1,285 1

Hash Join (cost=1.18..1,626.08 rows=33,364 width=13) (actual time=0.028..6.699 rows=1,285 loops=1)

  • Hash Cond: ((dp.period_tag)::text = (p.tag)::text)
17. 3.908 3.908 ↑ 1.3 36,218 1

Seq Scan on dim_date_period dp (cost=0.00..1,116.10 rows=46,710 width=22) (actual time=0.009..3.908 rows=36,218 loops=1)

18. 0.002 0.006 ↑ 1.0 5 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 110kB
21. 0.309 0.309 ↑ 1.0 2,191 1

Seq Scan on dim_date d (cost=0.00..53.91 rows=2,191 width=4) (actual time=0.007..0.309 rows=2,191 loops=1)

22. 0.040 0.263 ↑ 1.0 296 1

Hash (cost=25.71..25.71 rows=296 width=17) (actual time=0.263..0.263 rows=296 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
23. 0.059 0.223 ↑ 1.0 296 1

Hash Join (cost=9.61..25.71 rows=296 width=17) (actual time=0.080..0.223 rows=296 loops=1)

  • Hash Cond: ((mr.report_tag)::text = (r.tag)::text)
24. 0.072 0.136 ↑ 1.0 296 1

Hash Join (cost=3.15..15.18 rows=296 width=27) (actual time=0.045..0.136 rows=296 loops=1)

  • Hash Cond: ((mr.market_tag)::text = (m.tag)::text)
25. 0.035 0.035 ↑ 1.0 296 1

Seq Scan on dim_market_report mr (cost=0.00..7.96 rows=296 width=32) (actual time=0.010..0.035 rows=296 loops=1)

26. 0.011 0.029 ↑ 1.0 51 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.018 0.018 ↑ 1.0 51 1

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

28. 0.008 0.028 ↑ 2.0 77 1

Hash (cost=4.54..4.54 rows=154 width=18) (actual time=0.028..0.028 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
29. 0.020 0.020 ↑ 2.0 77 1

Seq Scan on dim_report r (cost=0.00..4.54 rows=154 width=18) (actual time=0.002..0.020 rows=77 loops=1)