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

sam · 2026年四月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	24	1562	2024年三月28日
Introducing chat threads! Announcements chat , new-feature	31	7893	2024年七月8日
A chat channel doesn’t show nor save messages Bug chat	12	921	2023年四月18日
Real-time updating of topics freezes under high activity Support	89	8663	2022年二月26日
Slow loading on private-message-topic-tracking-state.json Bug	32	2107	2021年十月4日

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

相關主題