대형 인스턴스 튜닝에 관한 몇 가지 다른 주제도 있습니다. 대형 PostgreSQL을 실행하는 원리는 MySQL과 동일하지만, 디테일에 악마가 있습니다.
이미 인터넷과 메타에서 해당 주제에 대해 검색을 시도해 보고 있습니다. 도움이 될 만한 주제로 추천해 주실 만한 것이 있으신가요?
현재 app.yml 파일에 아래 줄들이 주석 처리되어 있습니다:
해당 줄들의 주석을 해제하고 값을 적절히 증가시키세요. 그 후 다시 빌드해야 합니다.
3개의 좋아요
해당 작업을 수행하고 컨테이너를 다시 빌드했습니다. 이제 재인덱싱을 다시 실행해 보고 있는데, 다시 멈춘 것처럼 보입니다. 두 항목에 대해 확인한 내용은 다음과 같습니다:
work_mem 값을 더 높여야 하는지 확인할 방법이 있을까요? 디스크에 상당히 많은 부담이 가고 있는 것을 보니 파일 시스템 정렬이나 유사한 작업을 수행하고 있는 것 같지만, 이것이 실제로 그런지 아니면 다른 문제가 있는지는 어떻게 확인해야 할지 잘 모르겠습니다.
1개의 좋아요
OP에서 나열한 쿼리를 사용하세요. psql 셸에서 해당 쿼리 앞에 EXPLAIN ANALYZE를 붙여서 실행한 후, 출력을 여기에 붙여넣어 주세요.
2개의 좋아요
캐싱이 작동하는 것 같아서 이후 로딩은 더 빠른 편이지만, 캐시가 만료되면 5초를 초과하는 시간은 여전히 끔찍합니다.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=26396.41..26397.10 rows=6 width=1278) (actual time=90.879..114.499 rows=6 loops=1)
-> Gather Merge (cost=26396.41..26448.85 rows=456 width=1278) (actual time=90.877..114.496 rows=6 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=25396.40..25397.54 rows=456 width=1278) (actual time=87.195..87.197 rows=5 loops=2)
Sort Key: posts.like_count DESC, posts.created_at DESC
Sort Method: top-N heapsort Memory: 38kB
Worker 0: Sort Method: top-N heapsort Memory: 39kB
-> Nested Loop (cost=103.30..25388.23 rows=456 width=1278) (actual time=3.200..83.497 rows=3610 loops=2)
-> Parallel Bitmap Heap Scan on posts (cost=99.06..9509.95 rows=1356 width=783) (actual time=3.075..56.880 rows=5932 loops=2)
Recheck Cond: (user_id = 27510)
Filter: ((deleted_at IS NULL) AND (post_number > 1) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Rows Removed by Filter: 1071
Heap Blocks: exact=6272
-> Bitmap Index Scan on index_posts_on_user_id_and_created_at (cost=0.00..98.48 rows=2389 width=0) (actual time=3.916..3.917 rows=20157 loops=1)
Index Cond: (user_id = 27510)
-> Index Scan using topics_pkey on topics (cost=4.24..11.71 rows=1 width=495) (actual time=0.004..0.004 rows=1 loops=11864)
Index Cond: (id = posts.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: 0
SubPlan 1
-> Seq Scan on categories (cost=0.00..3.74 rows=28 width=4) (actual time=0.013..0.029 rows=35 loops=2)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 2.535 ms
Execution Time: 114.657 ms
(25 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=25004.87..25004.87 rows=1 width=1464) (actual time=98.136..121.987 rows=6 loops=1)
-> Sort (cost=25004.87..25004.87 rows=1 width=1464) (actual time=98.134..121.984 rows=6 loops=1)
Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
Sort Method: top-N heapsort Memory: 55kB
-> Nested Loop (cost=1103.75..25004.86 rows=1 width=1464) (actual time=6.763..118.114 rows=3443 loops=1)
-> Gather (cost=1099.51..24993.34 rows=1 width=969) (actual time=6.464..88.294 rows=9130 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=99.51..23993.24 rows=1 width=969) (actual time=3.151..73.939 rows=4565 loops=2)
-> Parallel Bitmap Heap Scan on posts (cost=99.08..9506.46 rows=1405 width=783) (actual time=3.032..43.325 rows=7003 loops=2)
Recheck Cond: (user_id = 27510)
Filter: ((deleted_at IS NULL) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Heap Blocks: exact=5953
-> Bitmap Index Scan on index_posts_on_user_id_and_created_at (cost=0.00..98.48 rows=2389 width=0) (actual time=3.790..3.791 rows=20157 loops=1)
Index Cond: (user_id = 27510)
-> Index Scan using index_topic_links_on_post_id on topic_links (cost=0.43..10.30 rows=1 width=186) (actual time=0.003..0.004 rows=1 loops=14006)
Index Cond: (post_id = posts.id)
Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 27510))
Rows Removed by Filter: 0
-> Index Scan using topics_pkey on topics (cost=4.24..11.52 rows=1 width=495) (actual time=0.003..0.003 rows=0 loops=9130)
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..3.74 rows=28 width=4) (actual time=0.009..0.022 rows=35 loops=1)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 1.102 ms
Execution Time: 122.098 ms
(28 rows)
SELECT "posts"."id" AS t0_r0, "posts"."user_id" AS t0_r1, "posts"."topic_id" AS t0_r2, "posts"."post_number" AS t0_r3, "posts"."raw" AS t0_r4, "posts"."cooked" AS t0_r5, "posts"."created_at" AS t0_r6, "posts"."updated_at" AS t0_r7, "posts"."reply_to_post_number" AS t0_r8, "posts"."reply_count" AS t0_r9, "posts"."quote_count" AS t0_r10, "posts"."deleted_at" AS t0_r11, "posts"."off_topic_count" AS t0_r12, "posts"."like_count" AS t0_r13, "posts"."incoming_link_count" AS t0_r14, "posts"."bookmark_count" AS t0_r15, "posts"."score" AS t0_r16, "posts"."reads" AS t0_r17, "posts"."post_type" AS t0_r18, "posts"."sort_order" AS t0_r19, "posts"."last_editor_id" AS t0_r20, "posts"."hidden" AS t0_r21, "posts"."hidden_reason_id" AS t0_r22, "posts"."notify_moderators_count" AS t0_r23, "posts"."spam_count" AS t0_r24, "posts"."illegal_count" AS t0_r25, "posts"."inappropriate_count" AS t0_r26, "posts"."last_version_at" AS t0_r27, "posts"."user_deleted" AS t0_r28, "posts"."reply_to_user_id" AS t0_r29, "posts"."percent_rank" AS t0_r30, "posts"."notify_user_count" AS t0_r31, "posts"."like_score" AS t0_r32, "posts"."deleted_by_id" AS t0_r33, "posts"."edit_reason" AS t0_r34, "posts"."word_count" AS t0_r35, "posts"."version" AS t0_r36, "posts"."cook_method" AS t0_r37, "posts"."wiki" AS t0_r38, "posts"."baked_at" AS t0_r39, "posts"."baked_version" AS t0_r40, "posts"."hidden_at" AS t0_r41, "posts"."self_edits" AS t0_r42, "posts"."reply_quoted" AS t0_r43, "posts"."via_email" AS t0_r44, "posts"."raw_email" AS t0_r45, "posts"."public_version" AS t0_r46, "posts"."action_code" AS t0_r47, "posts"."locked_by_id" AS t0_r48, "posts"."image_upload_id" AS t0_r49, "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 FROM "posts" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) WHERE ("posts"."deleted_at" IS NULL) 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 (3,4,5,17,19,25,26,27,28))) AND "posts"."user_id" = 27510 AND (post_number > 1) ORDER BY posts.like_count DESC, posts.created_at DESC LIMIT 6;
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"."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"."user_id" = 27510 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 (3,4,5,17,19,25,26,27,28))) AND "topic_links"."user_id" = 27510 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;
둘 다 임시 파일에 메모리를 사용 중이고, 실행 시간은 충분히 좋습니다. 이제 안정화되고 있는 걸까요?
이 analyze가 캐시된 데이터를 가져오는 것 같습니다. 사이트의 요약 페이지에는 훨씬 긴 실행 시간이 표시되어 있습니다:
캐시가 만료된 후에 analyze를 다시 실행해 보겠습니다.
재인덱싱은 한 번만 실행하면 됩니다 - 인덱스가 v13 디스크 포맷으로 변경되면, 그 후로는 v13 디스크 포맷을 유지합니다.
네, VACUUM에 ANALYZE가 포함되지 않았다면 인덱스 사용에 관한 정보를 놓치고 있는 것입니다.
2개의 좋아요
자, 이 쿼리는 특정 사용자의 좋아요 수가 가장 많은 게시물을 가져오는 쿼리입니다.
여기서 추정치가 크게 빗나갔습니다. 플래너는 2,389행을 예상했지만 실제로는 20,157행을 반환했습니다.
한 단계 위에서도 또다시 5배의 오차가 발생했습니다.
이러한 잘못된 추정치는 쿼리 성능을 극도로 저하시키는 원인이 됩니다.
2개의 좋아요
제가 아는 한, 인덱스는 v13 디스크 형식 외에는 어떤 형식도 사용해서는 안 됩니다. 우리는 다른 포럼 소프트웨어에서 데이터를 가져와서 이미 v13을 실행 중인 새로운 Discourse 설치 환경으로 가져왔습니다. 현재는 가져오기 후 모든 인덱스가 올바르게 재구축되었는지 확인하려고 노력하고 있습니다.
그렇네요. maintenance_work_mem을 늘린 후 재인덱싱을 다시 실행해 보고 있습니다. postmaster가 이전보다 더 많은 메모리를 사용 중인 것으로 보이지만, 합리적인 시간 내에 완료될지는 여전히 알 수 없습니다.
2개의 좋아요
데이터베이스 재인덱싱 중에 웹 서버를 중지하려면 다음을 실행할 수 있습니다.
sv stop unicorn
1개의 좋아요
좋아요, 마침 재인덱싱이 합리적인 시간 내에 완료되었습니다. 재구축할 수 없어서 건너뛴 인덱스가 몇 개 있었다고 했지만, 그 이유에 대한 추가적인 세부 사항은 없었습니다. 현재 테이블 통계는 다음과 같습니다:
프로필 로딩 문제는 더 악화되는 것 같습니다:
해당 쿼리들의 EXPLAIN 결과는 다음과 같습니다:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=625609.73..625610.43 rows=6 width=1278) (actual time=2058.211..2154.991 rows=6 loops=1)
-> Gather Merge (cost=625609.73..630928.70 rows=45588 width=1278) (actual time=1654.881..1751.658 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=624609.70..624666.69 rows=22794 width=1278) (actual time=1619.464..1619.469 rows=5 loops=3)
Sort Key: posts.like_count DESC, posts.created_at DESC
Sort Method: top-N heapsort Memory: 37kB
Worker 0: Sort Method: top-N heapsort Memory: 37kB
Worker 1: Sort Method: top-N heapsort Memory: 38kB
-> Parallel Hash Join (cost=63000.65..624201.13 rows=22794 width=1278) (actual time=1383.820..1600.913 rows=20578 loops=3)
Hash Cond: (posts.topic_id = topics.id)
-> Parallel Bitmap Heap Scan on posts (cost=1875.97..562899.73 rows=67322 width=783) (actual time=63.310..264.627 rows=20579 loops=3)
Recheck Cond: ((user_id = 7237) AND (deleted_at IS NULL))
Filter: ((post_number > 1) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Rows Removed by Filter: 39566
Heap Blocks: exact=50390
-> Bitmap Index Scan on idx_posts_user_id_deleted_at (cost=0.00..1835.58 rows=167352 width=0) (actual time=36.507..36.508 rows=180435 loops=1)
Index Cond: (user_id = 7237)
-> Parallel Hash (cost=59504.20..59504.20 rows=129638 width=495) (actual time=1319.362..1319.364 rows=131785 loops=3)
Buckets: 524288 Batches: 1 Memory Usage: 190560kB
-> Parallel Seq Scan on topics (cost=3.81..59504.20 rows=129638 width=495) (actual time=316.007..1204.917 rows=131785 loops=3)
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: 174529
SubPlan 1
-> Seq Scan on categories (cost=0.00..3.74 rows=28 width=4) (actual time=17.841..17.887 rows=35 loops=3)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 0.751 ms
JIT:
Functions: 91
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 11.057 ms, Inlining 134.342 ms, Optimization 762.277 ms, Emission 451.708 ms, Total 1359.384 ms
Execution Time: 2204.845 ms
(32 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=726143.96..726144.66 rows=6 width=1464) (actual time=5250.325..5305.144 rows=6 loops=1)
-> Gather Merge (cost=726143.96..726351.17 rows=1776 width=1464) (actual time=4800.064..4854.881 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=725143.93..725146.15 rows=888 width=1464) (actual time=4762.610..4762.615 rows=5 loops=3)
Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
Sort Method: top-N heapsort Memory: 36kB
Worker 0: Sort Method: top-N heapsort Memory: 37kB
Worker 1: Sort Method: top-N heapsort Memory: 32kB
-> Nested Loop (cost=574851.64..725128.02 rows=888 width=1464) (actual time=649.373..4710.853 rows=39385 loops=3)
-> Parallel Hash Join (cost=574847.40..695445.64 rows=2624 width=969) (actual time=630.974..3847.821 rows=336541 loops=3)
Hash Cond: (topic_links.post_id = posts.id)
-> Parallel Bitmap Heap Scan on topic_links (cost=11248.93..130753.84 rows=416505 width=186) (actual time=58.964..3095.540 rows=336541 loops=3)
Recheck Cond: (user_id = 7237)
Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote))
Rows Removed by Filter: 8
Heap Blocks: exact=23544
-> Bitmap Index Scan on index_topic_links_on_user_id (cost=0.00..10999.02 rows=1000879 width=0) (actual time=45.320..45.322 rows=1009648 loops=1)
Index Cond: (user_id = 7237)
-> Parallel Hash (cost=562726.85..562726.85 rows=69730 width=783) (actual time=571.264..571.266 rows=60145 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 71264kB
-> Parallel Bitmap Heap Scan on posts (cost=1877.42..562726.85 rows=69730 width=783) (actual time=377.440..481.561 rows=60145 loops=3)
Recheck Cond: ((user_id = 7237) AND (deleted_at IS NULL))
Filter: (post_type = ANY ('{1,2,3,4}'::integer[]))
Heap Blocks: exact=141396
-> Bitmap Index Scan on idx_posts_user_id_deleted_at (cost=0.00..1835.58 rows=167352 width=0) (actual time=29.225..29.226 rows=180435 loops=1)
Index Cond: (user_id = 7237)
-> Index Scan using topics_pkey on topics (cost=4.24..11.31 rows=1 width=495) (actual time=0.002..0.002 rows=0 loops=1009624)
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..3.74 rows=28 width=4) (actual time=17.793..17.835 rows=35 loops=3)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 4.526 ms
JIT:
Functions: 118
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 14.724 ms, Inlining 94.921 ms, Optimization 934.359 ms, Emission 525.383 ms, Total 1569.387 ms
Execution Time: 5309.478 ms
(40 rows)
이 문제를 해결하기 위해 이전에 생각해 낸 인덱스를 추가했는데, 로딩 시간이 상당히 줄었습니다. (Slow Page Loads on User Profiles - #2 by Ghan)
인덱스 추가 후 쿼리 계획:
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)
누군가 이 인덱스를 추가하고 싶다면 아래에 목록을 올립니다:
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개의 좋아요
여기와 여기에서 해당 인덱스가 누락되었는지 질문하셨습니다. 이 인덱스만 추가해도 프로필 로딩 속도 측면에서 밤과 낮의 차이가 납니다. 이 인덱스들은 반드시 고려되어야 합니다.
Slow Page Loads on User Profiles - #12 by codinghorror 에 추가된 캐시가 큰 차이를 만들었다는 점은 사실이지만, 이는 후속 로딩에만 해당됩니다. 인덱스는 전반적으로 초기 경험을 향상시킵니다. 캐시로 인해 시간이 지남에 따라 로딩이 빨라진다고 해서, 사용자가 캐시된 시간 동안 느린 로딩 시간을 견뎌야 할 이유는 없습니다. 목표는 첫 번째 초기 로딩(캐시가 만료될 때마다)을 개선하는 것이었고, 이를 통해 상당한 성과를 거두었습니다.
4개의 좋아요
사용자 ID로 범위가 지정된 사용자 페이지에 대해, 클릭 수/좋아요 수 기준으로 정렬된 모든 행에 인덱스가 왜 필요한지 명확하지 않습니다.
2개의 좋아요
클릭 수와 관련하여, 우리 경우 이 부분이 성능 개선의 핵심인 것 같습니다:
-> Sort (cost=725143.93..725146.15 rows=888 width=1464) (actual time=4762.610..4762.615 rows=5 loops=3)
Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
이 정렬 작업에는 4초가 소요되며, 이것이 프로필이 이렇게 느리게 로딩되는 이유 중 하나입니다. 프로필 페이지에는 이 쿼리를 포함한 수많은 쿼리가 존재하지만(속도 측면에서 가장 비용이 높은 쿼리 중 하나), 인덱스를 추가한 후 쿼리 플래너가 이를 여기서 선택하여 이 서브트리의 쿼리 실행 시간을 약 30ms로 줄였습니다:
-> 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)
물론 쿼리 플랜은 다른 부분에서도 변경되지만, 인덱스가 여기서 명확하게 참조되고 있으며 쿼리가 훨씬 더 빠르게 실행됩니다.
1개의 좋아요
하지만 개념적으로 우리가 신경 쓸 부분은 사용자 범위로 제한된 링크뿐인데, 왜 새로 제안된 인덱스는 사용자별로 스코프가 설정되어 있지 않은가요?
1개의 좋아요