# 100GB+ 数据库导致配置文件加载缓慢

**URL:** https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775
**Category:** Feature
**Created:** [2020年十二月19日 00:28 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775 "2020-12-19T00:28:09Z")
**Posts on this page:** 1
**Showing post:** 35

<div class="post-metadata">

### Author: ![Ghan](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/ghan/32/177964_2.png) [@Ghan](https://meta.discourse.org/u/Ghan)
#### Post date: [2020年十二月24日 18:00 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775/35 "2020-12-24T18:00:47Z")

</div>

我添加了之前提出的索引以协助解决此问题（[Slow Page Loads on User Profiles - #2 by Ghan](https://meta.discourse.org/t/slow-page-loads-on-user-profiles/155165/2?u=ghan%EF%BC%89%EF%BC%8C%E5%8A%A0%E8%BD%BD%E6%97%B6%E9%97%B4%E5%B7%B2%E5%A4%A7%E5%B9%85%E7%BC%A9%E7%9F%AD%E3%80%82)

添加索引后的查询计划：

```plaintext
                                                                                                 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)

```

以下是相关索引语句，如有需要可自行添加：

```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[]));

```

---

_[View the full topic](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775)._