explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ArC

Settings
# exclusive inclusive rows x rows loops node
1. 0.117 1,173.302 ↑ 1.1 238 1

Nested Loop (cost=138,392.84..138,876.41 rows=269 width=657) (actual time=1,171.756..1,173.302 rows=238 loops=1)

2.          

CTE allowed_channels

3. 0.542 0.542 ↑ 2.2 449 1

Seq Scan on "allowed_channels" (cost=0.00..52.76 rows=1,001 width=16) (actual time=0.013..0.542 rows=449 loops=1)

  • Filter: (("subscription_packs" && '{c1e7a931-a68b-4e0f-9f09-e575c4ff9ea8}'::"uuid"[]) AND (("allowed_timezones" && '{-0700}'::"text"[]) OR ("allowed_timezones" IS NULL)) AND (("allowed_dmas" && '{770}'::integer[]) OR ("allowed_dmas" IS NULL)) AND (NOT ("restricted_devices" && '{browser}'::"text"[])))
  • Rows Removed by Filter: 689
4.          

CTE matches

5. 0.593 1,172.331 ↑ 1.0 200 1

Limit (cost=138,339.15..138,339.65 rows=200 width=64) (actual time=1,171.728..1,172.331 rows=200 loops=1)

6. 44.008 1,171.738 ↑ 1,903.5 200 1

Sort (cost=138,339.15..139,290.89 rows=380,693 width=64) (actual time=1,171.727..1,171.738 rows=200 loops=1)

  • Sort Key: "m_1"."weight" DESC
  • Sort Method: top-N heapsort Memory: 49kB
7. 123.322 1,127.730 ↑ 1.6 237,838 1

Hash Join (cost=92,739.75..121,885.88 rows=380,693 width=64) (actual time=637.524..1,127.730 rows=237,838 loops=1)

  • Hash Cond: ("e"."asset" = "m_1"."asset")
8. 105.791 579.740 ↓ 1.2 450,528 1

Hash Left Join (cost=18,467.71..44,724.47 rows=375,571 width=68) (actual time=212.582..579.740 rows=450,528 loops=1)

  • Hash Cond: ("e"."id" = "l"."id")
9. 128.281 409.643 ↓ 1.2 450,528 1

Hash Left Join (cost=8,095.56..31,936.59 rows=375,571 width=52) (actual time=148.148..409.643 rows=450,528 loops=1)

  • Hash Cond: ("e"."id" = "s"."svod")
10. 22.660 133.361 ↑ 1.0 375,570 1

Append (cost=0.00..20,661.56 rows=375,571 width=36) (actual time=0.008..133.361 rows=375,570 loops=1)

11. 0.002 0.002 ↓ 0.0 0 1

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

12. 4.100 4.100 ↑ 1.0 13,264 1

Seq Scan on "tvod" "e_1" (cost=0.00..709.64 rows=13,264 width=36) (actual time=0.006..4.100 rows=13,264 loops=1)

13. 45.856 45.856 ↑ 1.0 160,838 1

Seq Scan on "svod" "e_2" (cost=0.00..8,310.38 rows=160,838 width=36) (actual time=0.008..45.856 rows=160,838 loops=1)

14. 60.743 60.743 ↑ 1.0 201,468 1

Seq Scan on "linear" "e_3" (cost=0.00..9,763.68 rows=201,468 width=36) (actual time=0.009..60.743 rows=201,468 loops=1)

15. 42.908 148.001 ↓ 1.5 193,236 1

Hash (cost=6,468.53..6,468.53 rows=130,162 width=32) (actual time=148.001..148.001 rows=193,236 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 14126kB
16. 27.893 105.093 ↓ 1.5 193,236 1

Nested Loop (cost=22.94..6,468.53 rows=130,162 width=32) (actual time=0.747..105.093 rows=193,236 loops=1)

17. 0.242 0.870 ↓ 2.2 449 1

HashAggregate (cost=22.52..24.52 rows=200 width=16) (actual time=0.731..0.870 rows=449 loops=1)

  • Group Key: "allowed_channels_2"."channel
18. 0.628 0.628 ↑ 2.2 449 1

CTE Scan on "allowed_channels" "allowed_channels_2" (cost=0.00..20.02 rows=1,001 width=16) (actual time=0.014..0.628 rows=449 loops=1)

19. 76.330 76.330 ↑ 1.7 430 449

Index Scan using "svod_channels_channel_idx" on "svod_channels" "s" (cost=0.42..24.95 rows=727 width=32) (actual time=0.005..0.170 rows=430 loops=449)

  • Index Cond: ("channel" = "allowed_channels_2"."channel")
20. 12.433 64.306 ↑ 1.7 60,425 1

Hash (cost=9,112.98..9,112.98 rows=100,734 width=32) (actual time=64.306..64.306 rows=60,425 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4801kB
21. 20.182 51.873 ↑ 1.7 60,425 1

Nested Loop (cost=22.94..9,112.98 rows=100,734 width=32) (actual time=0.165..51.873 rows=60,425 loops=1)

22. 0.226 0.261 ↓ 2.2 449 1

HashAggregate (cost=22.52..24.52 rows=200 width=16) (actual time=0.139..0.261 rows=449 loops=1)

  • Group Key: "allowed_channels_1"."channel
23. 0.035 0.035 ↑ 2.2 449 1

CTE Scan on "allowed_channels" "allowed_channels_1" (cost=0.00..20.02 rows=1,001 width=16) (actual time=0.002..0.035 rows=449 loops=1)

24. 31.430 31.430 ↑ 1.4 135 449

Index Scan using "linear_channel_idx" on "linear" "l" (cost=0.42..43.49 rows=195 width=32) (actual time=0.005..0.070 rows=135 loops=449)

  • Index Cond: ("channel" = "allowed_channels_1"."channel")
25. 26.604 424.668 ↑ 1.1 134,307 1

Hash (cost=72,352.50..72,352.50 rows=153,563 width=48) (actual time=424.668..424.668 rows=134,307 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9006kB
26. 11.999 398.064 ↑ 1.1 134,307 1

Subquery Scan on m_1 (cost=68,897.34..72,352.50 rows=153,563 width=48) (actual time=318.609..398.064 rows=134,307 loops=1)

27. 188.085 386.065 ↑ 1.1 134,307 1

Finalize HashAggregate (cost=68,897.34..70,816.87 rows=153,563 width=48) (actual time=318.607..386.065 rows=134,307 loops=1)

  • Group Key: "m_2"."asset
28. 38.585 197.980 ↑ 1.2 175,948 1

Gather (cost=45,403.64..67,348.30 rows=206,538 width=48) (actual time=128.498..197.980 rows=175,948 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 76.226 159.395 ↑ 1.8 58,649 3

Partial HashAggregate (cost=44,403.64..45,694.50 rows=103,269 width=48) (actual time=120.261..159.395 rows=58,649 loops=3)

  • Group Key: "m_2"."asset
30. 16.184 83.169 ↑ 1.3 80,375 3

Hash Join (cost=2,070.43..43,887.29 rows=103,269 width=28) (actual time=32.103..83.169 rows=80,375 loops=3)

  • Hash Cond: ("m_2"."searchable_field" = "w"."searchable_field")
31. 29.618 66.940 ↑ 1.3 80,375 3

Parallel Bitmap Heap Scan on "asset_search_matches" "m_2" (cost=2,018.81..43,563.97 rows=103,269 width=20) (actual time=32.010..66.940 rows=80,375 loops=3)

  • Recheck Cond: ("to_tsvector"('"english_nostop"'::"regconfig", "value") @@ '''a'':*'::"tsquery")
  • Heap Blocks: exact=5500
32. 37.322 37.322 ↑ 1.0 241,124 1

Bitmap Index Scan on "asset_search_matches2_to_tsvector_idx" (cost=0.00..1,956.85 rows=247,846 width=0) (actual time=37.322..37.322 rows=241,124 loops=1)

  • Index Cond: ("to_tsvector"('"english_nostop"'::"regconfig", "value") @@ '''a'':*'::"tsquery")
33. 0.007 0.045 ↑ 154.2 12 3

Hash (cost=28.50..28.50 rows=1,850 width=16) (actual time=0.044..0.045 rows=12 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
34. 0.038 0.038 ↑ 154.2 12 3

Seq Scan on "searchable_field_weights" "w" (cost=0.00..28.50 rows=1,850 width=16) (actual time=0.035..0.038 rows=12 loops=3)

35. 1,172.385 1,172.385 ↑ 1.0 200 1

CTE Scan on "matches" "m" (cost=0.00..4.00 rows=200 width=48) (actual time=1,171.731..1,172.385 rows=200 loops=1)

36. 0.800 0.800 ↑ 1.0 1 200

Index Scan using "tiles_id_idx" on "tiles" "t" (cost=0.42..2.39 rows=1 width=493) (actual time=0.004..0.004 rows=1 loops=200)

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