المواضيع بطيئة التحميل أو لا يتم تحميلها على الإطلاق عندما تحتوي على العديد من الردود والمستخدم لديه علامة مرجعية فيها

martin · 26 أكتوبر 2020، 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	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

المواضيع بطيئة التحميل أو لا يتم تحميلها على الإطلاق عندما تحتوي على العديد من الردود والمستخدم لديه علامة مرجعية فيها

الموضوعات ذات الصلة