Improve the query structure and performance of /api/channel

[bjester]

Observed behavior

We had to revert changes that added better ordering of channels on the 'My Channel's view, because the sort by modified caused the request to timeout

Expected behavior

The API endpoint should return results ordered descending by the last modified date, and should not timeout.

User-facing consequences

The user is unable to load the list of their channels

Steps to reproduce the issue

The request timeouts are likely to due to issues at scale, and the dependent on the quantity of the user's channels and quantity of resources in each channel.

[bjester]

The query plan needing optimization:

                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=14.54..82200.92 rows=2 width=936) (actual time=50930.464..51047.724 rows=29 loops=1)
   ->  Nested Loop  (cost=13.98..30.33 rows=2 width=689) (actual time=0.173..0.541 rows=29 loops=1)
         ->  Unique  (cost=13.69..13.70 rows=2 width=32) (actual time=0.158..0.211 rows=29 loops=1)
               ->  Sort  (cost=13.69..13.70 rows=2 width=32) (actual time=0.157..0.184 rows=29 loops=1)
                     Sort Key: ((u1.channel_id)::text)
                     Sort Method: quicksort  Memory: 29kB
                     ->  Nested Loop  (cost=0.57..13.68 rows=2 width=32) (actual time=0.065..0.084 rows=29 loops=1)
                           ->  Index Only Scan using contentcuration_user_pkey on contentcuration_user u0  (cost=0.29..4.30 rows=1 width=4) (actual time=0.053..0.054 rows=1 loops=1)
                                 Index Cond: (id = 28085)
                                 Heap Fetches: 1
                           ->  Index Scan using contentcuration_channel_editors_e8701ad4 on contentcuration_channel_editors u1  (cost=0.29..9.36 rows=2 width=36) (actual time=0.008..0.024 rows=29 loops=1)
                                 Index Cond: (user_id = 28085)
         ->  Index Scan using contentcuration_channel_id_be7411c1_like on contentcuration_channel  (cost=0.29..8.30 rows=1 width=689) (actual time=0.009..0.009 rows=1 loops=29)
               Index Cond: ((id)::text = (u1.channel_id)::text)
   ->  Index Scan using contentcuration_contentnode_pkey on contentcuration_contentnode  (cost=0.56..8.55 rows=1 width=47) (actual time=0.018..0.019 rows=1 loops=29)
         Index Cond: ((contentcuration_channel.main_tree_id)::text = (id)::text)
   SubPlan 1
     ->  Nested Loop  (cost=0.70..12.75 rows=1 width=0) (never executed)
           ->  Index Only Scan using contentcuration_user_pkey on contentcuration_user u0_1  (cost=0.29..4.30 rows=1 width=4) (never executed)
                 Index Cond: (id = 28085)
                 Heap Fetches: 0
           ->  Index Scan using contentcuration_channel_editors_channel_id_a6a01379_like on contentcuration_channel_editors u1_1  (cost=0.41..8.43 rows=1 width=4) (never executed)
                 Index Cond: ((channel_id)::text = (contentcuration_channel.id)::text)
                 Filter: (user_id = 28085)
   SubPlan 2
     ->  Nested Loop  (cost=0.57..13.68 rows=2 width=32) (actual time=0.005..0.020 rows=29 loops=1)
           ->  Index Only Scan using contentcuration_user_pkey on contentcuration_user u0_2  (cost=0.29..4.30 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)
                 Index Cond: (id = 28085)
                 Heap Fetches: 1
           ->  Index Scan using contentcuration_channel_editors_e8701ad4 on contentcuration_channel_editors u1_2  (cost=0.29..9.36 rows=2 width=36) (actual time=0.003..0.011 rows=29 loops=1)
                 Index Cond: (user_id = 28085)
   SubPlan 3
     ->  Nested Loop  (cost=0.56..12.61 rows=1 width=0) (never executed)
           ->  Index Only Scan using contentcuration_user_pkey on contentcuration_user u0_3  (cost=0.29..4.30 rows=1 width=4) (never executed)
                 Index Cond: (id = 28085)
                 Heap Fetches: 0
           ->  Index Scan using contentcuration_channel_viewers_e8701ad4 on contentcuration_channel_viewers u1_3  (cost=0.28..8.30 rows=1 width=4) (never executed)
                 Index Cond: (user_id = 28085)
                 Filter: ((channel_id)::text = (contentcuration_channel.id)::text)
   SubPlan 4
     ->  Nested Loop  (cost=0.56..12.61 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=1)
           ->  Index Only Scan using contentcuration_user_pkey on contentcuration_user u0_4  (cost=0.29..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
                 Index Cond: (id = 28085)
                 Heap Fetches: 1
           ->  Index Scan using contentcuration_channel_viewers_e8701ad4 on contentcuration_channel_viewers u1_4  (cost=0.28..8.29 rows=1 width=37) (actual time=0.006..0.006 rows=0 loops=1)
                 Index Cond: (user_id = 28085)
   SubPlan 5
     ->  Limit  (cost=28.48..28.48 rows=1 width=21) (actual time=0.024..0.024 rows=0 loops=29)
           ->  Sort  (cost=28.48..28.48 rows=1 width=21) (actual time=0.022..0.022 rows=0 loops=29)
                 Sort Key: u0_5.token DESC
                 Sort Method: quicksort  Memory: 25kB
                 ->  Nested Loop  (cost=4.72..28.47 rows=1 width=21) (actual time=0.018..0.019 rows=0 loops=29)
                       ->  Bitmap Heap Scan on contentcuration_channel_secret_tokens u1_5  (cost=4.43..11.84 rows=2 width=4) (actual time=0.009..0.009 rows=1 loops=29)
                             Recheck Cond: ((channel_id)::text = (contentcuration_channel.id)::text)
                      ->  Index Scan using contentcuration_secrettoken_pkey on contentcuration_secrettoken u0_5  (cost=0.29..8.30 rows=1 width=25) (actual time=0.004..0.004 rows=0 loops=24)
                             Index Cond: (id = u1_5.secrettoken_id)
                             Filter: is_primary
                             Rows Removed by Filter: 0
   SubPlan 6
     ->  Aggregate  (cost=19100.71..19100.72 rows=1 width=8) (actual time=1.062..1.063 rows=1 loops=29)
           ->  Index Scan using contentcuration_contentnode_656442a0 on contentcuration_contentnode u0_6  (cost=0.56..19092.87 rows=3136 width=33) (actual time=0.015..1.016 rows=466 loops=29)
                 Index Cond: (tree_id = contentcuration_contentnode.tree_id)
                 Filter: (((kind_id)::text <> 'topic'::text) OR (kind_id IS NULL))
                 Rows Removed by Filter: 97
   SubPlan 7
     ->  Limit  (cost=19103.36..19103.37 rows=1 width=8) (actual time=0.616..0.616 rows=1 loops=29)
           ->  Sort  (cost=19103.36..19113.85 rows=4196 width=8) (actual time=0.614..0.614 rows=1 loops=29)
                 Sort Key: u0_7.modified DESC
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Index Scan using contentcuration_contentnode_656442a0 on contentcuration_contentnode u0_7  (cost=0.56..19082.38 rows=4196 width=8) (actual time=0.008..0.543 rows=563 loops=29)
                       Index Cond: (tree_id = contentcuration_contentnode.tree_id)
   SubPlan 9
     ->  Index Scan using contentcuration_change_channel_id_35b185d1 on contentcuration_change v0  (cost=0.41..270565.82 rows=96 width=0) (actual time=1758.508..1758.508 rows=0 loops=29)
           Index Cond: ((channel_id)::text = (contentcuration_channel.id)::text)
           Filter: ((created_by_id IS NOT NULL) AND (user_id IS NULL) AND (server_rev > COALESCE((SubPlan 8), '0'::bigint)))
           Rows Removed by Filter: 367
           SubPlan 8
             ->  Limit  (cost=889.17..889.17 rows=1 width=8) (actual time=4.809..4.810 rows=1 loops=10581)
                   ->  Sort  (cost=889.17..889.17 rows=1 width=8) (actual time=4.805..4.805 rows=1 loops=10581)
                         Sort Key: u0_8.server_rev DESC
                         Sort Method: quicksort  Memory: 25kB
                         ->  Bitmap Heap Scan on contentcuration_change u0_8  (cost=18.69..889.16 rows=1 width=8) (actual time=2.402..4.771 rows=6 loops=10581)
                               Recheck Cond: ((channel_id)::text = (v0.channel_id)::text)
                               Filter: ((NOT errored) AND (change_type = 6))
                               Rows Removed by Filter: 9180
                               Heap Blocks: exact=9226201
                               ->  Bitmap Index Scan on contentcuration_change_channel_id_35b185d1_like  (cost=0.00..18.69 rows=303 width=0) (actual time=0.950..0.950 rows=9188 loops=10581)
                                     Index Cond: ((channel_id)::text = (v0.channel_id)::text)
   SubPlan 11
     ->  Index Scan using contentcuration_change_created_by_id_cab27257 on contentcuration_change v0_1  (cost=0.29..47600566.23 rows=17669 width=32) (never executed)
           Index Cond: (created_by_id IS NOT NULL)
           Filter: ((user_id IS NULL) AND (server_rev > COALESCE((SubPlan 10), '0'::bigint)))
           SubPlan 10
             ->  Limit  (cost=889.17..889.17 rows=1 width=8) (never executed)
                   ->  Sort  (cost=889.17..889.17 rows=1 width=8) (never executed)
                         Sort Key: u0_9.server_rev DESC
                         ->  Bitmap Heap Scan on contentcuration_change u0_9  (cost=18.69..889.16 rows=1 width=8) (never executed)
                               Recheck Cond: ((channel_id)::text = (v0_1.channel_id)::text)
                               Filter: ((NOT errored) AND (change_type = 6))
                               ->  Bitmap Index Scan on contentcuration_change_channel_id_35b185d1_like  (cost=0.00..18.69 rows=303 width=0) (never executed)
                                     Index Cond: ((channel_id)::text = (v0_1.channel_id)::text)
 Planning time: 9.159 ms
 Execution time: 51048.493 ms

[bjester]

Closed by #3839