element-hq / element-hq/synapse

Optimise push action processing

Open
#13,448 0 comments 0 reactions 0 assignees View on GitHub
A-Performance A-Push T-Task
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.