sam
21. April 2026 um 07:01
2
Was für ein großartiger Artikel!
Ich habe mich damit beschäftigt. Bei meinen Tests habe ich eine 50-fache Leistungssteigerung erreicht mit:
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
Wir brauchen wahrscheinlich noch viel mehr Tests, aber der Vorteil ist, dass ein kleiner Fehler hier nur bedeutet, dass die Zahlen etwas abweichen. Wir müssen die Last reduzieren, wir haben keine andere Wahl.
2 „Gefällt mir“