explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MkrQ

Settings
# exclusive inclusive rows x rows loops node
1. 72.639 18,691.422 ↑ 1.0 1 1

Aggregate (cost=5,186,556.63..5,186,556.64 rows=1 width=8) (actual time=18,691.421..18,691.422 rows=1 loops=1)

2. 2,968.812 18,618.783 ↑ 2.1 2,224,750 1

Merge Left Join (cost=1,129,366.80..5,174,989.14 rows=4,626,994 width=0) (actual time=8,773.141..18,618.783 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
3. 1,381.249 9,273.563 ↓ 1.1 2,224,750 1

Sort (cost=1,129,366.23..1,134,525.81 rows=2,063,831 width=20) (actual time=8,773.088..9,273.563 rows=2,224,750 loops=1)

  • Sort Key: scs.network_sk, scs.broadcast_air_date_sk, scs.audience_sk
  • Sort Method: external merge Disk: 39112kB
4. 265.697 7,892.314 ↓ 1.1 2,224,750 1

Hash Join (cost=767,759.37..828,251.42 rows=2,063,831 width=20) (actual time=6,857.213..7,892.314 rows=2,224,750 loops=1)

  • Hash Cond: (scs.audience_sk = dma.audience_sk)
5. 769.641 7,626.566 ↓ 1.1 2,224,750 1

Hash Right Join (cost=767,752.56..799,866.93 rows=2,063,831 width=20) (actual time=6,857.152..7,626.566 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))
6. 0.010 0.010 ↓ 0.0 0 1

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

7. 634.347 6,856.915 ↓ 1.1 2,224,750 1

Hash (cost=705,192.36..705,192.36 rows=2,063,831 width=36) (actual time=6,856.915..6,856.915 rows=2,224,750 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 128 (originally 64) Memory Usage: 3585kB
8. 309.829 6,222.568 ↓ 1.1 2,224,750 1

Hash Join (cost=14.73..705,192.36 rows=2,063,831 width=36) (actual time=0.044..6,222.568 rows=2,224,750 loops=1)

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

Seq Scan on fact_spot scs (cost=0.00..676,799.96 rows=2,063,831 width=36) (actual time=0.026..5,912.728 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
10. 0.003 0.011 ↑ 10.5 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.008 0.008 ↑ 10.5 20 1

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

12. 0.019 0.051 ↑ 1.0 214 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
13. 0.032 0.032 ↑ 1.0 214 1

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

14. 6,376.408 6,376.408 ↑ 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.051..6,376.408 rows=64,863,126 loops=1)

  • Heap Fetches: 0
Planning time : 2.109 ms
Execution time : 18,697.221 ms