mirror of
https://gh.wpcy.net/https://github.com/discourse/discourse.git
synced 2026-05-27 23:52:59 +08:00
83bed592e2
Rewrites the channel and thread unread queries to use a CTE that picks the candidate 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 --------- Co-authored-by: Martin Brennan <martin@discourse.org> |
||
|---|---|---|
| .. | ||
| channel_memberships_query.rb | ||
| channel_unreads_query.rb | ||
| messages_query.rb | ||
| thread_participant_query.rb | ||
| thread_unreads_query.rb | ||
| tracking_state_report_query.rb | ||
| users_from_usernames_and_groups_query.rb | ||
