mirror of
https://github.com/chatwoot/chatwoot.git
synced 2026-06-04 21:02:35 +08:00
fed32d5964
## Description Fix a Postgres planner trap on the "Pending Response: Longest first" sort that causes the conversation list to hang on busy accounts. The current `sort_on_waiting_since` generates query with `ORDER BY waiting_since ASC NULLS LAST, created_at ASC`. That order-by is exactly the shape of the single-column `index_conversations_on_waiting_since` btree, so the planner picks a forward index walk thinking `LIMIT 25` will stop early. In practice the per-account matches are spread along the global waiting_since timeline, so the scan reads tens of millions of rows from other accounts and discards them via the filter before producing any results which in turn causes the requests to time out and the conversation list spinner never resolves. DESC direction and every other sort (`priority`, `created_at`, `last_activity_at`) are unaffected. They fall through to `conv_acid_inbid_stat_asgnid_idx` (account-scoped composite), which is the right index for this access pattern. This change leads the ORDER BY with the expression `(waiting_since IS NULL)`, which no column-only btree can satisfy. The planner falls back to the same account-scoped index used by every other sort, and sorts in memory. Same logical NULLS LAST output for both directions; no behavior change for users. Fixes CW-6965 ## Type of change - [x] Bug fix (non-breaking change which fixes an issue) - [ ] New feature (non-breaking change which adds functionality) - [ ] Breaking change (fix or feature that would cause existing functionality not to work as expected) - [ ] This change requires a documentation update ## How Has This Been Tested? - [x] Existing specs pass - [x] Added new specs to cover NULL case - [x] Verify results and order for old and new query in prod - [x] Tested in prod since staging data was not sufficient `EXPLAIN (ANALYZE, BUFFERS)` for the same query (status=0, ASC, LIMIT 25) on a representative production account, before vs after: | Metric | Before | After | | --- | --- | --- | | Execution time | 34,679 ms | 0.71 ms | | Rows discarded by filter | 36,906,962 | 0 | | Shared buffer hits | 12,699,924 | 11 | | Blocks read from disk | 851,226 | 112 | | I/O read time | 17,785 ms | 0.3 ms | | Pages dirtied / written | 98 / 31,043 | 0 / 0 | Verified on two production accounts: identical row IDs in identical order between the old and new ORDER BY for both ASC and DESC. A NULL-bucket regression spec was added covering ASC/DESC tail ordering when some conversations have a null `waiting_since`. Roughly `49,000×` faster on this query (34,679 ms → 0.71 ms), and trivially less I/O and buffer pressure on the cluster while it runs. ## Checklist: - [x] My code follows the style guidelines of this project - [x] I have performed a self-review of my code - [ ] I have commented on my code, particularly in hard-to-understand areas - [ ] I have made corresponding changes to the documentation - [x] My changes generate no new warnings - [x] I have added tests that prove my fix is effective or that my feature works - [x] New and existing unit tests pass locally with my changes - [ ] Any dependent changes have been merged and published in downstream modules Co-authored-by: Tanmay Deep Sharma <32020192+tds-1@users.noreply.github.com> |
||
|---|---|---|
| .. | ||
| channel | ||
| concerns | ||
| enterprise/audit | ||
| integrations | ||
| .keep | ||
| account_spec.rb | ||
| account_user_spec.rb | ||
| agent_bot_inbox_spec.rb | ||
| agent_bot_spec.rb | ||
| application_record_external_credentials_encryption_spec.rb | ||
| article_spec.rb | ||
| assignment_policy_spec.rb | ||
| attachment_spec.rb | ||
| automation_rule_spec.rb | ||
| campaign_spec.rb | ||
| category_spec.rb | ||
| contact_inbox_spec.rb | ||
| contact_spec.rb | ||
| conversation_participants_spec.rb | ||
| conversation_spec.rb | ||
| csat_survey_response_spec.rb | ||
| custom_attribute_definition_spec.rb | ||
| data_import_spec.rb | ||
| folder_spec.rb | ||
| inbox_member_spec.rb | ||
| inbox_spec.rb | ||
| installation_config_spec.rb | ||
| label_spec.rb | ||
| macro_spec.rb | ||
| mention_spec.rb | ||
| message_spec.rb | ||
| note_spec.rb | ||
| notification_setting_spec.rb | ||
| notification_spec.rb | ||
| platform_app_permissible_spec.rb | ||
| platform_app_spec.rb | ||
| portal_spec.rb | ||
| related_category_spec.rb | ||
| reporting_event_spec.rb | ||
| reporting_events_rollup_spec.rb | ||
| team_member_spec.rb | ||
| team_spec.rb | ||
| user_spec.rb | ||
| webhook_spec.rb | ||
| working_hour_spec.rb | ||