From 3fab58827017041168a769184469cff3722d6c38 Mon Sep 17 00:00:00 2001 From: Owen Jacobson Date: Wed, 23 Oct 2024 00:31:53 -0400 Subject: Make sure (most) queries avoid table scans. I've exempted inserts (they never scan in the first place), queries on `event_sequence` (at most one row), and the coalesce()s used for event replay (for now; these are obviously a performance risk area and need addressing). Method: ``` find .sqlx -name 'query-*.json' -exec jq -r '"explain query plan " + .query + ";"' {} + > explain.sql ``` Then go query by query through the resulting file. --- migrations/20241023040731_missing_indices.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 migrations/20241023040731_missing_indices.sql (limited to 'migrations') diff --git a/migrations/20241023040731_missing_indices.sql b/migrations/20241023040731_missing_indices.sql new file mode 100644 index 0000000..4bfd76d --- /dev/null +++ b/migrations/20241023040731_missing_indices.sql @@ -0,0 +1,17 @@ +create index message_deleted_deleted_at +on message_deleted (deleted_at); + +create index message_channel +on message(channel); + +create index channel_created_sequence +on channel (created_sequence); + +create index channel_created_at +on channel (created_at); + +create index token_last_used_at +on token (last_used_at); + +create index invite_issued_at +on invite (issued_at); -- cgit v1.2.3