explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6d5c

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 11.048 ↓ 1.8 11 1

Append (cost=4,339.25..4,359.57 rows=6 width=651) (actual time=10.918..11.048 rows=11 loops=1)

2.          

CTE allowed_channels

3. 0.968 10.046 ↓ 1.6 1,014 1

Nested Loop Anti Join (cost=151.38..4,253.96 rows=637 width=16) (actual time=1.273..10.046 rows=1,014 loops=1)

  • Join Filter: ("channel_device_restrictions"."channel" = "c_1"."id")
  • Rows Removed by Join Filter: 5080
4. 0.895 9.078 ↓ 1.6 1,019 1

Nested Loop (cost=151.38..4,192.60 rows=640 width=16) (actual time=1.195..9.078 rows=1,019 loops=1)

5. 0.437 1.355 ↓ 1.5 1,138 1

HashAggregate (cost=151.10..158.61 rows=751 width=16) (actual time=1.173..1.355 rows=1,138 loops=1)

  • Group Key: "channel_subscription_packs"."channel
6. 0.918 0.918 ↑ 1.0 1,138 1

Seq Scan on "channel_subscription_packs" (cost=0.00..148.26 rows=1,138 width=16) (actual time=0.009..0.918 rows=1,138 loops=1)

  • Filter: ("subscription_pack" = ANY ('{c1e7a931-a68b-4e0f-9f09-e575c4ff9ea8}'::"uuid"[]))
  • Rows Removed by Filter: 6796
7. 2.148 6.828 ↑ 1.0 1 1,138

Index Only Scan using "channels_pkey" on "channels" "c_1" (cost=0.28..5.36 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1,138)

  • Index Cond: ("id" = "channel_subscription_packs"."channel")
  • Filter: (((NOT (alternatives: SubPlan 1 or hashed SubPlan 2)) OR (alternatives: SubPlan 3 or hashed SubPlan 4)) AND ((NOT (alternatives: SubPlan 5 or hashed SubPlan 6)) OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 1138
8.          

SubPlan (forIndex Only Scan)

9. 3.414 3.414 ↓ 0.0 0 1,138

Seq Scan on "channel_allowed_timezones" (cost=0.00..1.35 rows=4 width=0) (actual time=0.003..0.003 rows=0 loops=1,138)

  • Filter: ("channel" = "c_1"."id")
  • Rows Removed by Filter: 28
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on "channel_allowed_timezones" "channel_allowed_timezones_1" (cost=0.00..1.28 rows=28 width=16) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on "channel_allowed_timezones" "channel_allowed_timezones_2" (cost=0.00..1.42 rows=1 width=0) (never executed)

  • Filter: (("channel" = "c_1"."id") AND ("timezone" = '-0700'::"text"))
12. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on "channel_allowed_timezones" "channel_allowed_timezones_3" (cost=0.00..1.35 rows=4 width=16) (actual time=0.002..0.005 rows=4 loops=1)

  • Filter: ("timezone" = '-0700'::"text")
  • Rows Removed by Filter: 24
13. 1.134 1.134 ↓ 0.0 0 1,134

Index Only Scan using "channel_allowed_dmas_pkey" on "channel_allowed_dmas" (cost=0.28..15.29 rows=22 width=0) (actual time=0.001..0.001 rows=0 loops=1,134)

  • Index Cond: ("channel" = "c_1"."id")
  • Heap Fetches: 127
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on "channel_allowed_dmas" "channel_allowed_dmas_1" (cost=0.00..46.13 rows=2,813 width=16) (never executed)

15. 0.127 0.127 ↓ 0.0 0 127

Index Only Scan using "channel_allowed_dmas_pkey" on "channel_allowed_dmas" "channel_allowed_dmas_2" (cost=0.28..2.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=127)

  • Index Cond: (("channel" = "c_1"."id") AND ("dma" = 770))
  • Heap Fetches: 12
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on "channel_allowed_dmas" "channel_allowed_dmas_3" (cost=0.00..53.16 rows=12 width=16) (never executed)

  • Filter: ("dma" = 770)
17. 0.000 0.000 ↑ 1.0 5 1,019

Materialize (cost=0.00..13.38 rows=5 width=16) (actual time=0.000..0.000 rows=5 loops=1,019)

18. 0.074 0.074 ↑ 1.0 5 1

Seq Scan on "channel_device_restrictions" (cost=0.00..13.35 rows=5 width=16) (actual time=0.017..0.074 rows=5 loops=1)

  • Filter: ("device" = 'browser'::"text")
  • Rows Removed by Filter: 663
19.          

CTE matches

20. 0.002 10.855 ↓ 5.5 11 1

Limit (cost=83.74..83.75 rows=2 width=80) (actual time=10.853..10.855 rows=11 loops=1)

21. 0.014 10.853 ↓ 5.5 11 1

Sort (cost=83.74..83.75 rows=2 width=80) (actual time=10.852..10.853 rows=11 loops=1)

  • Sort Key: "m_3"."asset" DESC
  • Sort Method: quicksort Memory: 26kB
22. 0.005 10.839 ↓ 5.5 11 1

Nested Loop Left Join (cost=50.32..83.73 rows=2 width=80) (actual time=0.114..10.839 rows=11 loops=1)

23. 0.002 10.790 ↓ 5.5 11 1

Nested Loop Left Join (cost=35.57..45.11 rows=2 width=84) (actual time=0.106..10.790 rows=11 loops=1)

24. 0.003 0.138 ↓ 3.0 6 1

Nested Loop (cost=14.44..23.78 rows=2 width=68) (actual time=0.094..0.138 rows=6 loops=1)

25. 0.009 0.085 ↓ 2.0 2 1

GroupAggregate (cost=14.44..14.47 rows=1 width=48) (actual time=0.083..0.085 rows=2 loops=1)

  • Group Key: "m_3"."asset
26. 0.007 0.076 ↓ 2.0 2 1

Sort (cost=14.44..14.45 rows=1 width=28) (actual time=0.076..0.076 rows=2 loops=1)

  • Sort Key: "m_3"."asset
  • Sort Method: quicksort Memory: 25kB
27. 0.003 0.069 ↓ 2.0 2 1

Nested Loop (cost=11.15..14.43 rows=1 width=28) (actual time=0.065..0.069 rows=2 loops=1)

28. 0.006 0.058 ↓ 2.0 2 1

Bitmap Heap Scan on "asset_search_matches2" "m_3" (cost=11.00..12.26 rows=1 width=20) (actual time=0.056..0.058 rows=2 loops=1)

  • Recheck Cond: ("to_tsvector"('"english_nostop"'::"regconfig", "value") @@ '''red'' & ''oct'':*'::"tsquery")
  • Heap Blocks: exact=2
29. 0.052 0.052 ↓ 2.0 2 1

Bitmap Index Scan on "asset_search_matches2_to_tsvector_idx" (cost=0.00..11.00 rows=1 width=0) (actual time=0.052..0.052 rows=2 loops=1)

  • Index Cond: ("to_tsvector"('"english_nostop"'::"regconfig", "value") @@ '''red'' & ''oct'':*'::"tsquery")
30. 0.008 0.008 ↑ 1.0 1 2

Index Scan using "searchable_field_weights_pkey" on "searchable_field_weights" "w" (cost=0.15..2.17 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: ("searchable_field" = "m_3"."searchable_field")
31. 0.002 0.050 ↑ 2.0 3 2

Append (cost=0.00..9.24 rows=6 width=36) (actual time=0.011..0.025 rows=3 loops=2)

32. 0.002 0.002 ↓ 0.0 0 2

Seq Scan on "entitlements" "e" (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: ("m_3"."asset" = "asset")
33. 0.012 0.012 ↓ 0.0 0 2

Index Scan using "tvod_asset_idx" on "tvod" "e_1" (cost=0.29..3.32 rows=2 width=36) (actual time=0.005..0.006 rows=0 loops=2)

  • Index Cond: ("asset" = "m_3"."asset")
34. 0.024 0.024 ↑ 1.0 2 2

Index Scan using "svod_asset_idx" on "svod" "e_2" (cost=0.42..3.45 rows=2 width=36) (actual time=0.007..0.012 rows=2 loops=2)

  • Index Cond: ("asset" = "m_3"."asset")
35. 0.010 0.010 ↓ 0.0 0 2

Index Scan using "linear_asset_idx1" on "linear" "e_3" (cost=0.42..2.44 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: ("asset" = "m_3"."asset")
36. 0.013 10.650 ↓ 2.0 2 6

Hash Semi Join (cost=21.12..21.22 rows=1 width=32) (actual time=1.774..1.775 rows=2 loops=6)

  • Hash Cond: ("s_1"."channel" = "allowed_channels_1"."id")
37. 0.054 0.054 ↑ 1.0 2 6

Index Only Scan using "svod_channels_pkey" on "svod_channels" "s_1" (cost=0.42..0.47 rows=2 width=32) (actual time=0.008..0.009 rows=2 loops=6)

  • Index Cond: ("svod" = "e"."id")
  • Heap Fetches: 10
38. 0.175 10.583 ↓ 1.6 1,014 1

Hash (cost=12.74..12.74 rows=637 width=16) (actual time=10.583..10.583 rows=1,014 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
39. 10.408 10.408 ↓ 1.6 1,014 1

CTE Scan on "allowed_channels" "allowed_channels_1" (cost=0.00..12.74 rows=637 width=16) (actual time=1.273..10.408 rows=1,014 loops=1)

40. 0.000 0.044 ↓ 0.0 0 11

Nested Loop (cost=14.75..19.29 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=11)

  • Join Filter: ("l_1"."channel" = "allowed_channels"."id")
41. 0.044 0.044 ↓ 0.0 0 11

Index Scan using "linear_pkey" on "linear" "l_1" (cost=0.42..0.46 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: ("id" = "e"."id")
42. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=14.33..16.33 rows=200 width=16) (never executed)

  • Group Key: "allowed_channels"."id
43. 0.000 0.000 ↓ 0.0 0

CTE Scan on "allowed_channels" (cost=0.00..12.74 rows=637 width=16) (never executed)

44. 0.001 10.880 ↓ 0.0 0 1

Nested Loop (cost=1.55..7.66 rows=2 width=638) (actual time=10.880..10.880 rows=0 loops=1)

45. 0.000 10.879 ↓ 0.0 0 1

Nested Loop (cost=1.27..7.05 rows=2 width=510) (actual time=10.879..10.879 rows=0 loops=1)

46. 0.000 10.879 ↓ 0.0 0 1

Nested Loop (cost=0.84..5.93 rows=2 width=522) (actual time=10.879..10.879 rows=0 loops=1)

47. 0.000 10.879 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.92 rows=2 width=289) (actual time=10.879..10.879 rows=0 loops=1)

48. 10.859 10.859 ↓ 5.5 11 1

CTE Scan on "matches" "m" (cost=0.00..0.04 rows=2 width=48) (actual time=10.854..10.859 rows=11 loops=1)

49. 0.022 0.022 ↓ 0.0 0 11

Index Scan using "linear_pkey" on "linear" "l" (cost=0.42..2.44 rows=1 width=257) (actual time=0.002..0.002 rows=0 loops=11)

  • Index Cond: ("id" = "m"."entitlement")
50. 0.000 0.000 ↓ 0.0 0

Index Scan using "assets_pkey" on "assets" "a" (cost=0.42..0.51 rows=1 width=249) (never executed)

  • Index Cond: ("id" = "l"."asset")
51. 0.000 0.000 ↓ 0.0 0

Index Scan using "programs_pkey" on "programs" "p" (cost=0.43..0.56 rows=1 width=20) (never executed)

  • Index Cond: ("id" = "a"."program")
52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "channels_pkey" on "channels" "c" (cost=0.28..0.30 rows=1 width=16) (never executed)

  • Index Cond: ("id" = "l"."channel")
  • Heap Fetches: 0
53. 0.006 0.125 ↓ 5.0 10 1

Nested Loop Left Join (cost=1.27..7.10 rows=2 width=670) (actual time=0.038..0.125 rows=10 loops=1)

54. 0.002 0.089 ↓ 5.0 10 1

Nested Loop (cost=0.84..5.96 rows=2 width=553) (actual time=0.024..0.089 rows=10 loops=1)

55. 0.011 0.057 ↓ 5.0 10 1

Nested Loop (cost=0.42..4.92 rows=2 width=320) (actual time=0.013..0.057 rows=10 loops=1)

56. 0.002 0.002 ↓ 5.5 11 1

CTE Scan on "matches" "m_1" (cost=0.00..0.04 rows=2 width=64) (actual time=0.000..0.002 rows=11 loops=1)

57. 0.044 0.044 ↑ 1.0 1 11

Index Scan using "svod_pkey" on "svod" "s" (cost=0.42..2.44 rows=1 width=272) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: ("id" = "m_1"."entitlement")
58. 0.030 0.030 ↑ 1.0 1 10

Index Scan using "assets_pkey" on "assets" "a_1" (cost=0.42..0.52 rows=1 width=249) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: ("id" = "s"."asset")
59. 0.030 0.030 ↑ 1.0 1 10

Index Scan using "programs_pkey" on "programs" "p_1" (cost=0.43..0.56 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: ("id" = "a_1"."program")
60. 0.009 0.042 ↑ 2.0 1 1

Nested Loop (cost=0.71..7.01 rows=2 width=644) (actual time=0.042..0.042 rows=1 loops=1)

61. 0.000 0.033 ↑ 2.0 1 1

Nested Loop (cost=0.29..4.65 rows=2 width=310) (actual time=0.033..0.033 rows=1 loops=1)

62. 0.001 0.001 ↓ 5.5 11 1

CTE Scan on "matches" "m_2" (cost=0.00..0.04 rows=2 width=48) (actual time=0.000..0.001 rows=11 loops=1)

63. 0.033 0.033 ↓ 0.0 0 11

Index Scan using "tvod_pkey" on "tvod" "t" (cost=0.29..2.30 rows=1 width=278) (actual time=0.003..0.003 rows=0 loops=11)

  • Index Cond: ("id" = "m_2"."entitlement")