关于大型实例调优还有其他一些主题。运行大型 PostgreSQL 的原则与 MySQL 相同，但细节之处见真章。
已经在互联网和这里的 Meta 上尝试搜索相关主题了。您有没有觉得可能有帮助的话题可以推荐给我们？
您当前的 app.yml 中以下行被注释掉了：
请取消这些行的注释，并相应地增加数值。之后您需要重新构建。
3 个赞
我已经完成了这些操作并重新构建了容器。我试图再次运行 reindex，但它看起来又卡住了。以下是我得到的结果：
有什么方法可以判断 work_mem 是否需要调高吗？我看到磁盘的读写负载相当高，所以我怀疑可能正在进行文件系统排序或类似操作，但我不确定如何确认这一点，或者是否有其他问题在发生。
1 个赞
使用你在首帖中列出的查询。在 psql 命令行中，在这些查询前加上 EXPLAIN ANALYZE 后运行，并将输出粘贴到这里。
2 个赞
Looks like there’s some caching at work so subsequent loads tend to be faster, but > 5 seconds is still brutal when the cache expires.
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;
两者都在为临时文件使用内存，且执行时间足够好。也许它正在趋于稳定？
重新索引只需运行一次即可——一旦索引采用 v13 磁盘格式，它们将保持该格式。
是的，如果你的 VACUUM 命令未包含 ANALYZE，你将无法利用索引。
2 个赞
好的，这条查询正在获取给定用户点赞数最多的帖子。
这里的估算偏差很大——规划器估算的行数为 2,389 行，实际却得到了 20,157 行。
再往上一层，又出现了 5 倍的差异。
正是这类估算错误会导致糟糕的查询性能。
2 个赞
据我所知，它们本就不应使用除 v13 磁盘格式以外的任何格式。我们将数据从另一款论坛软件导入到一个全新安装的 Discourse 实例中，而该实例早已运行 v13 版本。目前，我们正致力于确保导入后所有索引都能正确重建。
有道理。我在增加 maintenance_work_mem 后尝试再次运行重建索引。看起来 postmaster 现在占用的内存比之前更多，但我仍不确定它能否在合理的时间内完成。
2 个赞
您可能需要执行以下操作以在数据库重新索引期间停止 Web 服务器：
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 秒，这也是个人资料页面加载缓慢的原因之一。这仅仅是个人资料页面上的众多查询之一（尽管在速度方面是成本最高的查询之一）。在添加索引后，查询规划器在此处识别并使用了该索引，将该子树的查询时间缩短至约 30 毫秒：
-> 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 个赞