mirror of
https://github.com/lingble/chatwoot.git
synced 2025-11-02 03:57:52 +00:00
462ab5241c
Database CPU utilization was spiking due to expensive notification COUNT
queries. Analysis revealed two critical issues:
1. Missing database index: Notification count queries were performing
table scans without proper indexing
2. Duplicate WHERE clauses: SQL queries contained redundant read_at IS
NULL conditions, causing unnecessary query complexity
### Root Cause Analysis
The expensive queries were:
```
-- 41.61 calls/sec with duplicate condition
SELECT COUNT(*) FROM "notifications"
WHERE "notifications"."user_id" = $1
AND "notifications"."account_id" = $2
AND "notifications"."snoozed_until" IS NULL
AND "notifications"."read_at" IS NULL
AND "notifications"."read_at" IS NULL -- Duplicate!
```
This was caused by a logic error in NotificationFinder#unread_count
introduced in commit cd06b2b33 (PR #8907). The method assumed
@notifications contained all notifications, but @notifications was
already filtered to unread notifications in most cases.
### The Default Query Flow:
1. Frontend calls: NotificationsAPI.getUnreadCount() →
/notifications/unread_count
2. No parameters sent, so params = {}
3. NotificationFinder setup:
- find_all_notifications: WHERE user_id = ? AND account_id = ?
- filter_snoozed_notifications: WHERE snoozed_until IS NULL
- filter_read_notifications: WHERE read_at IS NULL (because
type_included?('read') is false)
4. unread_count called: Adds another WHERE read_at IS NULL
----
### Solution
1. Added Missing Database Index
- Index: (user_id, account_id, snoozed_until, read_at)
2. Fixed Duplicate WHERE Clause Logic
12 lines
486 B
Ruby
12 lines
486 B
Ruby
class AddNotificationsPerformanceIndex < ActiveRecord::Migration[7.1]
|
|
disable_ddl_transaction!
|
|
|
|
def change
|
|
# Add composite index to optimize notification count queries
|
|
# This covers the common query pattern: WHERE user_id = ? AND account_id = ? AND snoozed_until IS NULL AND read_at IS NULL
|
|
add_index :notifications, [:user_id, :account_id, :snoozed_until, :read_at],
|
|
name: 'idx_notifications_performance',
|
|
algorithm: :concurrently
|
|
end
|
|
end
|