explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OSPz

Settings
# exclusive inclusive rows x rows loops node
1. 1.638 385.371 ↑ 2.8 209 1

Sort (cost=490,052.23..490,053.68 rows=583 width=646) (actual time=385.365..385.371 rows=209 loops=1)

  • Sort Key: channelsou0_.verimatrix3_encrypted, channelsou0_.securemedia_encrypted, channelsou0_.irdeto_encrypted, channelsou0_.irdeto_encrypted_ott, channelsou0_.hls_aes_encrypted, channelsou0_.hls_fairplay_encrypted, channelsou0_.externally_encrypted, channelsou0_.widevine_encrypted, channelsou0_.priority, channelsou0_.uri
  • Sort Method: quicksort Memory: 54kB
2. 0.109 383.733 ↑ 2.8 209 1

Nested Loop Semi Join (cost=176.46..490,025.45 rows=583 width=646) (actual time=0.862..383.733 rows=209 loops=1)

3. 0.242 382.370 ↑ 6.3 209 1

Nested Loop (cost=175.88..489,122.40 rows=1,327 width=646) (actual time=0.816..382.370 rows=209 loops=1)

  • Join Filter: (((channelsou0_.verimatrix3_encrypted AND channel1_.verimatrix3_encrypted) OR (channelsou0_.securemedia_encrypted AND channel1_.securemedia_encoded) OR (channelsou0_.irdeto_encrypted AND channel1_.irdeto_encrypted) OR (channelsou0_.irdeto_encrypted_ott AND channel1_.irdeto_encrypted_ott) OR (channelsou0_.externally_encrypted AND channel1_.externally_encoded) OR (channelsou0_.hls_aes_encrypted AND channel1_.hls_aes_encrypted) OR
4. 0.159 2.375 ↓ 1.3 209 1

Merge Semi Join (cost=0.83..274.09 rows=163 width=16) (actual time=0.076..2.375 rows=209 loops=1)

  • Merge Cond: (channel1_.channel_id = channel2_.channel_id)
5. 0.539 0.539 ↓ 1.2 688 1

Index Scan using channel_pkey on channel channel1_ (cost=0.28..50.84 rows=568 width=12) (actual time=0.004..0.539 rows=688 loops=1)

  • Filter: (verimatrix3_encrypted OR securemedia_encoded OR irdeto_encrypted OR irdeto_encrypted_ott OR externally_encoded OR hls_aes_encrypted OR hls_fairplay_encrypted OR widevine_encrypted OR ((NOT verimatrix3_encrypted) AND (NOT securemedia_encoded) AND (NOT irdeto_encrypted) AND (NOT irdeto_encrypted_ott) AND (NOT externally_encoded) AND (NOT hls_aes_encrypted) AND (NOT hls_fairplay_encrypted) AND (NOT widevine_encrypted)))
6. 0.043 1.677 ↑ 1.0 209 1

Materialize (cost=0.55..219.67 rows=213 width=4) (actual time=0.069..1.677 rows=209 loops=1)

7. 0.816 1.634 ↑ 1.0 209 1

Merge Left Join (cost=0.55..219.14 rows=213 width=4) (actual time=0.067..1.634 rows=209 loops=1)

  • Merge Cond: (channel2_.channel_id = childchann3_.parent_channel_id)
  • Filter: (((channel2_.channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,
  • Rows Removed by Filter: 535
8. 0.627 0.627 ↓ 1.3 744 1

Index Scan using channel_pkey on channel channel2_ (cost=0.28..52.70 rows=558 width=8) (actual time=0.005..0.627 rows=744 loops=1)

  • Filter: ((isp_id = 5) OR (isp_id IS NULL))
9. 0.191 0.191 ↑ 2.0 373 1

Index Only Scan using idx_channel_parent_channel_id on channel childchann3_ (cost=0.28..30.81 rows=744 width=8) (actual time=0.057..0.191 rows=373 loops=1)

  • Heap Fetches: 145
10. 4.051 379.753 ↑ 13.0 1 209

Bitmap Heap Scan on channel_source channelsou0_ (cost=175.06..2,948.84 rows=13 width=646) (actual time=0.483..1.817 rows=1 loops=209)

  • Recheck Cond: ((channel_id = channel2_.channel_id) AND ((locality_id = 10) OR (locality_id IS NULL)))
  • Filter: ((NOT private_use) AND ((locality_id = 10) OR ((locality_id IS NULL) AND (NOT (SubPlan 5)))))
  • Rows Removed by Filter: 30
  • Heap Blocks: exact=1,491
11. 0.418 94.050 ↓ 0.0 0 209

BitmapAnd (cost=175.06..175.06 rows=30 width=0) (actual time=0.450..0.450 rows=0 loops=209)

12. 0.836 0.836 ↓ 1.1 37 209

Bitmap Index Scan on idx_channel_source_channel (cost=0.00..0.72 rows=35 width=0) (actual time=0.004..0.004 rows=37 loops=209)

  • Index Cond: (channel_id = channel2_.channel_id)
13. 0.000 92.796 ↓ 0.0 0 209

BitmapOr (cost=174.08..174.08 rows=11,019 width=0) (actual time=0.444..0.444 rows=0 loops=209)

14. 14.212 14.212 ↓ 1.0 1,383 209

Bitmap Index Scan on idx_channel_source_locality (cost=0.00..22.61 rows=1,377 width=0) (actual time=0.068..0.068 rows=1,383 loops=209)

  • Index Cond: (locality_id = 10)
15. 78.584 78.584 ↓ 1.0 9,652 209

Bitmap Index Scan on idx_channel_source_locality (cost=0.00..149.10 rows=9,642 width=0) (actual time=0.376..0.376 rows=9,652 loops=209)

  • Index Cond: (locality_id IS NULL)
16.          

SubPlan (for Bitmap Heap Scan)

17. 11.496 281.652 ↓ 4.0 4 5,748

HashAggregate (cost=92.39..92.40 rows=1 width=4) (actual time=0.048..0.049 rows=4 loops=5,748)

  • Group Key: channelsou8_.locality_id
18. 17.244 270.156 ↓ 1.7 5 5,748

Nested Loop (cost=3.45..92.38 rows=3 width=4) (actual time=0.020..0.047 rows=5 loops=5,748)

  • Join Filter: (((channelsou8_.verimatrix3_encrypted AND channel9_.verimatrix3_encrypted) OR (channelsou8_.securemedia_encrypted AND channel9_.securemedia_encoded) OR (channelsou8_.irdeto_encrypted AND channel9_.irdeto_encrypted) OR (channelsou8_.irdeto_encrypted_ott AND channel9_.irdeto_encrypted_ott) OR (channelsou8_.externally_encrypted AND channel9_.externally_encoded) OR (channelsou8_.hls_aes_encrypted AND channel9_.h
19. 0.000 40.236 ↑ 1.0 1 5,748

Nested Loop Semi Join (cost=0.83..10.42 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=5,748)

20. 11.496 11.496 ↑ 1.0 1 5,748

Index Scan using channel_pkey on channel channel9_ (cost=0.28..3.29 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5,748)

  • Index Cond: (channel_id = channelsou0_.channel_id)
  • Filter: (verimatrix3_encrypted OR securemedia_encoded OR irdeto_encrypted OR irdeto_encrypted_ott OR externally_encoded OR hls_aes_encrypted OR hls_fairplay_encrypted OR widevine_encrypted OR ((NOT verimatrix3_encrypted) AND (NOT securemedia_encoded) AND (NOT irdeto_encrypted) AND (NOT irdeto_encrypted_ott) AND (NOT externally_encoded) AND (NOT hls_aes_encrypted) AND (NOT hls_fairplay_encrypted) AND (NOT widevine_encrypted)))
21. 11.496 28.740 ↑ 1.0 1 5,748

Nested Loop Left Join (cost=0.55..7.12 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=5,748)

  • Join Filter: (channel10_.channel_id = childchann11_.parent_channel_id)
  • Filter: (((channel10_.channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,3502,3020,3419,3421,3423,3425,1767,2287,3427,3429,3431,3433,3435,3437,3439,3441,3325,3327,3329,3638,3620,3443,3445,3447,3449,1779,3451,3371,3373,3453,1811,1813,1815,3516,1817,2618,1791,3670,3508,3512,1807,1711,3514,1769,1805,1925,1747,3455,3504,1783,1785,3506,1827,1809,3263,3220,3226,3022,1841,1843,1931,3218,1819,1393,1429,3303,1861,1558,3301,3299,3520,3528,3526,3524,3518,1879,1883,2805,3480,3482,3484,3486,2807,2809,3461,1417,1420,3457,3494,2811,3462,1889,1893,3596,1717,3592,3584,3588,3600,3602,1689,1691,3606,3614,3610,3616,1671,1679,1683,1687,1505,1701,1703,1705,1707,1713,1719,1721,1727,1675,1677,1745,1695,3630,3632,3634,1735,1723,1725,3652,3654,1887}'::integer[])) AND (channel10_.parent_channel_id IS NULL)) OR (childchann11_.channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,3502,3020,3419,3421,3423,3425,1767,2287,3427,3429,3431,3433,3435,3437,3439,3441,3325,3327,3329,3638,3620,3443,3445,3447,3449,1779,3451,3371,3373,3453,1811,1813,1815,3516,1817,2618,1791,3670,3508,3512,1807,1711,3514,1769,1805,1925,1747,3455,3504,1783,1785,3506,1827,1809,3263,3220,3226,3022,1841,1843,1931,3218,1819,1393,1429,3303,1861,1558,3301,3299,3520,3528,3526,3524,3518,1879,1883,2805,3480,3482,3484,3486,2807,2809,3461,1417,1420,3457,3494,2811,3462,1889,1893,3596,1717,3592,3584,3588,3600,3602,1689,1691,3606,3614,3610,3616,1671,1679,1683,1687,1505,1701,1703,1705,1707,1713,1719,1721,1727,1675,1677,1745,1695,3630,3632,3634,1735,1723,1725,3652,3654,1887}'::integer[])))
22. 5.748 5.748 ↑ 1.0 1 5,748

Index Scan using channel_pkey on channel channel10_ (cost=0.28..3.29 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5,748)

  • Index Cond: (channel_id = channelsou0_.channel_id)
  • Filter: ((isp_id = 5) OR (isp_id IS NULL))
23. 11.496 11.496 ↑ 1.0 1 5,748

Index Scan using channel_parent_channel_id_key on channel childchann11_ (cost=0.28..3.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,748)

  • Index Cond: (parent_channel_id = channelsou0_.channel_id)
24. 11.064 212.676 ↓ 1.2 5 5,748

Nested Loop Semi Join (cost=2.62..66.51 rows=4 width=20) (actual time=0.012..0.037 rows=5 loops=5,748)

25. 91.968 120.708 ↑ 1.6 5 5,748

Bitmap Heap Scan on channel_source channelsou8_ (cost=2.05..47.76 rows=8 width=24) (actual time=0.008..0.021 rows=5 loops=5,748)

  • Recheck Cond: (channel_id = channelsou0_.channel_id)
  • Filter: ((NOT private_use) AND (locality_id IS NOT NULL))
  • Rows Removed by Filter: 72
  • Heap Blocks: exact=91,051
26. 28.740 28.740 ↓ 2.2 77 5,748

Bitmap Index Scan on idx_channel_source_channel (cost=0.00..2.05 rows=35 width=0) (actual time=0.005..0.005 rows=77 loops=5,748)

  • Index Cond: (channel_id = channelsou0_.channel_id)
27. 0.000 80.904 ↑ 1.0 1 26,968

Nested Loop (cost=0.57..2.33 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=26,968)

28. 26.968 26.968 ↑ 1.0 1 26,968

Index Only Scan using channel_source_pkey on channel_source channelsou14_ (cost=0.29..1.99 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=26,968)

  • Index Cond: (channel_source_id = channelsou8_.channel_source_id)
  • Heap Fetches: 1,576
29. 53.936 53.936 ↑ 1.0 1 26,968

Index Only Scan using channel_source_device_type_channel_source_id_key on channel_source_device_type channelsou15_ (cost=0.29..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=26,968)

  • Index Cond: ((channel_source_id = channelsou14_.channel_source_id) AND (device_type = 'STB'::text))
  • Heap Fetches: 19,671
30.          

SubPlan (for Nested Loop)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_channel_parent_channel_id on channel channel12_ (cost=0.28..3.56 rows=1 width=4) (never executed)

  • Index Cond: ((parent_channel_id IS NULL) AND (channel_id = channel9_.channel_id))
  • Filter: (multibitrate_capable AND ((isp_id = 5) OR (isp_id IS NULL)) AND (channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,3502,3020,3419,3421,3423,3425,1767,2287,3427,3429,3431,3433,3435,3437,3439,3441,3325,3327,3329,3638,3620,3443,3445,3447,3449,1779,3451,3371,3373,3453,1811,1813,1815,3516,1817,2618,1791,3670,3508,3512,1807,1711,3514,1769,1805,1925,1747,3455,3504,1783,1785,3506,1827,1809,3263,3220,3226,3022,1841,1843,1931,3218,1819,1393,1429,3303,1861,1558,3301,3299,3520,3528,3526,3524,3518,1879,1883,2805,3480,3482,3484,3486,2807,2809,3461,1417,1420,3457,3494,2811,3462,1889,1893,3596,1717,3592,3584,3588,3600,3602,1689,1691,3606,3614,3610,3616,1671,1679,1683,1687,1505,1701,1703,1705,1707,1713,1719,1721,1727,1675,1677,1745,1695,3630,3632,3634,1735,1723,1725,3652,3654,1887}'::integer[])))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using channel_parent_channel_id_key on channel channel13_ (cost=0.28..3.56 rows=1 width=4) (never executed)

  • Index Cond: ((parent_channel_id IS NOT NULL) AND (parent_channel_id = channel9_.channel_id))
  • Filter: (multibitrate_capable AND ((isp_id = 5) OR (isp_id IS NULL)) AND (channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,3502,3020,3419,3421,3423,3425,1767,2287,3427,3429,3431,3433,3435,3437,3439,3441,3325,3327,3329,3638,3620,3443,3445,3447,3449,1779,3451,3371,3373,3453,1811,1813,1815,3516,1817,2618,1791,3670,3508,3512,1807,1711,3514,1769,1805,1925,1747,3455,3504,1783,1785,3506,1827,1809,3263,3220,3226,3022,1841,1843,1931,3218,1819,1393,1429,3303,1861,1558,3301,3299,3520,3528,3526,3524,3518,1879,1883,2805,3480,3482,3484,3486,2807,2809,3461,1417,1420,3457,3494,2811,3462,1889,1893,3596,1717,3592,3584,3588,3600,3602,1689,1691,3606,3614,3610,3616,1671,1679,1683,1687,1505,1701,1703,1705,1707,1713,1719,1721,1727,1675,1677,1745,1695,3630,3632,3634,1735,1723,1725,3652,3654,1887}'::integer[])))
33.          

SubPlan (for Nested Loop)

34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_channel_parent_channel_id on channel channel4_ (cost=0.28..3.56 rows=1 width=4) (never executed)

  • Index Cond: ((parent_channel_id IS NULL) AND (channel_id = channel1_.channel_id))
  • Filter: (multibitrate_capable AND ((isp_id = 5) OR (isp_id IS NULL)) AND (channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,3502,3020,3419,3421,3423,3425,1767,2287,3427,3429,3431,3433,3435,3437,3439,3441,3325,3327,3329,3638,3620,3443,3445,3447,3449,1779,3451,3371,3373,3453,1811,1813,1815,3516,1817,2618,1791,3670,3508,3512,1807,1711,3514,1769,1805,1925,1747,3455,3504,1783,1785,3506,1827,1809,3263,3220,3226,3022,1841,1843,1931,3218,1819,1393,1429,3303,1861,1558,3301,3299,3520,3528,3526,3524,3518,1879,1883,2805,3480,3482,3484,3486,2807,2809,3461,1417,1420,3457,3494,2811,3462,1889,1893,3596,1717,3592,3584,3588,3600,3602,1689,1691,3606,3614,3610,3616,1671,1679,1683,1687,1505,1701,1703,1705,1707,1713,1719,1721,1727,1675,1677,1745,1695,3630,3632,3634,1735,1723,1725,3652,3654,1887}'::integer[])))
35. 0.000 0.000 ↓ 0.0 0

Index Scan using channel_parent_channel_id_key on channel channel5_ (cost=0.28..3.56 rows=1 width=4) (never executed)

  • Index Cond: ((parent_channel_id IS NOT NULL) AND (parent_channel_id = channel1_.channel_id))
  • Filter: (multibitrate_capable AND ((isp_id = 5) OR (isp_id IS NULL)) AND (channel_id = ANY ('{1359,1366,1396,1387,1411,1372,1983,1426,1390,3646,3385,3375,3381,3379,3383,3500,3413,3415,3417,3377,3492,1801,3658,3297,1743,3626,3640,3628,3293,1709,3642,1663,1789,3285,3287,3276,3269,3295,3274,3296,3279,3281,3283,1681,2592,1933,1935,1937,1939,1943,1945,1949,1951,1953,1955,3323,1423,1475,3666,3464,3387,3580,3389,3391,3393,3466,3395,3397,3399,3401,3403,3405,3407,3259,3650,3409,3411,3502,3020,3419,3421,3423,3425,1767,2287,3427,3429,3431,3433,3435,3437,3439,3441,3325,3327,3329,3638,3620,3443,3445,3447,3449,1779,3451,3371,3373,3453,1811,1813,1815,3516,1817,2618,1791,3670,3508,3512,1807,1711,3514,1769,1805,1925,1747,3455,3504,1783,1785,3506,1827,1809,3263,3220,3226,3022,1841,1843,1931,3218,1819,1393,1429,3303,1861,1558,3301,3299,3520,3528,3526,3524,3518,1879,1883,2805,3480,3482,3484,3486,2807,2809,3461,1417,1420,3457,3494,2811,3462,1889,1893,3596,1717,3592,3584,3588,3600,3602,1689,1691,3606,3614,3610,3616,1671,1679,1683,1687,1505,1701,1703,1705,1707,1713,1719,1721,1727,1675,1677,1745,1695,3630,3632,3634,1735,1723,1725,3652,3654,1887}'::integer[])))
36. 0.209 1.254 ↑ 1.0 1 209

Nested Loop (cost=0.57..0.67 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=209)

37. 0.418 0.418 ↑ 1.0 1 209

Index Only Scan using channel_source_pkey on channel_source channelsou6_ (cost=0.29..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=209)

  • Index Cond: (channel_source_id = channelsou0_.channel_source_id)
  • Heap Fetches: 10
38. 0.627 0.627 ↑ 1.0 1 209

Index Only Scan using channel_source_device_type_channel_source_id_key on channel_source_device_type channelsou7_ (cost=0.29..0.33 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=209)

  • Index Cond: ((channel_source_id = channelsou6_.channel_source_id) AND (device_type = 'STB'::text))
  • Heap Fetches: 159
Planning time : 5.589 ms
Execution time : 385.744 ms