トピックに多くの返信があり、ユーザーがブックマークしている場合に、トピックの読み込みが遅いか、まったく読み込まれない

martin · 2020 年 10 月 26 日午前 4: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（関連トピックを作成：https://meta.discourse.org/t/bookmarking-a-post-on-a-large-thread-creates-absurd-loading-times/168041）

トピック		返信	表示
Bookmarking a Post on a Large Topic creates Absurd Loading Times Bug	10	1084	2020 年 10 月 26 日
Major slowdown for staff in large topics Support	13	2261	2019 年 6 月 4 日
Performance improvements on long topics? Support	30	5561	2018 年 8 月 20 日
Slow Page Loads on User Profiles Feature	15	2486	2020 年 11 月 23 日
Topics jumping to wrong post Bug	23	1209	2023 年 8 月 29 日