perf(coderd/database): optimize GetAPIKeysLastUsedAfter (#19725)

mafredri · web-flow · commit 21402c7aaa02 · 2025-09-08T13:13:12.000+03:00
This change adds a support-index for `GetAPIKeysLastUsedAfter`.

On dogfood (24h): called 4.4k times, 380ms average.

Change for tested time range: `170ms` -&gt; `3.3ms`.
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000365_add_index_for_getapikeyslastusedafter.down.sql b/coderd/database/migrations/000365_add_index_for_getapikeyslastusedafter.down.sql
@@ -0,0 +1 @@
+DROP INDEX api_keys_last_used_idx;
diff --git a/coderd/database/migrations/000365_add_index_for_getapikeyslastusedafter.up.sql b/coderd/database/migrations/000365_add_index_for_getapikeyslastusedafter.up.sql
@@ -0,0 +1,2 @@
+CREATE INDEX api_keys_last_used_idx ON api_keys (last_used DESC);
+COMMENT ON INDEX api_keys_last_used_idx IS 'Index for optimizing api_keys queries filtering by last_used';

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,2 @@`
	`1`	`+CREATE INDEX api_keys_last_used_idx ON api_keys (last_used DESC);`
	`2`	`+COMMENT ON INDEX api_keys_last_used_idx IS 'Index for optimizing api_keys queries filtering by last_used';`