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

martin · 2020 年 10 月 26 日午前 4:32

This has been fixed in:

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 who made this related topic Bookmarking a Post on a Large Topic creates Absurd Loading Times

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

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

関連トピック