sam
21.Апрель.2026 07:01:37
2
Какой потрясающий обзор!
Я начал изучать это, и в моих тестах я получил улучшение производительности в 50 раз с помощью:
main ← chat-perf
opened 06:57AM - 21 Apr 26 UTC
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
Нам, вероятно, потребуется ещё много тестирования, но плюс в том, что даже если здесь возникнет небольшая ошибка, это лишь немного исказит подсчёты — нам нужно сбрасывать нагрузку, другого выбора у нас нет.
2 лайка