נושאים נטענים לאט או בכלל לא כשיש להם הרבה תגובות והמשתמש סימן אותם בסימנייה

martin · 26 באוקטובר,‏ 2020,‏ 4:32am

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	1054	26 באוקטובר,‏ 2020
Major slowdown for staff in large topics Support	13	2232	4 ביוני,‏ 2019
Performance improvements on long topics? Support	30	5484	20 באוגוסט,‏ 2018
Slow Page Loads on User Profiles Feature	15	2441	23 בנובמבר,‏ 2020
Topics jumping to wrong post Bug	23	1140	29 באוגוסט,‏ 2023

נושאים נטענים לאט או בכלל לא כשיש להם הרבה תגובות והמשתמש סימן אותם בסימנייה

נושאים קשורים