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

martin · 2020年十月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	9	1158	2020年十月26日
Major slowdown for staff in large topics Support	12	2373	2019年六月3日
Performance improvements on long topics? Support	29	5750	2018年七月12日
Slow Page Loads on User Profiles Feature	14	2668	2020年十一月23日
Topics jumping to wrong post Bug	22	1493	2023年八月28日

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

相關主題