Tuesday, March 5, 2024
1 change · 17.0
Enhancements to existing features
This update adds a database index to the mail system to significantly speed up email routing and alias searches. The optimization reduces processing time from 696 milliseconds to just 1 millisecond for typical email operations, resulting in a dramatic improvement in system performance when handling incoming emails.
Original PR description
## Description Since https://github.com/odoo/odoo/pull/76734, the new field `alias_full_name` is used in addition to `alias_name` to search on `mail.alias` and route emails in `message_route`. It's…
## Description
Since https://github.com/odoo/odoo/pull/76734, the new field `alias_full_name` is used in addition to `alias_name` to search on `mail.alias` and route emails in `message_route`. It's also used as a search criteria in `_search_alias_email` on `mail.alias.mixin. optional`. The issue is that the table `mail.alias` can grow quite large, and only the old field `alias_name` has an index on it, which may not be selective enough, forcing Seq.Scans on the table with possibly millions of records. The impact is noticeable on `message_route` which is a hot path for processing incoming emails.
Adding an index on `alias_full_name` allows PostgreSQL to do an bitmap OR scan on the two indexes, considerably speeding up search criteria that are a disjunction between `alias_name` and `alias_full_name`.
## Benchmark
For domain
```python
[
'&',
('alias_model_id', '!=', reply_model_id),
'|',
('alias_full_name', 'in', email_to_list),
'&', ('alias_name', 'in', email_to_localparts), ('alias_incoming_local', '=', True),
]
```
with test arguments, on a `mail.alias` table containing over 2M records.
| | Before | After |
|-----------|--------|-------|
| Timing | 696ms | 1ms |
| Buffer IO | 790MB | 48KB |
Specially impactful as those gains needs to be multiplied by the frequency of the searches.
## Reference
task-3724844
---
I confirm I have signed the CLA and read the PR guidelines at www.odoo.com/submit-pr