explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OahT : rotations with latest quarterly rates

Settings
# exclusive inclusive rows x rows loops node
1. 6.843 2,914.343 ↓ 1.5 658 1

Sort (cost=7,831.88..7,832.97 rows=439 width=325) (actual time=2,914.262..2,914.343 rows=658 loops=1)

  • Sort Key: nr.station_code, nr.start_time
  • Sort Method: quicksort Memory: 194kB
  • Y",FOOD,FUSN,FXX,FM,FUSE,FX,FXM,FYI,HGTV,HIST,IFC,JUST,LGHN,MTV2,MTV3,HISE,LAFF,LIFE,LOGO,MTV,NGCW,OWN,PARA,MTV-Locals,MTV2-Locals,NGC,OVAT,OXYG,REVOLT,FX-Locals,IFC-Locals,AMC-Locals,BRAV-Locals,MSNB-Locals,COOK-Locals,LX,LIFE-Locals,E!-Locals,HIST-Locals}'::text[]))
  • Y",FOOD,FUSN,FXX,FM,FUSE,FX,FXM,FYI,HGTV,HIST,IFC,JUST,LGHN,MTV2,MTV3,HISE,LAFF,LIFE,LOGO,MTV,NGCW,OWN,PARA,MTV-Locals,MTV2-Locals,NGC,OVAT,OXYG,REVOLT,FX-Locals,IFC-Locals,AMC-Locals,BRAV-Locals,MSNB-Locals,COOK-Locals,LX,LIFE-Locals,E!-Locals,HIST-Locals}'::text[]))
  • Execution time: 2,920.926 m
2.          

CTE filtered_rates

3. 39.084 187.500 ↓ 11.5 37,343 1

Hash Join (cost=377.67..3,783.72 rows=3,240 width=92) (actual time=2.188..187.500 rows=37,343 loops=1)

  • Hash Cond: (r_1.network_rotation_id = nr_1.id)
4. 146.307 146.307 ↓ 3.0 121,086 1

Seq Scan on rates r_1 (cost=0.00..3,222.29 rows=40,362 width=92) (actual time=0.060..146.307 rows=121,086 loops=1)

  • Filter: (lower(broadcast_week) <= '2020-07-27'::date)
5. 0.248 2.109 ↓ 1.4 954 1

Hash (cost=369.04..369.04 rows=691 width=4) (actual time=2.109..2.109 rows=954 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
6. 0.447 1.861 ↓ 1.4 954 1

Bitmap Heap Scan on network_rotations nr_1 (cost=161.84..369.04 rows=691 width=4) (actual time=1.444..1.861 rows=954 loops=1)

  • Recheck Cond: (station_code = ANY ('{IMPRACTICALJOKERS,LEVERAGE,PAWNSTARS,"ESPN-Locals: 30 for 30",VH-1,VICE,STAV,TNT,TRU,VICE-Locals,TLC-Locals,SUND,TRAV,WE,AMC,AMTV,ANTV,ASPIRE,AWE,BDMX,BETJ,BOOM,BUZR,CMET,COM,"COM: LOGO",COOK,COZI,DEST,"DIRECTV: ADULT","DIRECTV: YOUNG ADULT","DISH: MEN",DIY,DSLC,BETS,CI,CMT,"COM: DFC","COM: VICE",DABL,DFC,"DIRECTV: MEN - UA",DISC,"DISH: NEWS",DRAM,E!,"ELR
  • Heap Blocks: exact=113
7. 1.414 1.414 ↓ 1.4 954 1

Bitmap Index Scan on network_rotations_station_code (cost=0.00..161.67 rows=691 width=0) (actual time=1.414..1.414 rows=954 loops=1)

  • Index Cond: (station_code = ANY ('{IMPRACTICALJOKERS,LEVERAGE,PAWNSTARS,"ESPN-Locals: 30 for 30",VH-1,VICE,STAV,TNT,TRU,VICE-Locals,TLC-Locals,SUND,TRAV,WE,AMC,AMTV,ANTV,ASPIRE,AWE,BDMX,BETJ,BOOM,BUZR,CMET,COM,"COM: LOGO",COOK,COZI,DEST,"DIRECTV: ADULT","DIRECTV: YOUNG ADULT","DISH: MEN",DIY,DSLC,BETS,CI,CMT,"COM: DFC","COM: VICE",DABL,DFC,"DIRECTV: MEN - UA",DISC,"DISH: NEWS",DRAM,E!,"ELR
8.          

CTE filtered_rotations

9. 9.167 15.148 ↓ 1.3 658 1

Bitmap Heap Scan on network_rotations (cost=161.80..368.99 rows=503 width=80) (actual time=6.056..15.148 rows=658 loops=1)

  • Recheck Cond: (station_code = ANY ('{IMPRACTICALJOKERS,LEVERAGE,PAWNSTARS,"ESPN-Locals: 30 for 30",VH-1,VICE,STAV,TNT,TRU,VICE-Locals,TLC-Locals,SUND,TRAV,WE,AMC,AMTV,ANTV,ASPIRE,AWE,BDMX,BETJ,BOOM,BUZR,CMET,COM,"COM: LOGO",COOK,COZI,DEST,"DIRECTV: ADULT","DIRECTV: YOUNG ADULT","DISH: MEN",DIY,DSLC,BETS,CI,CMT,"COM: DFC","COM: VICE",DABL,DFC,"DIRECTV: MEN - UA",DISC,"DISH: NEWS",DRAM,E!,ELRY,FOOD,FUSN,FXX,FM,FUSE,FX,FXM,FYI,HGTV,HIST,IFC,JUST,LGHN,MTV2,MTV3,HISE,LAFF,LIFE,LOGO,MTV,NGCW,OWN,PARA,MTV-Locals,MTV2-Locals,NGC,OVAT,OXYG,REVOLT,FX-Locals,IFC-Locals,AMC-Locals,BRAV-Locals,MSNB-Locals,COOK-Locals,LX,LIFE-Locals,E!-Locals,HIST-Locals}'::text[]))
  • Filter: (NOT archived)
  • Rows Removed by Filter: 301
  • Heap Blocks: exact=113
10. 5.981 5.981 ↓ 1.4 959 1

Bitmap Index Scan on network_rotations_station_code (cost=0.00..161.67 rows=691 width=0) (actual time=5.981..5.981 rows=959 loops=1)

  • Index Cond: (station_code = ANY ('{IMPRACTICALJOKERS,LEVERAGE,PAWNSTARS,"ESPN-Locals: 30 for 30",VH-1,VICE,STAV,TNT,TRU,VICE-Locals,TLC-Locals,SUND,TRAV,WE,AMC,AMTV,ANTV,ASPIRE,AWE,BDMX,BETJ,BOOM,BUZR,CMET,COM,"COM: LOGO",COOK,COZI,DEST,"DIRECTV: ADULT","DIRECTV: YOUNG ADULT","DISH: MEN",DIY,DSLC,BETS,CI,CMT,"COM: DFC","COM: VICE",DABL,DFC,"DIRECTV: MEN - UA",DISC,"DISH: NEWS",DRAM,E!,ELRY,FOOD,FUSN,FXX,FM,FUSE,FX,FXM,FYI,HGTV,HIST,IFC,JUST,LGHN,MTV2,MTV3,HISE,LAFF,LIFE,LOGO,MTV,NGCW,OWN,PARA,MTV-Locals,MTV2-Locals,NGC,OVAT,OXYG,REVOLT,FX-Locals,IFC-Locals,AMC-Locals,BRAV-Locals,MSNB-Locals,COOK-Locals,LX,LIFE-Locals,E!-Locals,HIST-Locals}'::text[]))
11. 2,181.004 2,907.500 ↓ 1.5 658 1

Merge Left Join (cost=540.07..3,659.90 rows=439 width=325) (actual time=310.156..2,907.500 rows=658 loops=1)

  • Merge Cond: (nr.id = r2.network_rotation_id)
  • Join Filter: ((r.broadcast_week < r2.broadcast_week) OR ((r.broadcast_week = r2.broadcast_week) AND (r.id < r2.id)))
  • Rows Removed by Join Filter: 1,611,974
  • Filter: ((r2.id IS NULL) OR ((r.id IS NULL) AND (r2.id IS NOT NULL)))
  • Rows Removed by Filter: 1,576,526
12. 17.419 306.170 ↓ 4.4 35,464 1

Merge Left Join (cost=286.35..411.10 rows=8,149 width=293) (actual time=278.826..306.170 rows=35,464 loops=1)

  • Merge Cond: (nr.id = r.network_rotation_id)
13. 0.886 16.717 ↓ 1.3 658 1

Sort (cost=32.63..33.89 rows=503 width=149) (actual time=16.505..16.717 rows=658 loops=1)

  • Sort Key: nr.id
  • Sort Method: quicksort Memory: 119kB
14. 15.831 15.831 ↓ 1.3 658 1

CTE Scan on filtered_rotations nr (cost=0.00..10.06 rows=503 width=149) (actual time=6.060..15.831 rows=658 loops=1)

15. 33.985 272.034 ↓ 11.5 37,343 1

Sort (cost=253.72..261.82 rows=3,240 width=148) (actual time=262.314..272.034 rows=37,343 loops=1)

  • Sort Key: r.network_rotation_id
  • Sort Method: quicksort Memory: 4,029kB
16. 238.049 238.049 ↓ 11.5 37,343 1

CTE Scan on filtered_rates r (cost=0.00..64.80 rows=3,240 width=148) (actual time=2.192..238.049 rows=37,343 loops=1)

17. 409.552 420.326 ↓ 984.7 3,190,395 1

Sort (cost=253.72..261.82 rows=3,240 width=40) (actual time=26.070..420.326 rows=3,190,395 loops=1)

  • Sort Key: r2.network_rotation_id
  • Sort Method: quicksort Memory: 3,863kB
18. 10.774 10.774 ↓ 11.5 37,343 1

CTE Scan on filtered_rates r2 (cost=0.00..64.80 rows=3,240 width=40) (actual time=0.028..10.774 rows=37,343 loops=1)

Planning time : 16.579 ms