Chat Pitchfork 超时：回复静默创建线程，自动追踪随时间膨胀

sam · 2026 年4 月 21 日 07:01

太精彩的总结了！

我开始研究这个，在我的测试中，性能提升了 50 倍：

github.com/discourse/discourse

PERF: speed up chat channel and thread unread queries (#39398)

main ← chat-perf

opened 06:57AM - 21 Apr 26 UTC

SamSaffron

+254 -134

Rewrites the channel and thread unread queries to use a CTE that picks the candi…date set up front, followed by LATERAL subqueries for each aggregate. The previous form used three correlated subqueries that each re-joined the user's full membership lists, and packed the unread predicate into a single OR-bag that forced a sequential scan over every message past last_read_message_id. The channel query now: - Limits candidate channels in a CTE before aggregating. - Splits unread_count into three additive, index-friendly pieces (standalone messages, thread original messages, and DM thread replies) instead of one OR branch. - Pushes the muted check into a CASE on the outer row. The thread query now: - Orders candidate memberships by last_message_id and caps them at MAX_THREADS in the CTE, so recent threads always win. - Derives unread and watched counts from a single aggregate switched by notification_level. - Skips threads whose channel membership is missing. Also adds a covering index on chat_messages(thread_id, id) INCLUDE (user_id) WHERE deleted_at IS NULL to support the thread aggregates as index-only scans, and adds specs for the orphaned-thread case and the MAX_THREADS recency cap. --- This gives this about 50x perf improvement in testing

我们可能还需要进行更多测试，但好处是，这里的小 bug 最多只会导致计数略有偏差，我们必须减轻负载，别无选择。

话题		回复	浏览量
Query causing 400% CPU Load Bug chat	25	1203	2024 年3 月 29 日
Introducing chat threads! Announcements new-feature , chat	33	7157	2024 年7 月 8 日
A chat channel doesn’t show nor save messages Bug chat	13	803	2023 年7 月 12 日
Slow loading on private-message-topic-tracking-state.json Bug	33	1601	2021 年10 月 18 日
Real-time updating of topics freezes under high activity Support	89	7426	2022 年2 月 26 日

Chat Pitchfork 超时：回复静默创建线程，自动追踪随时间膨胀

相关话题