explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VJT9

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 23,589.883 ↑ 1.0 14 1

Limit (cost=1,520,925.64..1,520,926.41 rows=14 width=1,571) (actual time=23,589.867..23,589.883 rows=14 loops=1)

2.          

CTE user_channels

3. 0.049 20.426 ↓ 2.3 150 1

Unique (cost=0.28..1,403.27 rows=64 width=4) (actual time=0.062..20.426 rows=150 loops=1)

4. 20.377 20.377 ↓ 2.3 150 1

Index Scan using sentv_channel_channel_id_idx on sentv_channel c_2 (cost=0.28..1,403.11 rows=64 width=4) (actual time=0.058..20.377 rows=150 loops=1)

  • Filter: ((cardinality(region_dma_codes) > 0) AND (('SUPER'::text = ANY ('{EXTENDED,MACHINIMA,PREMIUM,EPIX,SPORTS_PACK,BASIC}'::text[])) OR ('{EXTENDED,MACHINIMA,PREMIUM,EPIX,SPORTS_PACK,BASIC}'::text[] && ent_keywords)) AND (((channel_id)::text || '_RESTRICTED'::text) <> ALL ('{EXTENDED,MACHINIMA,PREMIUM,EPIX,SPORTS_PACK,BASIC}'::text[])) AND (('NAT'::text = ANY (region_dma_codes)) OR ('NAT-EA'::text = ANY (region_dma_codes)) OR (('501'::text = ANY (region_dma_codes)) AND (('501'::text <> ALL (sub_dmas)) OR ('07107'::text = ANY (sub_dma_zips)) OR (sub_dmas = '{}'::text[]) OR (sub_dma_zips = '{}'::text[])))))
  • Rows Removed by Filter: 1043
5. 0.370 23,589.880 ↑ 14.2 14 1

WindowAgg (cost=1,519,522.37..1,519,533.31 rows=199 width=1,571) (actual time=23,589.867..23,589.880 rows=14 loops=1)

6. 0.044 23,589.510 ↑ 2.6 76 1

Merge Append (cost=1,519,522.37..1,519,527.34 rows=199 width=1,549) (actual time=23,589.302..23,589.510 rows=76 loops=1)

  • Sort Key: whs.watch_date DESC NULLS LAST, rolledupseriesprogram.watch_date DESC NULLS LAST
7. 0.545 23,583.918 ↑ 2.8 72 1

Sort (cost=1,518,721.88..1,518,722.38 rows=198 width=1,549) (actual time=23,583.753..23,583.918 rows=72 loops=1)

  • Sort Key: whs.watch_date DESC NULLS LAST, rolledupseriesprogram.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 695kB
8. 1.245 23,583.373 ↑ 2.8 72 1

Nested Loop Left Join (cost=7,667.78..1,518,714.33 rows=198 width=1,549) (actual time=946.993..23,583.373 rows=72 loops=1)

  • Join Filter: (s.series_id = whs.series_id)
  • Rows Removed by Join Filter: 13690
9. 0.239 23,574.568 ↑ 2.8 72 1

Nested Loop (cost=7,667.22..1,518,375.48 rows=198 width=1,529) (actual time=946.974..23,574.568 rows=72 loops=1)

10. 0.244 653.889 ↑ 1.4 145 1

Nested Loop (cost=311.23..61,884.40 rows=198 width=21) (actual time=16.838..653.889 rows=145 loops=1)

11. 0.160 1.319 ↑ 1.1 174 1

Nested Loop (cost=0.98..450.49 rows=198 width=12) (actual time=0.026..1.319 rows=174 loops=1)

12. 0.289 0.289 ↑ 1.1 174 1

Index Scan using sentv_user_favorite_series_pkey on sentv_user_favorite_series fs (cost=0.56..189.38 rows=198 width=12) (actual time=0.016..0.289 rows=174 loops=1)

  • Index Cond: (profile_id = 4606705)
13. 0.870 0.870 ↑ 1.0 1 174

Index Only Scan using sentv_series_series_id_idx on sentv_series s (cost=0.42..1.31 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=174)

  • Index Cond: (series_id = fs.series_id)
  • Heap Fetches: 9
14. 0.174 652.326 ↑ 1.0 1 174

Limit (cost=310.25..310.25 rows=1 width=21) (actual time=3.749..3.749 rows=1 loops=174)

15. 1.566 652.152 ↑ 1.0 1 174

Sort (cost=310.25..310.25 rows=1 width=21) (actual time=3.748..3.748 rows=1 loops=174)

  • Sort Key: plw.season_number DESC NULLS LAST, plw.episode_number DESC NULLS LAST, pwhlw.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
16. 12.363 650.586 ↓ 9.0 9 174

Nested Loop (cost=1.00..310.24 rows=1 width=21) (actual time=0.259..3.739 rows=9 loops=174)

17. 69.774 69.774 ↓ 5.5 467 174

Index Scan using sentv_program_series_id_idx on sentv_program plw (cost=0.42..88.81 rows=85 width=12) (actual time=0.007..0.401 rows=467 loops=174)

  • Index Cond: (series_id = s.series_id)
18. 568.449 568.449 ↓ 0.0 0 81,207

Index Scan using sentv_user_program_watch_history_pkey on sentv_user_program_watch_history pwhlw (cost=0.57..2.59 rows=1 width=13) (actual time=0.007..0.007 rows=0 loops=81,207)

  • Index Cond: ((profile_id = 4606705) AND (program_id = plw.program_id))
19. 0.290 22,920.440 ↓ 0.0 0 145

Subquery Scan on rolledupseriesprogram (cost=7,355.99..7,356.01 rows=1 width=1,517) (actual time=158.072..158.072 rows=0 loops=145)

  • Filter: CASE WHEN (rolledupseriesprogram.season_id < 0) THEN (rolledupseriesprogram.percent IS NOT NULL) ELSE true END
  • Rows Removed by Filter: 0
20. 0.145 22,920.150 ↑ 1.0 1 145

Limit (cost=7,355.99..7,355.99 rows=1 width=1,549) (actual time=158.070..158.070 rows=1 loops=145)

21. 2.755 22,920.005 ↑ 1.0 1 145

Sort (cost=7,355.99..7,355.99 rows=1 width=1,549) (actual time=158.069..158.069 rows=1 loops=145)

  • Sort Key: p.season_number, p.episode_number, whp.watch_date DESC NULLS LAST, (CASE WHEN (((array_agg(CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) >= now()) THEN ((array_agg(CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) ELSE NULL::timestamp without time zone END), (CASE WHEN (((array_agg(a.start_date ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) >= now()) THEN ((array_agg(a.start_date ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) ELSE NULL::timestamp without time zone END), (CASE WHEN (((array_agg(CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) < now()) THEN ((array_agg(CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) ELSE NULL::timestamp without time zone END) DESC NULLS LAST, (CASE WHEN (((array_agg(a.start_date ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) < now()) THEN ((array_agg(a.start_date ORDER BY CASE WHEN (a.calculated_broadcast_date IS NULL) THEN a.broadcast_date ELSE a.calculated_broadcast_date END DESC, CASE WHEN (a.start_date <= now()) THEN a.start_date ELSE '-infinity'::timestamp without time zone END DESC, CASE WHEN (a.start_date > now()) THEN a.start_date ELSE 'infinity'::timestamp without time zone END, a.airing_id))[1]) ELSE NULL::timestamp without time zone END) DESC NULLS LAST
  • Sort Method: quicksort Memory: 29kB
22. 1.789 22,917.250 ↓ 9.0 9 145

Nested Loop Left Join (cost=7,353.74..7,355.98 rows=1 width=1,549) (actual time=157.784..158.050 rows=9 loops=145)

23. 46.110 22,911.450 ↓ 9.0 9 145

GroupAggregate (cost=7,353.17..7,353.34 rows=1 width=1,495) (actual time=157.777..158.010 rows=9 loops=145)

  • Group Key: p.program_id
24. 5.800 22,865.340 ↓ 15.0 15 145

Sort (cost=7,353.17..7,353.17 rows=1 width=2,265) (actual time=157.684..157.692 rows=15 loops=145)

  • Sort Key: p.program_id
  • Sort Method: quicksort Memory: 29kB
25. 0.867 22,859.540 ↓ 15.0 15 145

Nested Loop Anti Join (cost=1.82..7,353.16 rows=1 width=2,265) (actual time=34.854..157.652 rows=15 loops=145)

26. 1.393 22,854.175 ↓ 16.0 16 145

Nested Loop (cost=1.54..7,346.28 rows=1 width=2,350) (actual time=34.851..157.615 rows=16 loops=145)

27. 53.400 22,836.920 ↓ 3.2 16 145

Nested Loop (cost=0.98..7,341.39 rows=5 width=2,354) (actual time=34.841..157.496 rows=16 loops=145)

28. 81.200 81.200 ↓ 10.1 435 145

Index Scan using sentv_program_series_id_idx on sentv_program p (cost=0.42..89.87 rows=43 width=1,343) (actual time=0.038..0.560 rows=435 loops=145)

  • Index Cond: (series_id = s.series_id)
  • Filter: (((plw.season_number IS NULL) AND (plw.episode_number IS NULL)) OR CASE WHEN (pwhlw.fully_watched IS TRUE) THEN ((season_number > plw.season_number) OR ((season_number = plw.season_number) AND (episode_number > plw.episode_number))) ELSE ((season_number = plw.season_number) AND (episode_number = plw.episode_number)) END)
  • Rows Removed by Filter: 117
29. 2,963.914 22,702.320 ↓ 0.0 0 63,062

Nested Loop (cost=0.56..168.00 rows=64 width=1,015) (actual time=0.354..0.360 rows=0 loops=63,062)

30. 819.806 819.806 ↓ 2.3 150 63,062

CTE Scan on user_channels c (cost=0.00..1.28 rows=64 width=4) (actual time=0.000..0.013 rows=150 loops=63,062)

31. 18,918.600 18,918.600 ↓ 0.0 0 9,459,300

Index Scan using idx_sentv_airing_complex1 on sentv_airing a (cost=0.56..2.59 rows=1 width=1,019) (actual time=0.002..0.002 rows=0 loops=9,459,300)

  • Index Cond: ((channel_id = c.channel_id) AND (program_id = p.program_id))
  • Filter: ((start_date <= '2019-09-10 18:12:51.949'::timestamp without time zone) AND (dvr_expiration_date <> end_date) AND (dvr_expiration_date > now()) AND ((age_rating || '_RESTRICTED'::text) <> ALL ('{EXTENDED,MACHINIMA,PREMIUM,EPIX,SPORTS_PACK,BASIC}'::text[])))
  • Rows Removed by Filter: 0
32. 15.862 15.862 ↑ 1.0 1 2,266

Index Scan using sentv_user_favorite_series_pkey on sentv_user_favorite_series fs_1 (cost=0.56..0.97 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=2,266)

  • Index Cond: ((profile_id = 4606705) AND (series_id = a.series_id))
  • Filter: ((favorite_date < a.expiration_date) AND (CASE WHEN (a.type = 'vod'::text) THEN 'vod'::text WHEN (now() < a.start_date) THEN 'coming_up'::text WHEN ((now() > a.start_date) AND (now() < a.end_date)) THEN 'live'::text WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN 'dvr'::text WHEN ((CASE WHEN (a.type = 'vod'::text) THEN a.expiration_date WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN a.dvr_expiration_date WHEN (a.fallback_startover_date IS NOT NULL) THEN a.fallback_startover_date ELSE a.expiration_date END IS NOT NULL) AND (now() < CASE WHEN (a.type = 'vod'::text) THEN a.expiration_date WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN a.dvr_expiration_date WHEN (a.fallback_startover_date IS NOT NULL) THEN a.fallback_startover_date ELSE a.expiration_date END)) THEN 'catchup'::text WHEN a.vod_replacement_available THEN 'vod'::text ELSE 'no_longer_available'::text END <> 'no_longer_available'::text) AND (((CASE WHEN (a.type = 'vod'::text) THEN 'vod'::text WHEN (now() < a.start_date) THEN 'coming_up'::text WHEN ((now() > a.start_date) AND (now() < a.end_date)) THEN 'live'::text WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN 'dvr'::text WHEN ((CASE WHEN (a.type = 'vod'::text) THEN a.expiration_date WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN a.dvr_expiration_date WHEN (a.fallback_startover_date IS NOT NULL) THEN a.fallback_startover_date ELSE a.expiration_date END IS NOT NULL) AND (now() < CASE WHEN (a.type = 'vod'::text) THEN a.expiration_date WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN a.dvr_expiration_date WHEN (a.fallback_startover_date IS NOT NULL) THEN a.fallback_startover_date ELSE a.expiration_date END)) THEN 'catchup'::text WHEN a.vod_replacement_available THEN 'vod'::text ELSE 'no_longer_available'::text END <> 'vod'::text) AND (((favorite_date < a.end_date) AND (a.dvr_expiration_date > now())) OR (a.expiration_date > now()))) OR ((CASE WHEN (a.type = 'vod'::text) THEN 'vod'::text WHEN (now() < a.start_date) THEN 'coming_up'::text WHEN ((now() > a.start_date) AND (now() < a.end_date)) THEN 'live'::text WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN 'dvr'::text WHEN ((CASE WHEN (a.type = 'vod'::text) THEN a.expiration_date WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN a.dvr_expiration_date WHEN (a.fallback_startover_date IS NOT NULL) THEN a.fallback_startover_date ELSE a.expiration_date END IS NOT NULL) AND (now() < CASE WHEN (a.type = 'vod'::text) THEN a.expiration_date WHEN ((now() < a.dvr_expiration_date) AND (favorite_date < a.end_date)) THEN a.dvr_expiration_date WHEN (a.fallback_startover_date IS NOT NULL) THEN a.fallback_startover_date ELSE a.expiration_date END)) THEN 'catchup'::text WHEN a.vod_replacement_available THEN 'vod'::text ELSE 'no_longer_available'::text END = 'vod'::text) AND (a.vod_ready_date <= now()) AND (a.expiration_date >= now()))))
  • Rows Removed by Filter: 0
33. 4.498 4.498 ↓ 0.0 0 2,249

Index Scan using sentv_blackout_rule_pkey on sentv_blackout_rule b (cost=0.28..3.55 rows=5 width=1,425) (actual time=0.002..0.002 rows=0 loops=2,249)

  • Index Cond: ((rule_name)::text = ANY (a.bo_rules))
  • Filter: CASE WHEN (('501'::text = ANY (dmas)) OR ('07107'::text = ANY (zipcodes)) OR ('PS4'::text = ANY (device_types)) OR ((cardinality(media_blackouts) > 0) AND (('501_BLACKOUT'::text = ANY (a.bo_keywords)) OR (a.bo_keywords && media_blackouts)))) THEN CASE WHEN (rule_type = 1) THEN true ELSE false END ELSE CASE WHEN (rule_type = 2) THEN true ELSE false END END
  • Rows Removed by Filter: 0
34. 4.011 4.011 ↓ 0.0 0 1,337

Index Scan using sentv_user_program_watch_history_pkey on sentv_user_program_watch_history whp (cost=0.57..2.59 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=1,337)

  • Index Cond: ((profile_id = 4606705) AND (program_id = p.program_id))
35. 0.660 7.560 ↓ 2.2 191 72

Materialize (cost=0.56..86.61 rows=85 width=12) (actual time=0.000..0.105 rows=191 loops=72)

36. 6.900 6.900 ↓ 2.2 191 1

Index Scan using sentv_user_series_watch_history_pkey on sentv_user_series_watch_history whs (cost=0.56..86.19 rows=85 width=12) (actual time=0.011..6.900 rows=191 loops=1)

  • Index Cond: (profile_id = 4606705)
37. 0.011 5.548 ↓ 4.0 4 1

Sort (cost=800.48..800.49 rows=1 width=1,549) (actual time=5.546..5.548 rows=4 loops=1)

  • Sort Key: (NULL::timestamp without time zone) DESC NULLS LAST, whp_1.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 45kB
38. 0.017 5.537 ↓ 4.0 4 1

Sort (cost=800.46..800.46 rows=1 width=1,549) (actual time=5.536..5.537 rows=4 loops=1)

  • Sort Key: whp_1.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 45kB
39. 0.012 5.520 ↓ 4.0 4 1

Nested Loop (cost=795.44..800.45 rows=1 width=1,549) (actual time=0.868..5.520 rows=4 loops=1)

40. 0.656 1.252 ↓ 7.0 14 1

GroupAggregate (cost=794.86..795.22 rows=2 width=1,503) (actual time=0.678..1.252 rows=14 loops=1)

  • Group Key: p_1.program_id, fp.favorite_date
41. 0.057 0.596 ↓ 14.5 29 1

Sort (cost=794.86..794.87 rows=2 width=2,273) (actual time=0.584..0.596 rows=29 loops=1)

  • Sort Key: p_1.program_id, fp.favorite_date
  • Sort Method: quicksort Memory: 139kB
42. 0.014 0.539 ↓ 14.5 29 1

Nested Loop Anti Join (cost=4.08..794.85 rows=2 width=2,273) (actual time=0.122..0.539 rows=29 loops=1)

43. 0.016 0.525 ↓ 9.7 29 1

Hash Join (cost=3.81..784.09 rows=3 width=2,358) (actual time=0.119..0.525 rows=29 loops=1)

  • Hash Cond: (a_1.channel_id = c_1.channel_id)
44. 0.123 0.480 ↓ 1.0 58 1

Nested Loop (cost=1.73..781.77 rows=57 width=2,362) (actual time=0.082..0.480 rows=58 loops=1)

  • Join Filter: ((fp_1.favorite_date < a_1.expiration_date) AND (fp_1.program_id = a_1.program_id) AND (CASE WHEN (a_1.type = 'vod'::text) THEN 'vod'::text WHEN (now() < a_1.start_date) THEN 'coming_up'::text WHEN ((now() > a_1.start_date) AND (now() < a_1.end_date)) THEN 'live'::text WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN 'dvr'::text WHEN ((CASE WHEN (a_1.type = 'vod'::text) THEN a_1.expiration_date WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN a_1.dvr_expiration_date WHEN (a_1.fallback_startover_date IS NOT NULL) THEN a_1.fallback_startover_date ELSE a_1.expiration_date END IS NOT NULL) AND (now() < CASE WHEN (a_1.type = 'vod'::text) THEN a_1.expiration_date WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN a_1.dvr_expiration_date WHEN (a_1.fallback_startover_date IS NOT NULL) THEN a_1.fallback_startover_date ELSE a_1.expiration_date END)) THEN 'catchup'::text WHEN a_1.vod_replacement_available THEN 'vod'::text ELSE 'no_longer_available'::text END <> 'no_longer_available'::text) AND (((CASE WHEN (a_1.type = 'vod'::text) THEN 'vod'::text WHEN (now() < a_1.start_date) THEN 'coming_up'::text WHEN ((now() > a_1.start_date) AND (now() < a_1.end_date)) THEN 'live'::text WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN 'dvr'::text WHEN ((CASE WHEN (a_1.type = 'vod'::text) THEN a_1.expiration_date WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN a_1.dvr_expiration_date WHEN (a_1.fallback_startover_date IS NOT NULL) THEN a_1.fallback_startover_date ELSE a_1.expiration_date END IS NOT NULL) AND (now() < CASE WHEN (a_1.type = 'vod'::text) THEN a_1.expiration_date WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN a_1.dvr_expiration_date WHEN (a_1.fallback_startover_date IS NOT NULL) THEN a_1.fallback_startover_date ELSE a_1.expiration_date END)) THEN 'catchup'::text WHEN a_1.vod_replacement_available THEN 'vod'::text ELSE 'no_longer_available'::text END <> 'vod'::text) AND (((fp_1.favorite_date < a_1.end_date) AND (a_1.dvr_expiration_date > now())) OR (a_1.expiration_date > now()))) OR ((CASE WHEN (a_1.type = 'vod'::text) THEN 'vod'::text WHEN (now() < a_1.start_date) THEN 'coming_up'::text WHEN ((now() > a_1.start_date) AND (now() < a_1.end_date)) THEN 'live'::text WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN 'dvr'::text WHEN ((CASE WHEN (a_1.type = 'vod'::text) THEN a_1.expiration_date WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN a_1.dvr_expiration_date WHEN (a_1.fallback_startover_date IS NOT NULL) THEN a_1.fallback_startover_date ELSE a_1.expiration_date END IS NOT NULL) AND (now() < CASE WHEN (a_1.type = 'vod'::text) THEN a_1.expiration_date WHEN ((now() < a_1.dvr_expiration_date) AND (fp_1.favorite_date < a_1.end_date)) THEN a_1.dvr_expiration_date WHEN (a_1.fallback_startover_date IS NOT NULL) THEN a_1.fallback_startover_date ELSE a_1.expiration_date END)) THEN 'catchup'::text WHEN a_1.vod_replacement_available THEN 'vod'::text ELSE 'no_longer_available'::text END = 'vod'::text) AND (a_1.vod_ready_date <= now()) AND (a_1.expiration_date >= now()))))
  • Rows Removed by Join Filter: 2
45. 0.020 0.141 ↓ 1.4 18 1

Nested Loop (cost=1.30..401.71 rows=13 width=1,367) (actual time=0.023..0.141 rows=18 loops=1)

46. 0.017 0.049 ↓ 1.4 18 1

Merge Join (cost=0.88..370.09 rows=13 width=24) (actual time=0.015..0.049 rows=18 loops=1)

  • Merge Cond: (fp.program_id = fp_1.program_id)
47. 0.012 0.012 ↑ 10.7 18 1

Index Scan using sentv_user_favorite_program_pkey on sentv_user_favorite_program fp (cost=0.44..184.50 rows=192 width=12) (actual time=0.008..0.012 rows=18 loops=1)

  • Index Cond: (profile_id = 4606705)
48. 0.020 0.020 ↑ 10.7 18 1

Index Scan using sentv_user_favorite_program_pkey on sentv_user_favorite_program fp_1 (cost=0.44..184.50 rows=192 width=12) (actual time=0.003..0.020 rows=18 loops=1)

  • Index Cond: (profile_id = 4606705)
49. 0.072 0.072 ↑ 1.0 1 18

Index Scan using sentv_program_pkey on sentv_program p_1 (cost=0.42..2.42 rows=1 width=1,347) (actual time=0.004..0.004 rows=1 loops=18)

  • Index Cond: (program_id = fp_1.program_id)
50. 0.216 0.216 ↑ 37.3 3 18

Index Scan using sentv_airing_program_id_idx on sentv_airing a_1 (cost=0.43..7.40 rows=112 width=1,015) (actual time=0.007..0.012 rows=3 loops=18)

  • Index Cond: (program_id = p_1.program_id)
  • Filter: ((start_date <= '2019-09-10 18:12:51.949'::timestamp without time zone) AND (dvr_expiration_date <> end_date) AND (dvr_expiration_date > now()) AND ((age_rating || '_RESTRICTED'::text) <> ALL ('{EXTENDED,MACHINIMA,PREMIUM,EPIX,SPORTS_PACK,BASIC}'::text[])))
  • Rows Removed by Filter: 1
51. 0.013 0.029 ↓ 2.3 150 1

Hash (cost=1.28..1.28 rows=64 width=4) (actual time=0.029..0.029 rows=150 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
52. 0.016 0.016 ↓ 2.3 150 1

CTE Scan on user_channels c_1 (cost=0.00..1.28 rows=64 width=4) (actual time=0.000..0.016 rows=150 loops=1)

53. 0.000 0.000 ↓ 0.0 0 29

Index Scan using sentv_blackout_rule_pkey on sentv_blackout_rule b_1 (cost=0.28..3.55 rows=5 width=1,425) (actual time=0.000..0.000 rows=0 loops=29)

  • Index Cond: ((rule_name)::text = ANY (a_1.bo_rules))
  • Filter: CASE WHEN (('501'::text = ANY (dmas)) OR ('07107'::text = ANY (zipcodes)) OR ('PS4'::text = ANY (device_types)) OR ((cardinality(media_blackouts) > 0) AND (('501_BLACKOUT'::text = ANY (a_1.bo_keywords)) OR (a_1.bo_keywords && media_blackouts)))) THEN CASE WHEN (rule_type = 1) THEN true ELSE false END ELSE CASE WHEN (rule_type = 2) THEN true ELSE false END END
54. 4.256 4.256 ↓ 0.0 0 14

Index Scan using sentv_user_program_watch_history_pkey on sentv_user_program_watch_history whp_1 (cost=0.57..2.59 rows=1 width=26) (actual time=0.304..0.304 rows=0 loops=14)

  • Index Cond: ((profile_id = 4606705) AND (program_id = p_1.program_id))
  • Filter: (NOT fully_watched)
  • Rows Removed by Filter: 0
Planning time : 17.238 ms
Execution time : 23,591.359 ms