sam
Abril 21, 2026, 7:01am
2
Que relatório incrível!
Comecei a analisar isso e, nos meus testes, obtive uma melhoria de desempenho de 50x com:
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
Provavelmente precisaremos de muito mais testes, mas o lado positivo é que um pequeno bug aqui significará apenas que as contagens estarão um pouco fora do esperado. Precisamos reduzir a carga, não temos outra opção.
2 curtidas