sam
Avril 21, 2026, 7:01
2
Quel article incroyable !
J’ai commencé à l’examiner. Lors de mes tests, j’ai obtenu une amélioration des performances de 50 fois avec :
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
Nous aurons probablement besoin de beaucoup plus de tests, mais l’avantage est qu’un petit bug ici ne signifiera qu’une légère imprécision dans les comptes. Nous devons absolument réduire la charge, sans autre choix.
2 « J'aime »