Slow Profile Loads with 100GB+ database

feature
35

I’ve added the indexes I came up with previously to help with this (Slow Page Loads on User Profiles - #2 by Ghan) and that has cut the load times down quite a bit.

Query plan after adding indexes:

                                                                                                 QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1004.13..25136.02 rows=6 width=1473) (actual time=84.592..96.171 rows=6 loops=1)
   ->  Gather Merge  (cost=1004.13..9649737.42 rows=2399 width=1473) (actual time=84.591..96.168 rows=6 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Nested Loop  (cost=4.11..9648460.49 rows=1000 width=1473) (actual time=60.452..60.471 rows=4 loops=3)
               ->  Nested Loop  (cost=0.87..9619828.27 rows=2943 width=977) (actual time=0.115..47.798 rows=6058 loops=3)
                     ->  Parallel Index Scan using index_topic_links_on_clicks_and_created_desc on topic_links  (cost=0.43..6728256.06 rows=403188 width=186) (actual time=0.051..30.428 rows=6058 loops=3)
                           Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 7237))
                           Rows Removed by Filter: 54345
                     ->  Index Scan using posts_pkey on posts  (cost=0.44..7.17 rows=1 width=791) (actual time=0.002..0.002 rows=1 loops=18173)
                           Index Cond: (id = topic_links.post_id)
                           Filter: ((deleted_at IS NULL) AND (user_id = 7237) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
               ->  Index Scan using topics_pkey on topics  (cost=3.24..9.73 rows=1 width=496) (actual time=0.002..0.002 rows=0 loops=18173)
                     Index Cond: (id = topic_links.topic_id)
                     Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
                     Rows Removed by Filter: 1
                     SubPlan 1
                       ->  Seq Scan on categories  (cost=0.00..2.74 rows=28 width=4) (actual time=0.031..0.049 rows=35 loops=3)
                             Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
 Planning Time: 1.205 ms
 Execution Time: 96.258 ms
(21 rows)

Here are the indexes if anyone wants to add them:

CREATE INDEX index_topic_links_on_clicks_and_created ON public.topic_links USING btree (clicks, created_at);
CREATE INDEX index_posts_on_like_count_and_created ON public.posts USING btree (like_count, created_at);
CREATE INDEX index_topic_links_on_clicks_and_created_desc ON public.topic_links USING btree (clicks DESC, created_at DESC);
CREATE INDEX index_posts_on_like_count_and_created_desc ON public.posts USING btree (like_count DESC, created_at DESC, user_id) WHERE deleted_at IS NULL AND post_number > 1 AND (post_type = ANY ('{1,2,3,4}'::integer[]));
1 Like