element-hq / element-hq/synapse
Optimise push action processing
- Dominant language
- Python
- Stars
- 4.6k
- Forks
- 600
- Avg merge
- 5d 22h
- Merged PRs (30d)
- 51
Description
This issue has been migrated from [#13448](https://github.com/matrix-org/synapse/issues/13448).
---
[This query](https://github.com/matrix-org/synapse/blob/a648a06d52715d0d4ad1ec72d042df1b3fd1be71/synapse/storage/databases/main/event_push_actions.py#L381) is responsible for a significant amount of load on our instance and I'd like to optimise it :)
My first (currently only :)) suggestion is to store the stream ordering of an event in the `receipts_linearized` table (probably as `event_stream_ordering`). I believe (will confirm at a later time) that this means both queries can be combined into a much simpler one along the lines of:
```sql
SELECT ep.event_id, ep.room_id, ep.stream_ordering, ep.actions,
ep.highlight
FROM event_push_actions AS ep
LEFT JOIN receipts_linearized AS rl USING (room_id, event_id)
WHERE
ep.stream_ordering > rl.stream_ordering
AND ep.user_id = ?
AND ep.stream_ordering > ?
AND ep.stream_ordering <= ?
AND ep.notif = 1
ORDER BY ep.stream_ordering ASC LIMIT ?
```
This would also optimise a bunch of other queries such as [here](https://github.com/matrix-org/synapse/blob/a648a06d52715d0d4ad1ec72d042df1b3fd1be71/synapse/storage/databases/main/receipts.py#L642) and [here](https://github.com/matrix-org/synapse/blob/a648a06d52715d0d4ad1ec72d042df1b3fd1be71/synapse/storage/databases/main/event_push_actions.py#L874).
Contributor guide
Assessment
This issue has not been assessed yet.