当主题包含许多回复且用户已将其加入书签时，主题加载缓慢或根本不加载

martin · 2020 年10 月 26 日 04:32

此问题已在以下提交中修复：

FIX: Prevent slow bookmark first post reminder at query for topic (#11024)

committed 04:30AM - 26 Oct 20 UTC

On forums with a large amount of posts when a user had a bookmark in the topic, …PostgreSQL was using an inefficient query plan to fetch the first post of the topic. When running this ActiveRecord query: ``` topic.posts.with_deleted.where(post_number: 1).first ``` The following query plan was produced: ``` Limit (cost=0.43..583.49 rows=1 width=891) (actual time=3850.515..3850.515 rows=1 loops=1) -> Index Scan using posts_pkey on posts (cost=0.43..391231.51 rows=671 width=891) (actual time=3850.514..3850.514 rows=1 loops=1) Filter: ((topic_id = 160918) AND (post_number = 1)) Rows Removed by Filter: 2274520 Planning time: 0.200 ms Execution time: 3850.559 ms (6 rows) ``` The issue here is the combination of ORDER BY and LIMIT causing the ineficcient Index Scan using posts_pkey on posts to be used. When we correct the AR call to this: ``` topic.posts.with_deleted.find_by(post_number: 1) ``` We end up with a query that still has a LIMIT but no ORDER BY, which in turn creates a much more efficient query plan: ``` Limit (cost=0.43..1.44 rows=1 width=891) (actual time=0.033..0.034 rows=1 loops=1) -> Index Scan using index_posts_on_topic_id_and_post_number on posts (cost=0.43..678.82 rows=671 width=891) (actua l time=0.033..0.033 rows=1 loops=1) Index Cond: ((topic_id = 160918) AND (post_number = 1)) Planning time: 0.167 ms Execution time: 0.072 ms (5 rows) ``` This query plan uses the correct index, `Index Scan using index_posts_on_topic_id_and_post_number on posts`. Note that this is only a problem on forums with a larger amount of posts; tiny forums would not notice the difference. On large forums a query for a topic that takes 1s without a bookmark can take 8-30 seconds, and even end up with 502 errors from nginx.

cc @Macaw，他创建了相关话题 Bookmarking a Post on a Large Topic creates Absurd Loading Times

话题		回复	浏览量
Bookmarking a Post on a Large Topic creates Absurd Loading Times Bug	10	1073	2020 年10 月 26 日
Major slowdown for staff in large topics Support	13	2236	2019 年6 月 4 日
Performance improvements on long topics? Support	30	5517	2018 年8 月 20 日
Slow Page Loads on User Profiles Feature	15	2459	2020 年11 月 23 日
Topics jumping to wrong post Bug	23	1178	2023 年8 月 29 日