explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y5L6

Settings
# exclusive inclusive rows x rows loops node
1. 3,962.693 8,447.896 ↑ 1.3 251,036 1

GroupAggregate (cost=168,509.95..271,212.29 rows=336,729 width=264) (actual time=4,194.390..8,447.896 rows=251,036 loops=1)

  • Group Key: wd.deal_id, wd.proposal_id, ((COALESCE(to_char(dt.broadcast_week_start_date, 'YYYYMMDD'::text), to_char((hsp.start_date)::timestamp with time zone, 'YYYYMMDD'::text)))::integer), dn.network_sk, dp.property_sk, da.audience_sk
2. 1,239.890 4,485.203 ↓ 3.0 1,009,898 1

Sort (cost=168,509.95..169,351.77 rows=336,729 width=38) (actual time=4,194.058..4,485.203 rows=1,009,898 loops=1)

  • Sort Key: wd.deal_id, wd.proposal_id, ((COALESCE(to_char(dt.broadcast_week_start_date, 'YYYYMMDD'::text), to_char((hsp.start_date)::timestamp with time zone, 'YYYYMMDD'::text)))::integer), dn.network_sk, dp.property_sk, da.audience_sk
  • Sort Method: external merge Disk: 49480kB
3. 849.075 3,245.313 ↓ 3.0 1,009,898 1

Hash Left Join (cost=110,841.66..128,387.66 rows=336,729 width=38) (actual time=1,306.203..3,245.313 rows=1,009,898 loops=1)

  • Hash Cond: (wd.selling_period_id = hsp.selling_period_id)
4. 200.827 2,396.197 ↓ 3.0 1,009,898 1

Hash Left Join (cost=110,835.64..119,542.50 rows=336,729 width=46) (actual time=1,306.136..2,396.197 rows=1,009,898 loops=1)

  • Hash Cond: (wd.property_id = dp.property_id)
5. 188.691 2,195.356 ↓ 3.0 1,009,898 1

Hash Left Join (cost=110,816.19..118,630.22 rows=336,729 width=46) (actual time=1,306.114..2,195.356 rows=1,009,898 loops=1)

  • Hash Cond: (wd.network_id = dn.network_id)
6. 196.862 2,006.572 ↓ 3.0 1,009,898 1

Hash Left Join (cost=110,803.61..117,722.61 rows=336,729 width=46) (actual time=1,306.015..2,006.572 rows=1,009,898 loops=1)

  • Hash Cond: (wd.primary_demo_id = da.audience_id)
7. 225.226 1,809.644 ↓ 3.0 1,009,898 1

Merge Right Join (cost=110,796.79..116,814.38 rows=336,729 width=46) (actual time=1,305.932..1,809.644 rows=1,009,898 loops=1)

  • Merge Cond: (dt.calendar_date = wd.broadcast_week)
8. 3.045 3.045 ↑ 1.9 11,308 1

Index Scan using idx_dd_calendar_date on dim_dates dt (cost=0.29..1,382.34 rows=21,916 width=16) (actual time=0.010..3.045 rows=11,308 loops=1)

9. 101.589 1,581.373 ↓ 3.0 1,009,898 1

Materialize (cost=110,185.08..111,868.73 rows=336,729 width=42) (actual time=1,303.237..1,581.373 rows=1,009,898 loops=1)

10. 637.536 1,479.784 ↓ 3.0 1,009,898 1

Sort (cost=110,185.08..111,026.90 rows=336,729 width=42) (actual time=1,303.234..1,479.784 rows=1,009,898 loops=1)

  • Sort Key: wd.broadcast_week
  • Sort Method: external merge Disk: 53448kB
11. 842.248 842.248 ↓ 3.0 1,009,898 1

Seq Scan on vw_deal_and_proposals wd (cost=0.00..68,911.29 rows=336,729 width=42) (actual time=0.122..842.248 rows=1,009,898 loops=1)

  • Filter: ((end_date)::date >= '2016-01-01'::date)
12. 0.035 0.066 ↑ 1.0 214 1

Hash (cost=4.14..4.14 rows=214 width=8) (actual time=0.066..0.066 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
13. 0.031 0.031 ↑ 1.0 214 1

Seq Scan on dim_audience da (cost=0.00..4.14 rows=214 width=8) (actual time=0.008..0.031 rows=214 loops=1)

14. 0.046 0.093 ↑ 1.0 337 1

Hash (cost=8.37..8.37 rows=337 width=8) (actual time=0.093..0.093 rows=337 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
15. 0.047 0.047 ↑ 1.0 337 1

Seq Scan on dim_network dn (cost=0.00..8.37 rows=337 width=8) (actual time=0.005..0.047 rows=337 loops=1)

16. 0.007 0.014 ↑ 12.0 35 1

Hash (cost=14.20..14.20 rows=420 width=8) (actual time=0.014..0.014 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.007 0.007 ↑ 12.0 35 1

Seq Scan on dim_property dp (cost=0.00..14.20 rows=420 width=8) (actual time=0.003..0.007 rows=35 loops=1)

18. 0.021 0.041 ↑ 1.0 134 1

Hash (cost=4.34..4.34 rows=134 width=8) (actual time=0.041..0.041 rows=134 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.020 0.020 ↑ 1.0 134 1

Seq Scan on selling_period hsp (cost=0.00..4.34 rows=134 width=8) (actual time=0.002..0.020 rows=134 loops=1)

Planning time : 1.043 ms
Execution time : 8,705.913 ms