explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LU61

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 420.817 ↑ 1.0 14 1

Limit (cost=1,617,136.49..1,617,137.26 rows=14 width=1,574) (actual time=420.800..420.817 rows=14 loops=1)

2. 0.162 420.816 ↑ 14.1 14 1

WindowAgg (cost=1,617,136.49..1,617,147.37 rows=198 width=1,574) (actual time=420.799..420.816 rows=14 loops=1)

3. 0.015 420.654 ↑ 4.2 47 1

Merge Append (cost=1,617,136.49..1,617,141.43 rows=198 width=1,552) (actual time=420.610..420.654 rows=47 loops=1)

  • Sort Key: whs.watch_date DESC NULLS LAST, rolledupseriesprogram.watch_date DESC NULLS LAST
4. 0.193 411.647 ↑ 7.3 27 1

Sort (cost=1,616,316.68..1,616,317.18 rows=197 width=1,552) (actual time=411.623..411.647 rows=27 loops=1)

  • Sort Key: whs.watch_date DESC NULLS LAST, rolledupseriesprogram.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 171kB
5. 0.303 411.454 ↑ 7.3 27 1

Nested Loop Left Join (cost=8,201.73..1,616,309.18 rows=197 width=1,552) (actual time=4.640..411.454 rows=27 loops=1)

  • Join Filter: (s.series_id = whs.series_id)
  • Rows Removed by Join Filter: 2707
6. 0.070 410.773 ↑ 7.3 27 1

Nested Loop (cost=8,201.16..1,615,932.01 rows=197 width=1,532) (actual time=4.605..410.773 rows=27 loops=1)

7. 0.154 212.953 ↑ 1.7 113 1

Nested Loop (cost=314.86..62,324.74 rows=197 width=21) (actual time=2.117..212.953 rows=113 loops=1)

8. 0.102 1.367 ↓ 1.1 214 1

Nested Loop (cost=0.98..486.10 rows=197 width=12) (actual time=0.021..1.367 rows=214 loops=1)

9. 0.195 0.195 ↓ 1.1 214 1

Index Scan using sentv_user_favorite_series_pkey on sentv_user_favorite_series fs (cost=0.56..188.43 rows=197 width=12) (actual time=0.014..0.195 rows=214 loops=1)

  • Index Cond: (profile_id = 397947)
10. 1.070 1.070 ↑ 1.0 1 214

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

  • Index Cond: (series_id = fs.series_id)
  • Heap Fetches: 58
11. 0.214 211.432 ↑ 1.0 1 214

Limit (cost=313.88..313.88 rows=1 width=21) (actual time=0.988..0.988 rows=1 loops=214)

12. 1.284 211.218 ↑ 1.0 1 214

Sort (cost=313.88..313.88 rows=1 width=21) (actual time=0.987..0.987 rows=1 loops=214)

  • Sort Key: plw.season_number DESC NULLS LAST, plw.episode_number DESC NULLS LAST, pwhlw.watch_date DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 25kB
13. 36.006 209.934 ↓ 7.0 7 214

Nested Loop (cost=1.00..313.87 rows=1 width=21) (actual time=0.135..0.981 rows=7 loops=214)

14. 56.710 56.710 ↓ 3.2 274 214

Index Scan using sentv_program_series_id_idx on sentv_program plw (cost=0.42..89.84 rows=86 width=12) (actual time=0.007..0.265 rows=274 loops=214)

  • Index Cond: (series_id = s.series_id)
15. 117.218 117.218 ↓ 0.0 0 58,609

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.002..0.002 rows=0 loops=58,609)

  • Index Cond: ((profile_id = 397947) AND (program_id = plw.program_id))
16. 0.226 197.750 ↓ 0.0 0 113

Subquery Scan on rolledupseriesprogram (cost=7,886.30..7,886.32 rows=1 width=1,520) (actual time=1.749..1.750 rows=0 loops=113)

  • Filter: CASE WHEN (rolledupseriesprogram.season_id < 0) THEN (rolledupseriesprogram.percent IS NOT NULL) ELSE true END
  • Rows Removed by Filter: 0
17. 0.113 197.524 ↓ 0.0 0 113

Limit (cost=7,886.30..7,886.31 rows=1 width=1,552) (actual time=1.748..1.748 rows=0 loops=113)

18. 1.130 197.411 ↓ 0.0 0 113

Sort (cost=7,886.30..7,886.31 rows=1 width=1,552) (actual time=1.747..1.747 rows=0 loops=113)

  • 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: 25kB
19. 0.868 196.281 ↓ 3.0 3 113

Nested Loop Left Join (cost=7,884.06..7,886.29 rows=1 width=1,552) (actual time=1.629..1.737 rows=3 loops=113)

20. 14.125 193.908 ↓ 3.0 3 113

GroupAggregate (cost=7,883.48..7,883.66 rows=1 width=1,498) (actual time=1.625..1.716 rows=3 loops=113)

  • Group Key: p.program_id
21. 1.356 179.783 ↓ 4.0 4 113

Sort (cost=7,883.48..7,883.49 rows=1 width=2,269) (actual time=1.589..1.591 rows=4 loops=113)

  • Sort Key: p.program_id
  • Sort Method: quicksort Memory: 25kB
22. 0.452 178.427 ↓ 4.0 4 113

Nested Loop Anti Join (cost=1.97..7,883.47 rows=1 width=2,269) (actual time=0.492..1.579 rows=4 loops=113)

23. 0.765 177.975 ↓ 4.0 4 113

Nested Loop (cost=1.70..7,876.60 rows=1 width=2,352) (actual time=0.384..1.575 rows=4 loops=113)

24. 0.000 173.794 ↑ 1.2 4 113

Nested Loop (cost=1.13..7,871.73 rows=5 width=2,356) (actual time=0.378..1.538 rows=4 loops=113)

25. 0.000 112.435 ↑ 3.4 27 113

Nested Loop (cost=0.85..7,835.78 rows=91 width=2,360) (actual time=0.318..0.995 rows=27 loops=113)

26. 48.364 48.364 ↓ 4.5 193 113

Index Scan using sentv_program_series_id_idx on sentv_program p (cost=0.42..90.91 rows=43 width=1,346) (actual time=0.087..0.428 rows=193 loops=113)

  • 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: 282
27. 65.382 65.382 ↓ 0.0 0 21,794

Index Scan using sentv_airing_program_id_idx on sentv_airing a (cost=0.43..178.95 rows=116 width=1,018) (actual time=0.002..0.003 rows=0 loops=21,794)

  • Index Cond: (program_id = p.program_id)
  • Filter: ((start_date <= '2019-09-05 23:42:07.624'::timestamp without time zone) AND (dvr_expiration_date <> end_date) AND (dvr_expiration_date > now()) AND ((age_rating || '_RESTRICTED'::text) <> ALL ('{EXTENDED,BASIC}'::text[])))
  • Rows Removed by Filter: 0
28. 61.820 61.820 ↓ 0.0 0 3,091

Index Scan using sentv_channel_channel_id_idx on sentv_channel c (cost=0.28..0.39 rows=1 width=4) (actual time=0.019..0.020 rows=0 loops=3,091)

  • Index Cond: (channel_id = a.channel_id)
  • Filter: ((cardinality(region_dma_codes) > 0) AND (('SUPER'::text = ANY ('{EXTENDED,BASIC}'::text[])) OR ('{EXTENDED,BASIC}'::text[] && ent_keywords)) AND (((channel_id)::text || '_RESTRICTED'::text) <> ALL ('{EXTENDED,BASIC}'::text[])) AND (('NAT'::text = ANY (region_dma_codes)) OR ('NAT-EA'::text = ANY (region_dma_codes)) OR (('515'::text = ANY (region_dma_codes)) AND (('515'::text <> ALL (sub_dmas)) OR ('45202'::text = ANY (sub_dma_zips)) OR (sub_dmas = '{}'::text[]) OR (sub_dma_zips = '{}'::text[])))))
  • Rows Removed by Filter: 1
29. 3.416 3.416 ↑ 1.0 1 488

Index Scan using sentv_user_favorite_series_pkey on sentv_user_favorite_series fs_1 (cost=0.56..0.96 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=488)

  • Index Cond: ((profile_id = 397947) 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
30. 0.000 0.000 ↓ 0.0 0 486

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.000..0.000 rows=0 loops=486)

  • Index Cond: ((rule_name)::text = ANY (a.bo_rules))
  • Filter: CASE WHEN (('515'::text = ANY (dmas)) OR ('45202'::text = ANY (zipcodes)) OR ('PS4'::text = ANY (device_types)) OR ((cardinality(media_blackouts) > 0) AND (('515_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
31. 1.505 1.505 ↓ 0.0 0 301

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.005..0.005 rows=0 loops=301)

  • Index Cond: ((profile_id = 397947) AND (program_id = p.program_id))
32. 0.251 0.378 ↓ 1.1 101 27

Materialize (cost=0.56..96.68 rows=95 width=12) (actual time=0.002..0.014 rows=101 loops=27)

33. 0.127 0.127 ↓ 1.1 101 1

Index Scan using sentv_user_series_watch_history_pkey on sentv_user_series_watch_history whs (cost=0.56..96.20 rows=95 width=12) (actual time=0.026..0.127 rows=101 loops=1)

  • Index Cond: (profile_id = 397947)
34. 0.033 8.992 ↓ 20.0 20 1

Sort (cost=819.80..819.80 rows=1 width=1,552) (actual time=8.986..8.992 rows=20 loops=1)

  • Sort Key: (NULL::timestamp without time zone) DESC NULLS LAST, whp_1.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 111kB
35. 0.063 8.959 ↓ 20.0 20 1

Sort (cost=819.77..819.78 rows=1 width=1,552) (actual time=8.950..8.959 rows=20 loops=1)

  • Sort Key: whp_1.watch_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 111kB
36. 0.007 8.896 ↓ 20.0 20 1

Nested Loop (cost=814.75..819.76 rows=1 width=1,552) (actual time=6.917..8.896 rows=20 loops=1)

37. 2.100 8.673 ↓ 27.0 54 1

GroupAggregate (cost=814.18..814.53 rows=2 width=1,506) (actual time=6.672..8.673 rows=54 loops=1)

  • Group Key: p_1.program_id, fp.favorite_date
38. 0.208 6.573 ↓ 43.0 86 1

Sort (cost=814.18..814.18 rows=2 width=2,277) (actual time=6.543..6.573 rows=86 loops=1)

  • Sort Key: p_1.program_id, fp.favorite_date
  • Sort Method: quicksort Memory: 346kB
39. 0.078 6.365 ↓ 43.0 86 1

Nested Loop Anti Join (cost=2.28..814.17 rows=2 width=2,277) (actual time=0.200..6.365 rows=86 loops=1)

40. 0.156 6.287 ↓ 28.7 86 1

Nested Loop (cost=2.01..803.41 rows=3 width=2,360) (actual time=0.198..6.287 rows=86 loops=1)

41. 0.667 3.471 ↓ 3.4 190 1

Nested Loop (cost=1.73..781.28 rows=56 width=2,364) (actual time=0.182..3.471 rows=190 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()))))
42. 0.132 1.346 ↓ 12.5 162 1

Nested Loop (cost=1.30..399.79 rows=13 width=1,370) (actual time=0.029..1.346 rows=162 loops=1)

43. 0.133 0.242 ↓ 12.5 162 1

Merge Join (cost=0.88..368.18 rows=13 width=24) (actual time=0.020..0.242 rows=162 loops=1)

  • Merge Cond: (fp.program_id = fp_1.program_id)
44. 0.050 0.050 ↑ 1.2 162 1

Index Scan using sentv_user_favorite_program_pkey on sentv_user_favorite_program fp (cost=0.44..183.55 rows=191 width=12) (actual time=0.011..0.050 rows=162 loops=1)

  • Index Cond: (profile_id = 397947)
45. 0.059 0.059 ↑ 1.2 162 1

Index Scan using sentv_user_favorite_program_pkey on sentv_user_favorite_program fp_1 (cost=0.44..183.55 rows=191 width=12) (actual time=0.005..0.059 rows=162 loops=1)

  • Index Cond: (profile_id = 397947)
46. 0.972 0.972 ↑ 1.0 1 162

Index Scan using sentv_program_pkey on sentv_program p_1 (cost=0.42..2.42 rows=1 width=1,350) (actual time=0.005..0.006 rows=1 loops=162)

  • Index Cond: (program_id = fp_1.program_id)
47. 1.458 1.458 ↑ 116.0 1 162

Index Scan using sentv_airing_program_id_idx on sentv_airing a_1 (cost=0.43..6.72 rows=116 width=1,014) (actual time=0.006..0.009 rows=1 loops=162)

  • Index Cond: (program_id = p_1.program_id)
  • Filter: ((start_date <= '2019-09-05 23:42:07.624'::timestamp without time zone) AND (dvr_expiration_date <> end_date) AND (dvr_expiration_date > now()) AND ((age_rating || '_RESTRICTED'::text) <> ALL ('{EXTENDED,BASIC}'::text[])))
  • Rows Removed by Filter: 1
48. 2.660 2.660 ↓ 0.0 0 190

Index Scan using sentv_channel_channel_id_idx on sentv_channel c_1 (cost=0.28..0.39 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=190)

  • Index Cond: (channel_id = a_1.channel_id)
  • Filter: ((cardinality(region_dma_codes) > 0) AND (('SUPER'::text = ANY ('{EXTENDED,BASIC}'::text[])) OR ('{EXTENDED,BASIC}'::text[] && ent_keywords)) AND (((channel_id)::text || '_RESTRICTED'::text) <> ALL ('{EXTENDED,BASIC}'::text[])) AND (('NAT'::text = ANY (region_dma_codes)) OR ('NAT-EA'::text = ANY (region_dma_codes)) OR (('515'::text = ANY (region_dma_codes)) AND (('515'::text <> ALL (sub_dmas)) OR ('45202'::text = ANY (sub_dma_zips)) OR (sub_dmas = '{}'::text[]) OR (sub_dma_zips = '{}'::text[])))))
  • Rows Removed by Filter: 1
49. 0.000 0.000 ↓ 0.0 0 86

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=86)

  • Index Cond: ((rule_name)::text = ANY (a_1.bo_rules))
  • Filter: CASE WHEN (('515'::text = ANY (dmas)) OR ('45202'::text = ANY (zipcodes)) OR ('PS4'::text = ANY (device_types)) OR ((cardinality(media_blackouts) > 0) AND (('515_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
50. 0.216 0.216 ↓ 0.0 0 54

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.004..0.004 rows=0 loops=54)

  • Index Cond: ((profile_id = 397947) AND (program_id = p_1.program_id))
  • Filter: (NOT fully_watched)
  • Rows Removed by Filter: 0
Planning time : 17.070 ms
Execution time : 422.170 ms