sam
21 أبريل 2026، 7:01ص
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)