diff options
| author | Neil Alexander <neilalexander@users.noreply.github.com> | 2022-10-04 16:43:10 +0100 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2022-10-04 16:43:10 +0100 |
| commit | 21f88819858dec022dec5919d6db5554605a9b8b (patch) | |
| tree | d926394ee2bb0eefb119c4179f67681266c62175 | |
| parent | ae10aac456e90dc6a3ea56ca4aceb4a4e5aa8e04 (diff) | |
Add indexes that optimise `selectStateInRangeSQL` (#2764)
This gets rid of some expensive scans on `add_state_ids` and
`remove_state_ids`, turning them into much cheaper and faster index
scans instead.
| -rw-r--r-- | syncapi/storage/postgres/output_room_events_table.go | 2 | ||||
| -rw-r--r-- | syncapi/storage/sqlite3/output_room_events_table.go | 2 |
2 files changed, 4 insertions, 0 deletions
diff --git a/syncapi/storage/postgres/output_room_events_table.go b/syncapi/storage/postgres/output_room_events_table.go index cb092150..b562e680 100644 --- a/syncapi/storage/postgres/output_room_events_table.go +++ b/syncapi/storage/postgres/output_room_events_table.go @@ -76,6 +76,8 @@ CREATE INDEX IF NOT EXISTS syncapi_output_room_events_type_idx ON syncapi_output CREATE INDEX IF NOT EXISTS syncapi_output_room_events_sender_idx ON syncapi_output_room_events (sender); CREATE INDEX IF NOT EXISTS syncapi_output_room_events_room_id_idx ON syncapi_output_room_events (room_id); CREATE INDEX IF NOT EXISTS syncapi_output_room_events_exclude_from_sync_idx ON syncapi_output_room_events (exclude_from_sync); +CREATE INDEX IF NOT EXISTS syncapi_output_room_events_add_state_ids_idx ON syncapi_output_room_events ((add_state_ids IS NOT NULL)); +CREATE INDEX IF NOT EXISTS syncapi_output_room_events_remove_state_ids_idx ON syncapi_output_room_events ((remove_state_ids IS NOT NULL)); ` const insertEventSQL = "" + diff --git a/syncapi/storage/sqlite3/output_room_events_table.go b/syncapi/storage/sqlite3/output_room_events_table.go index 16594302..d6a674b9 100644 --- a/syncapi/storage/sqlite3/output_room_events_table.go +++ b/syncapi/storage/sqlite3/output_room_events_table.go @@ -55,6 +55,8 @@ CREATE INDEX IF NOT EXISTS syncapi_output_room_events_type_idx ON syncapi_output CREATE INDEX IF NOT EXISTS syncapi_output_room_events_sender_idx ON syncapi_output_room_events (sender); CREATE INDEX IF NOT EXISTS syncapi_output_room_events_room_id_idx ON syncapi_output_room_events (room_id); CREATE INDEX IF NOT EXISTS syncapi_output_room_events_exclude_from_sync_idx ON syncapi_output_room_events (exclude_from_sync); +CREATE INDEX IF NOT EXISTS syncapi_output_room_events_add_state_ids_idx ON syncapi_output_room_events ((add_state_ids IS NOT NULL)); +CREATE INDEX IF NOT EXISTS syncapi_output_room_events_remove_state_ids_idx ON syncapi_output_room_events ((remove_state_ids IS NOT NULL)); ` const insertEventSQL = "" + |