explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7U5O

Settings
# exclusive inclusive rows x rows loops node
1. 0.810 15,632.906 ↓ 8,565.0 8,565 1

Nested Loop (cost=23,413.74..23,416.79 rows=1 width=4) (actual time=15,613.207..15,632.906 rows=8,565 loops=1)

  • Buffers: shared hit=289361
2. 10.200 15,614.966 ↓ 8,565.0 8,565 1

HashAggregate (cost=23,413.32..23,413.33 rows=1 width=4) (actual time=15,613.200..15,614.966 rows=8,565 loops=1)

  • Group Key: tveentity3_.entity_id
  • Buffers: shared hit=263326
3. 22.429 15,604.766 ↓ 19,739.0 19,739 1

Nested Loop Left Join (cost=16,862.29..23,413.32 rows=1 width=4) (actual time=150.885..15,604.766 rows=19,739 loops=1)

  • Filter: ((NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) OR (entitygenr5_.genre_id IS NULL) OR (genre6_.genre_category = 0))
  • Buffers: shared hit=263326
4. 7.441 15,463.903 ↓ 19,739.0 19,739 1

Nested Loop Left Join (cost=16,862.02..23,409.27 rows=1 width=8) (actual time=150.865..15,463.903 rows=19,739 loops=1)

  • Buffers: shared hit=99659
5. 6.948 15,430.767 ↓ 8,565.0 8,565 1

Nested Loop (cost=16,861.73..23,408.78 rows=1 width=4) (actual time=150.858..15,430.767 rows=8,565 loops=1)

  • Join Filter: (tveentity3_.entity_id = entityfilt4_.entity_id)
  • Buffers: shared hit=79958
6. 10,755.967 15,363.864 ↓ 8,565.0 8,565 1

Merge Join (cost=16,830.24..23,376.97 rows=1 width=8) (actual time=150.586..15,363.864 rows=8,565 loops=1)

  • Merge Cond: (tveentity3_.content_provider_id = licensingw7_.content_provider_id)
  • Join Filter: (tveentity3_.entity_id = licensingw7_.entity_id)
  • Rows Removed by Join Filter: 85159101
  • Buffers: shared hit=27261
7. 2.266 236.073 ↓ 364.8 8,754 1

Nested Loop (cost=15,108.26..24,691.83 rows=24 width=12) (actual time=139.401..236.073 rows=8,754 loops=1)

  • Join Filter: (tveentity3_.content_provider_id = contentpro22_.content_provider_id)
  • Rows Removed by Join Filter: 3569
  • Buffers: shared hit=24312
8. 0.036 0.036 ↑ 1.0 1 1

Index Scan using content_provider_pkey on content_provider contentpro22_ (cost=0.27..14.98 rows=1 width=4) (actual time=0.024..0.036 rows=1 loops=1)

  • Filter: ((code)::text = 'HBOGO'::text)
  • Rows Removed by Filter: 90
  • Buffers: shared hit=7
9. 125.518 233.771 ↓ 3.9 12,323 1

Seq Scan on entity tveentity3_ (cost=15,107.99..24,637.11 rows=3,179 width=8) (actual time=139.373..233.771 rows=12,323 loops=1)

  • Filter: (serial AND (audience <= 3) AND (NOT (hashed SubPlan 5)) AND (entity_state = 2))
  • Rows Removed by Filter: 206170
  • Buffers: shared hit=24305
10.          

SubPlan (for Seq Scan)

11. 50.206 108.253 ↑ 1.8 121,694 1

Merge Join (cost=185.15..14,572.83 rows=214,064 width=4) (actual time=1.394..108.253 rows=121,694 loops=1)

  • Merge Cond: (baseepg20_.opvr_entity_id = tveentity21_.entity_id)
  • Buffers: shared hit=18522
12. 26.415 26.415 ↑ 6.2 121,695 1

Index Only Scan using epg_opvr_entity_id_key on epg baseepg20_ (cost=0.42..16,751.78 rows=758,874 width=4) (actual time=0.013..26.415 rows=121,695 loops=1)

  • Heap Fetches: 15002
  • Buffers: shared hit=14543
13. 31.632 31.632 ↓ 1.0 218,493 1

Index Only Scan using entity_pkey on entity tveentity21_ (cost=0.42..5,426.67 rows=214,064 width=4) (actual time=0.007..31.632 rows=218,493 loops=1)

  • Heap Fetches: 2882
  • Buffers: shared hit=3979
14. 4,366.018 4,371.824 ↓ 7,192.4 85,171,944 1

Sort (cost=1,721.98..1,751.59 rows=11,842 width=8) (actual time=8.949..4,371.824 rows=85,171,944 loops=1)

  • Sort Key: licensingw7_.content_provider_id
  • Sort Method: quicksort Memory: 1602kB
  • Buffers: shared hit=2949
15. 5.806 5.806 ↓ 1.5 17,790 1

Index Only Scan using licensing_window_entity_id_key on licensing_window licensingw7_ (cost=0.41..920.77 rows=11,842 width=8) (actual time=0.084..5.806 rows=17,790 loops=1)

  • Index Cond: ((isp_id = 1) AND (start_date <= '2020-03-22 19:22:30.312+01'::timestamp with time zone) AND (end_date > '2020-03-22 19:22:30.312+01'::timestamp with time zone))
  • Heap Fetches: 3743
  • Buffers: shared hit=2949
16. 59.723 59.955 ↑ 3.0 1 8,565

Index Scan using idx_entity_filter_cache_entity_id on entity_filter_cache entityfilt4_ (cost=31.49..31.77 rows=3 width=4) (actual time=0.006..0.007 rows=1 loops=8,565)

  • Index Cond: (entity_id = licensingw7_.entity_id)
  • Filter: ((serial = 32092::bigint) AND ((hashed SubPlan 1) OR (hashed SubPlan 2)))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=52697
17.          

SubPlan (for Index Scan)

18. 0.009 0.200 ↑ 1.0 10 1

Nested Loop (cost=15.90..28.08 rows=10 width=4) (actual time=0.117..0.200 rows=10 loops=1)

  • Join Filter: (offersvodp9_.offer_id = offer10_.offer_id)
  • Buffers: shared hit=32
19. 0.009 0.181 ↑ 1.0 10 1

Hash Join (cost=15.75..26.22 rows=10 width=12) (actual time=0.110..0.181 rows=10 loops=1)

  • Hash Cond: (offersvodp9_.svod_package_id = svodpackag8_.svod_package_id)
  • Buffers: shared hit=21
20. 0.042 0.132 ↑ 2.0 10 1

Hash Join (cost=11.84..22.14 rows=20 width=12) (actual time=0.064..0.132 rows=10 loops=1)

  • Hash Cond: (offersvodp9_.offer_id = tariffoffe11_.offer_id)
  • Buffers: shared hit=19
21. 0.036 0.036 ↑ 1.0 370 1

Seq Scan on offer_svod_package offersvodp9_ (cost=0.00..8.70 rows=370 width=8) (actual time=0.003..0.036 rows=370 loops=1)

  • Buffers: shared hit=5
22. 0.001 0.054 ↑ 1.7 6 1

Hash (cost=11.71..11.71 rows=10 width=4) (actual time=0.054..0.054 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=14
23. 0.005 0.053 ↑ 1.7 6 1

Nested Loop (cost=1.27..11.71 rows=10 width=4) (actual time=0.044..0.053 rows=6 loops=1)

  • Join Filter: (tariff12_.tariff_id = tariffoffe11_.tariff_id)
  • Buffers: shared hit=14
24. 0.003 0.031 ↑ 1.0 1 1

Nested Loop (cost=1.00..7.07 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=1)

  • Buffers: shared hit=10
25. 0.004 0.024 ↑ 1.0 1 1

Nested Loop (cost=0.85..6.90 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)

  • Buffers: shared hit=8
26. 0.011 0.011 ↑ 1.0 1 1

Index Scan using subscription_stb_account_pkey on subscription_stb_account subscripti14_ (cost=0.42..3.44 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (subscription_stb_account_id = 483404)
  • Buffers: shared hit=4
27. 0.009 0.009 ↑ 1.0 1 1

Index Scan using subscription_pkey on subscription subscripti13_ (cost=0.43..3.45 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (subscription_id = subscripti14_.subscription_id)
  • Buffers: shared hit=4
28. 0.004 0.004 ↑ 1.0 1 1

Index Only Scan using tariff_pkey on tariff tariff12_ (cost=0.14..0.16 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (tariff_id = subscripti13_.tariff_id)
  • Heap Fetches: 0
  • Buffers: shared hit=2
29. 0.017 0.017 ↑ 1.0 6 1

Index Only Scan using tariff_offer_offer_id_key on tariff_offer tariffoffe11_ (cost=0.28..4.57 rows=6 width=8) (actual time=0.011..0.017 rows=6 loops=1)

  • Index Cond: (tariff_id = subscripti13_.tariff_id)
  • Heap Fetches: 0
  • Buffers: shared hit=4
30. 0.001 0.040 ↑ 1.1 13 1

Hash (cost=3.74..3.74 rows=14 width=8) (actual time=0.040..0.040 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=2
31. 0.019 0.039 ↑ 1.1 13 1

Hash Join (cost=1.64..3.74 rows=14 width=8) (actual time=0.024..0.039 rows=13 loops=1)

  • Hash Cond: (entityfilt15_.entity_filter_id = svodpackag8_.entity_filter_id)
  • Buffers: shared hit=2
32. 0.006 0.006 ↑ 1.0 64 1

Seq Scan on entity_filter entityfilt15_ (cost=0.00..1.64 rows=64 width=4) (actual time=0.002..0.006 rows=64 loops=1)

  • Buffers: shared hit=1
33. 0.004 0.014 ↑ 1.7 13 1

Hash (cost=1.36..1.36 rows=22 width=8) (actual time=0.014..0.014 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
34. 0.010 0.010 ↑ 1.0 22 1

Seq Scan on svod_package svodpackag8_ (cost=0.00..1.36 rows=22 width=8) (actual time=0.004..0.010 rows=22 loops=1)

  • Filter: (isp_id = 1)
  • Rows Removed by Filter: 7
  • Buffers: shared hit=1
35. 0.010 0.010 ↑ 1.0 1 10

Index Only Scan using offer_pkey on offer offer10_ (cost=0.14..0.17 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (offer_id = tariffoffe11_.offer_id)
  • Heap Fetches: 0
  • Buffers: shared hit=11
36. 0.004 0.032 ↓ 17.0 17 1

Nested Loop (cost=0.14..3.09 rows=1 width=4) (actual time=0.009..0.032 rows=17 loops=1)

  • Buffers: shared hit=19
37. 0.011 0.011 ↓ 17.0 17 1

Seq Scan on tvod_category tvodcatego16_ (cost=0.00..1.43 rows=1 width=4) (actual time=0.004..0.011 rows=17 loops=1)

  • Filter: ((delete_date IS NULL) AND (isp_id = 1))
  • Rows Removed by Filter: 17
  • Buffers: shared hit=1
38. 0.017 0.017 ↑ 1.0 1 17

Index Only Scan using entity_filter_pkey on entity_filter entityfilt17_ (cost=0.14..1.66 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=17)

  • Index Cond: (entity_filter_id = tvodcatego16_.entity_filter_id)
  • Heap Fetches: 0
  • Buffers: shared hit=18
39. 25.695 25.695 ↑ 1.0 2 8,565

Index Only Scan using entity_genre_entity_id_key on entity_genre entitygenr5_ (cost=0.29..0.47 rows=2 width=8) (actual time=0.003..0.003 rows=2 loops=8,565)

  • Index Cond: (entity_id = tveentity3_.entity_id)
  • Heap Fetches: 4840
  • Buffers: shared hit=19701
40. 39.478 39.478 ↑ 1.0 1 19,739

Index Scan using genre_pkey on genre genre6_ (cost=0.27..0.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=19,739)

  • Index Cond: (entitygenr5_.genre_id = genre_id)
  • Buffers: shared hit=59581
41.          

SubPlan (for Nested Loop Left Join)

42. 19.739 78.956 ↑ 2.0 1 19,739

Nested Loop (cost=0.71..6.78 rows=2 width=0) (actual time=0.004..0.004 rows=1 loops=19,739)

  • Buffers: shared hit=104086
43. 39.478 39.478 ↑ 1.0 1 19,739

Index Only Scan using entity_pkey on entity tveentity19_ (cost=0.42..3.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=19,739)

  • Index Cond: (entity_id = tveentity3_.entity_id)
  • Heap Fetches: 566
  • Buffers: shared hit=59785
44. 19.739 19.739 ↑ 2.0 1 19,739

Index Only Scan using entity_genre_entity_id_key on entity_genre entitygenr18_ (cost=0.29..3.32 rows=2 width=4) (actual time=0.001..0.001 rows=1 loops=19,739)

  • Index Cond: (entity_id = tveentity3_.entity_id)
  • Heap Fetches: 4785
  • Buffers: shared hit=44301
45. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=851.68..7,414.56 rows=35,715 width=4) (never executed)

  • Merge Cond: (entitygenr18__1.entity_id = tveentity19__1.entity_id)
46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using entity_genre_entity_id_key on entity_genre entitygenr18__1 (cost=0.29..1,008.52 rows=35,715 width=4) (never executed)

  • Heap Fetches: 0
47. 0.000 0.000 ↓ 0.0 0

Index Only Scan using entity_pkey on entity tveentity19__1 (cost=0.42..5,426.67 rows=214,064 width=4) (never executed)

  • Heap Fetches: 0
48. 17.130 17.130 ↑ 1.0 1 8,565

Index Only Scan using entity_pkey on entity tveentity0_ (cost=0.42..3.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8,565)

  • Index Cond: (entity_id = tveentity3_.entity_id)
  • Heap Fetches: 242
  • Buffers: shared hit=26035