# Slow Page Loads on User Profiles
**URL:** https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165
**Category:** Feature
**Created:** [June 17, 2020, 8:56pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165 "2020-06-17T20:56:15Z")
**Posts on this page:** 16
**Page:** 1
### Author:  [@Ghan](https://meta.discourse.org/u/Ghan)
#### Post date: [June 17, 2020, 8:56pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/1 "2020-06-17T20:56:15Z")
We’ve completed an import of users posts from our current software to Discourse, and one thing we’ve noticed is that some of the user pages are very slow to load (several seconds) and this looks to be due to slow queries. How can we troubleshoot this and identify the bottleneck?
Here’s some more information about our setup:
Server Specs: 8 cores, 32 GB RAM, NVMe SSD storage with ZFS
The board has about 25 million posts. Most of these are private conversations, with about 10.5 million being public.
We do have a number of megatopics (we’re a writing site so individual stories tend to evolve slowly over time, stacking up lots of posts) with thousands of posts, so this might be contributing, but I don’t know how to confirm this.
Here is one query that appears to be trouble - I have seen it at over 5000ms in the built-in load analyzer:
```plaintext
SELECT "topic_links"."id" AS t0_r0, "topic_links"."topic_id" AS t0_r1, "topic_links"."post_id" AS t0_r2, "topic_links"."user_id" AS t0_r3, "topic_links"."url" AS t0_r4, "topic_links"."domain" AS t0_r5, "topic_links"."internal" AS t0_r6, "topic_links"."link_topic_id" AS t0_r7, "topic_links"."created_at" AS t0_r8, "topic_links"."updated_at" AS t0_r9, "topic_links"."reflection" AS t0_r10, "topic_links"."clicks" AS t0_r11, "topic_links"."link_post_id" AS t0_r12, "topic_links"."title" AS t0_r13, "topic_links"."crawled_at" AS t0_r14, "topic_links"."quote" AS t0_r15, "topic_links"."extension" AS t0_r16, "topics"."id" AS t1_r0, "topics"."title" AS t1_r1, "topics"."last_posted_at" AS t1_r2, "topics"."created_at" AS t1_r3, "topics"."updated_at" AS t1_r4, "topics"."views" AS t1_r5, "topics"."posts_count" AS t1_r6, "topics"."user_id" AS t1_r7, "topics"."last_post_user_id" AS t1_r8, "topics"."reply_count" AS t1_r9, "topics"."featured_user1_id" AS t1_r10, "topics"."featured_user2_id" AS t1_r11, "topics"."featured_user3_id" AS t1_r12, "topics"."deleted_at" AS t1_r13, "topics"."highest_post_number" AS t1_r14, "topics"."like_count" AS t1_r15, "topics"."incoming_link_count" AS t1_r16, "topics"."category_id" AS t1_r17, "topics"."visible" AS t1_r18, "topics"."moderator_posts_count" AS t1_r19, "topics"."closed" AS t1_r20, "topics"."archived" AS t1_r21, "topics"."bumped_at" AS t1_r22, "topics"."has_summary" AS t1_r23, "topics"."archetype" AS t1_r24, "topics"."featured_user4_id" AS t1_r25, "topics"."notify_moderators_count" AS t1_r26, "topics"."spam_count" AS t1_r27, "topics"."pinned_at" AS t1_r28, "topics"."score" AS t1_r29, "topics"."percent_rank" AS t1_r30, "topics"."subtype" AS t1_r31, "topics"."slug" AS t1_r32, "topics"."deleted_by_id" AS t1_r33, "topics"."participant_count" AS t1_r34, "topics"."word_count" AS t1_r35, "topics"."excerpt" AS t1_r36, "topics"."pinned_globally" AS t1_r37, "topics"."pinned_until" AS t1_r38, "topics"."fancy_title" AS t1_r39, "topics"."highest_staff_post_number" AS t1_r40, "topics"."featured_link" AS t1_r41, "topics"."reviewable_score" AS t1_r42, "topics"."image_upload_id" AS t1_r43, "posts"."id" AS t2_r0, "posts"."user_id" AS t2_r1, "posts"."topic_id" AS t2_r2, "posts"."post_number" AS t2_r3, "posts"."raw" AS t2_r4, "posts"."cooked" AS t2_r5, "posts"."created_at" AS t2_r6, "posts"."updated_at" AS t2_r7, "posts"."reply_to_post_number" AS t2_r8, "posts"."reply_count" AS t2_r9, "posts"."quote_count" AS t2_r10, "posts"."deleted_at" AS t2_r11, "posts"."off_topic_count" AS t2_r12, "posts"."like_count" AS t2_r13, "posts"."incoming_link_count" AS t2_r14, "posts"."bookmark_count" AS t2_r15, "posts"."score" AS t2_r16, "posts"."reads" AS t2_r17, "posts"."post_type" AS t2_r18, "posts"."sort_order" AS t2_r19, "posts"."last_editor_id" AS t2_r20, "posts"."hidden" AS t2_r21, "posts"."hidden_reason_id" AS t2_r22, "posts"."notify_moderators_count" AS t2_r23, "posts"."spam_count" AS t2_r24, "posts"."illegal_count" AS t2_r25, "posts"."inappropriate_count" AS t2_r26, "posts"."last_version_at" AS t2_r27, "posts"."user_deleted" AS t2_r28, "posts"."reply_to_user_id" AS t2_r29, "posts"."percent_rank" AS t2_r30, "posts"."notify_user_count" AS t2_r31, "posts"."like_score" AS t2_r32, "posts"."deleted_by_id" AS t2_r33, "posts"."edit_reason" AS t2_r34, "posts"."word_count" AS t2_r35, "posts"."version" AS t2_r36, "posts"."cook_method" AS t2_r37, "posts"."wiki" AS t2_r38, "posts"."baked_at" AS t2_r39, "posts"."baked_version" AS t2_r40, "posts"."hidden_at" AS t2_r41, "posts"."self_edits" AS t2_r42, "posts"."reply_quoted" AS t2_r43, "posts"."via_email" AS t2_r44, "posts"."raw_email" AS t2_r45, "posts"."public_version" AS t2_r46, "posts"."action_code" AS t2_r47, "posts"."locked_by_id" AS t2_r48, "posts"."image_upload_id" AS t2_r49 FROM "topic_links" INNER JOIN "topics" ON "topics"."id" = "topic_links"."topic_id" AND ("topics"."deleted_at" IS NULL) INNER JOIN "posts" ON "posts"."id" = "topic_links"."post_id" AND ("posts"."deleted_at" IS NULL) WHERE (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (3,4))) AND "topic_links"."user_id" = 7237 AND "topic_links"."internal" = FALSE AND "topic_links"."reflection" = FALSE AND "topic_links"."quote" = FALSE ORDER BY clicks DESC, topic_links.created_at DESC LIMIT 6
```
When watching the server while loading a user page, it seems to just be spiking CPU usage on the postgresql processes while the query executes. I have not made any configuration changes to the install other than to add MySQL support for the import process. Any advice on next steps would be appreciated!
---
### Author:  [@Ghan](https://meta.discourse.org/u/Ghan)
#### Post date: [June 19, 2020, 8:53pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/2 "2020-06-19T20:53:36Z")
Putting this here for reference. I’ve added 4 indexes to the database, and that has greatly improved performance, though I think it could still be better. This is what I’ve added:
```plaintext
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[]));
```
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [June 19, 2020, 10:44pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/3 "2020-06-19T22:44:43Z")
Hmm are we missing some indexes here @tgxworld?
---
### Author:  [@klopsknoedel](https://meta.discourse.org/u/klopsknoedel)
#### Post date: [June 25, 2020, 1:47pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/4 "2020-06-25T13:47:24Z")
Hi @Ghan,
thank you very much for these indexes. We have [the same issue](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870) for a while now and couldn’t solve this.
After applying these indexes loading user profiles improved drastically. There were some “worst case” profiles which took up to 30s to load. Now they show up in ~5s.
So thanks again and I hope there is some more potential to speed this up.
Kind Regards
Sascha
P.S. Even the `/about` - page takes up to ~20s to load. May this could be speed up as well.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [June 25, 2020, 5:19pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/5 "2020-06-25T17:19:29Z")
Asking again. Are we missing these indexes @tgxworld @sam?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [June 25, 2020, 5:41pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/6 "2020-06-25T17:41:38Z")
Let me check the plans for those queries in Meta.
Queries are quite fast on Meta, but I can see how they can perform badly on under powered databases. We are using ActiveRecord and the code is quite clean, but the generated SQL is quite messy.
Doing the SQL manually we can make the query use existing indexes and optimize it a lot:
Before
After
In the optimized query we only retrieve posts from the user, limiting the loop of posts for the join happening too late.
So I believe we can get better performance without adding index overhead.
---
### Author:  [@Ghan](https://meta.discourse.org/u/Ghan)
#### Post date: [June 25, 2020, 6:51pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/8 "2020-06-25T18:51:23Z")
Can the rewritten version be done in the ActiveRecord code or would you need to pass in a manual query string here?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [June 25, 2020, 7:16pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/9 "2020-06-25T19:16:24Z")
I hope so because the current Ruby code is beautiful. But if not we have a very simple pattern for doing SQL in the app that is already widely used.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [June 26, 2020, 2:07am UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/10 "2020-06-26T02:07:09Z")
> [@codinghorror](#):
>
> Asking again. Are we missing these indexes @tgxworld @sam?
It does seem like we’re missing indexes on alot of these queries running on the user summary page. Will need to optimize these queries one by one.
@Falco I see you’ve assigned yourself to this, do you still want to take it? The tricky part here is that these queries are all mostly untested so we have to be careful not to break stuff.
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [July 6, 2020, 10:17pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/11 "2020-07-06T22:17:19Z")
> [@tgxworld](#):
>
> The tricky part here is that these queries are all mostly untested so we have to be careful not to break stuff.
I added a draft commit for testing the serializer output here so we don’t fly blind:
[https://github.com/discourse/discourse/pull/10175/commits/791a1cfa1822096e9d870f1373c91ad143c0c2d9](https://github.com/discourse/discourse/pull/10175/commits/791a1cfa1822096e9d870f1373c91ad143c0c2d9)
After that, I tried re-applying what I did [here](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/6) but turns out I was adding an extra clause that was making it fast but incorrect.
The fact that we pass all this (even the most liked/ liked by) through the guardian means the info is always very correct and tailored to the viewer, but makes caching it not helping against multiple users.
I guess we can _at least_ add a longer cache to this endpoint? In my opinion a 1 hour cache would be adequate, as the information in the summary doesn’t change often.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [July 6, 2020, 10:18pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/12 "2020-07-06T22:18:37Z")
> [@Falco](#):
>
> In my opinion a 1 hour cache would be adequate, as the information in the summary doesn’t change often.
Sounds good.. make it so!
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [July 7, 2020, 10:24pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/13 "2020-07-07T22:24:16Z")
I pushed the cache to my PR branch:
[https://github.com/discourse/discourse/pull/10175](https://github.com/discourse/discourse/pull/10175)
While debugging further, I noticed that we also have some N+1 in there. Thumbnails and Solved are triggering a N+1 for the topic lists. Any ideas for it @david @tgxworld ?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [July 13, 2020, 7:29pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/14 "2020-07-13T19:29:40Z")
Merged the cache work.
| Before | After |
| --- | --- |
|  |  |
Still working on N+1.
---
### Author:  [@tgxworld](https://meta.discourse.org/u/tgxworld)
#### Post date: [July 16, 2020, 8:56am UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/15 "2020-07-16T08:56:22Z")
> [@Falco](#):
>
> Thumbnails and Solved are triggering a N+1 for the topic lists. Any ideas for it @david @tgxworld ?
Which serializer does the user summary page use for topic lists? For the full page search, I recently fixed the N+1 by dropping the `thumbnails` attributes from the search serializer because it isn’t used at all.
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [November 23, 2020, 5:28pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/16 "2020-11-23T17:28:24Z")
Fixed in [PERF: Preload topic thumbnails for all topic lists by davidtaylorhq · Pull Request #11238 · discourse/discourse · GitHub](https://github.com/discourse/discourse/pull/11238/files) and [PERF: Simplify topic serialization for user summary page by davidtaylorhq · Pull Request #11236 · discourse/discourse · GitHub](https://github.com/discourse/discourse/pull/11236)
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [November 23, 2020, 5:28pm UTC](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/17 "2020-11-23T17:28:32Z")