From 2be43560ca7d81601e6763c9fcb8b72ecd74009b Mon Sep 17 00:00:00 2001 From: PiotrKozimor <37144818+PiotrKozimor@users.noreply.github.com> Date: Tue, 30 Aug 2022 15:47:54 +0200 Subject: Index on `syncapi_send_to_device` table (#2684) Introduced index improves select query performance. Example execution time of `selectSendToDeviceMessagesSQL` query dropped from 80 ms to 15 ms. No sytest modifications are required. ### Pull Request Checklist * [x] I have added added tests for PR _or_ I have justified why this PR doesn't need tests. * [x] Pull request includes a [sign off](https://github.com/matrix-org/dendrite/blob/main/docs/CONTRIBUTING.md#sign-off) Signed-off-by: `Piotr Kozimor <p1996k@gmail.com>` --- syncapi/storage/postgres/send_to_device_table.go | 2 ++ 1 file changed, 2 insertions(+) (limited to 'syncapi/storage/postgres') diff --git a/syncapi/storage/postgres/send_to_device_table.go b/syncapi/storage/postgres/send_to_device_table.go index fd0c1c56..6ab1f0f4 100644 --- a/syncapi/storage/postgres/send_to_device_table.go +++ b/syncapi/storage/postgres/send_to_device_table.go @@ -41,6 +41,8 @@ CREATE TABLE IF NOT EXISTS syncapi_send_to_device ( -- The event content JSON. content TEXT NOT NULL ); + +CREATE INDEX IF NOT EXISTS syncapi_send_to_device_user_id_device_id_idx ON syncapi_send_to_device(user_id, device_id); ` const insertSendToDeviceMessageSQL = ` -- cgit v1.2.3