# 100GB 이상 데이터베이스에서 프로필 로딩이 느립니다 **URL:** \ **Category:** Feature\ **Created:** [12월 19, 2020, 12:28오전 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775 "2020-12-19T00:28:09Z")\ **Posts on this page:** 4\ **Page:** 3
**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:** [12월 27, 2020, 10:32오후 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775/41 "2020-12-27T22:32:45Z")
성능을 더 향상시키는 데 도움이 될 수도 있습니다. 확신은 없어요. user\_id 필터는 posts 테이블에 적용되므로, join의 topic\_links 쪽과 어떻게 상호작용하는지 정확히 모르겠습니다. 하지만 분명히 살펴볼 가치가 있습니다. ---
**Author:** ![TheDarkWizard](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/thedarkwizard/32/177913_2.png) [@TheDarkWizard](https://meta.discourse.org/u/TheDarkWizard)\ **Post date:** [12월 27, 2020, 11:33오후 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775/42 "2020-12-27T23:33:41Z")
> [@sam](#): > > 하지만 개념적으로 우리가 신경 써야 하는 것은 사용자 범위에 속한 링크뿐인데, 새로 제안된 인덱스는 왜 사용자에 스코프가 설정되어 있지 않나요? 사용자 ID를 인덱싱해 보라고 제안하시나요? ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [12월 29, 2020, 2:24오전 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775/43 "2020-12-29T02:24:09Z")
OK, 정리해 보겠습니다: ```sql 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, "topics"."slow_mode_seconds" AS t1_r44, "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"."deleted_at" IS NULL AND "topics"."id" = "topic_links"."topic_id" INNER JOIN "posts" ON "posts"."deleted_at" IS NULL AND "posts"."id" = "topic_links"."post_id" WHERE "posts"."user_id" = 32 AND (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 (20,25,69,83,87,88,89,91,94,95,96,97,99,100))) AND "topic_links"."user_id" = 32 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; ``` 이 쿼리는 해당 섹션을 위해 존재합니다: ![image](https://global.discourse-cdn.com/meta/original/3X/f/b/fb971bd7829ea0ee12a2b5955330f4cd9f642574.png) Topic links 테이블에는 다음과 같은 인덱스가 있습니다: > 또한 `user_id` 컬럼도 가지고 있습니다 > 다음과 같습니다: > 우리가 생성하는 링크들은 user id에 올바르게 바인딩되어 있습니다. 따라서, 여기서는 단순히 인덱스를 개선하는 것이 합리적이라고 생각합니다: 현재의 조건절은 다음과 같습니다: `"topic_links"."user_id" = 32 AND "topic_links"."internal" = FALSE AND "topic_links"."reflection" = FALSE AND "topic_links"."quote" = FALSE ` 이는 `topic_id` / `post_id`를 기준으로 조인됩니다. 따라서 인덱스는 `create index idxFaster on topic_links(user_id, clicks desc, created_at desc) where (NOT reflection and NOT quote and NOT internal) include (post_id, topic_id)` 이렇게 되어야 합니다. 다만, AR(Active Record)이 여기서 과다 선택(over-select)을 수행하므로, 커버링 인덱스를 만드는 것은 다소 무의미하여 include 부분을 제거할 수 있습니다. 추가로 만든 인덱스들을 모두 삭제하고 `idxFaster`가 문제를 해결하는지 확인해 보실 수 있을까요? **수정** > > > These 2 indexes optimise performance on profile pages. > > The summary page display…s: > > 1. A list of "Top Link" - links sorted by number of clicks posted by user > 2. A list of "Top Replies" - replies made by a user that go the most hearts > > These two areas could devolve into full index or table scans, new indexes are there to avoid this cost on large dbs > > One minor downside is that storage requirements go a tiny bit up to maintain the new indexes 병합되면 이 문제를 수정할 것입니다. ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [1월 1, 2021, 3:15오전 UTC](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775/44 "2021-01-01T03:15:23Z")
[이전 페이지](https://meta.discourse.org/t/slow-profile-loads-with-100gb-database/173775.md?page=2)