explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dMUN

Settings
# exclusive inclusive rows x rows loops node
1. 70.035 19,723.002 ↑ 1.0 1 1

Aggregate (cost=5,250,214.34..5,250,214.35 rows=1 width=8) (actual time=19,723.002..19,723.002 rows=1 loops=1)

2. 242.929 19,652.967 ↑ 2.1 2,224,750 1

Hash Join (cost=1,129,403.35..5,238,646.86 rows=4,626,994 width=0) (actual time=9,558.365..19,652.967 rows=2,224,750 loops=1)

  • Hash Cond: (scs.spot_status_sk = ss.spot_status_sk)
3. 2,960.385 19,410.022 ↑ 2.1 2,224,750 1

Merge Left Join (cost=1,129,366.80..5,174,989.14 rows=4,626,994 width=4) (actual time=9,558.336..19,410.022 rows=2,224,750 loops=1)

  • Merge Cond: ((scs.network_sk = gd.network_sk) AND (scs.broadcast_air_date_sk = gd.broadcast_date_sk) AND (scs.audience_sk = gd.audience_sk))
  • Join Filter: ((scs.gregorian_air_date_time >= gd.program_start_date_time) AND (scs.gregorian_air_date_time <= gd.program_end_date_time))
  • Rows Removed by Join Filter: 62483
4. 1,479.926 10,067.323 ↓ 1.1 2,224,750 1

Sort (cost=1,129,366.23..1,134,525.81 rows=2,063,831 width=24) (actual time=9,558.282..10,067.323 rows=2,224,750 loops=1)

  • Sort Key: scs.network_sk, scs.broadcast_air_date_sk, scs.audience_sk
  • Sort Method: external merge Disk: 47856kB
5. 270.897 8,587.397 ↓ 1.1 2,224,750 1

Hash Join (cost=767,759.37..828,251.42 rows=2,063,831 width=24) (actual time=7,157.760..8,587.397 rows=2,224,750 loops=1)

  • Hash Cond: (scs.audience_sk = dma.audience_sk)
6. 1,158.951 8,316.424 ↓ 1.1 2,224,750 1

Hash Right Join (cost=767,752.56..799,866.93 rows=2,063,831 width=24) (actual time=7,157.676..8,316.424 rows=2,224,750 loops=1)

  • Hash Cond: ((ed.division_sk = scs.division_sk) AND (ed.network_sk = scs.network_sk) AND (ed.selling_title_sk = scs.selling_title_sk) AND (ed.rate_card_type_sk = rct.rate_card_type_sk) AND (ed.audience_sk = scs.audience_sk))
  • Join Filter: ((COALESCE(NULLIF(scs.broadcast_air_date_sk, 0), scs.broadcast_week_date_sk) >= ed.broadcast_start_date_sk) AND (COALESCE(NULLIF(scs.broadcast_air_date_sk, 0), scs.broadcast_week_date_sk) <= ed.broadcast_end_date_sk))
7. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on estimate_delivery ed (cost=0.00..12.60 rows=260 width=28) (actual time=0.011..0.011 rows=0 loops=1)

8. 704.248 7,157.462 ↓ 1.1 2,224,750 1

Hash (cost=705,192.36..705,192.36 rows=2,063,831 width=40) (actual time=7,157.462..7,157.462 rows=2,224,750 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 128 (originally 64) Memory Usage: 3585kB
9. 487.613 6,453.214 ↓ 1.1 2,224,750 1

Hash Join (cost=14.73..705,192.36 rows=2,063,831 width=40) (actual time=0.128..6,453.214 rows=2,224,750 loops=1)

  • Hash Cond: (scs.rate_card_type_sk = rct.rate_card_type_sk)
10. 5,965.574 5,965.574 ↓ 1.1 2,224,750 1

Seq Scan on fact_spot scs (cost=0.00..676,799.96 rows=2,063,831 width=40) (actual time=0.038..5,965.574 rows=2,224,750 loops=1)

  • Filter: ((audience_sk <> 0) AND (broadcast_week_date_sk >= 20150101) AND (is_guaranteed_audience = 1) AND (spot_source_sk = 1))
  • Rows Removed by Filter: 6736254
11. 0.009 0.027 ↑ 10.5 20 1

Hash (cost=12.10..12.10 rows=210 width=4) (actual time=0.027..0.027 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.018 0.018 ↑ 10.5 20 1

Seq Scan on dim_rate_card_type rct (cost=0.00..12.10 rows=210 width=4) (actual time=0.015..0.018 rows=20 loops=1)

13. 0.048 0.076 ↑ 1.0 214 1

Hash (cost=4.14..4.14 rows=214 width=4) (actual time=0.076..0.076 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
14. 0.028 0.028 ↑ 1.0 214 1

Seq Scan on dim_audience dma (cost=0.00..4.14 rows=214 width=4) (actual time=0.014..0.028 rows=214 loops=1)

15. 6,382.314 6,382.314 ↑ 1.1 64,863,126 1

Index Only Scan using idx_del_keys_reveal_genesis_delivery on reveal_genesis_delivery gd (cost=0.57..2,855,215.09 rows=72,683,368 width=28) (actual time=0.048..6,382.314 rows=64,863,126 loops=1)

  • Heap Fetches: 0
16. 0.004 0.016 ↑ 84.3 14 1

Hash (cost=21.80..21.80 rows=1,180 width=4) (actual time=0.016..0.016 rows=14 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
17. 0.012 0.012 ↑ 84.3 14 1

Seq Scan on dim_spot_status ss (cost=0.00..21.80 rows=1,180 width=4) (actual time=0.011..0.012 rows=14 loops=1)

Planning time : 2.863 ms
Execution time : 19,729.128 ms