fix(coderd/database): add index on workspace_app_statuses.app_id

mafredri · mafredri · commit 836370c86890 · 2025-12-04T12:26:30.000Z
The queries GetWorkspaceAppStatusesByAppIDs and GetLatestWorkspaceAppStatusByAppID both filter by app_id but there was no index to support this, causing sequential scans. Refs https://www.notion.so/coderhq/2k-Task-Status-Updates-retest-2bfd579be59280ca8f86eb376d05e0fa
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000402_workspace_app_statuses_app_id_index.down.sql b/coderd/database/migrations/000402_workspace_app_statuses_app_id_index.down.sql
@@ -0,0 +1 @@
+DROP INDEX IF EXISTS workspace_app_statuses_app_id_idx;
diff --git a/coderd/database/migrations/000402_workspace_app_statuses_app_id_index.up.sql b/coderd/database/migrations/000402_workspace_app_statuses_app_id_index.up.sql
@@ -0,0 +1 @@
+CREATE INDEX workspace_app_statuses_app_id_idx ON workspace_app_statuses (app_id);

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1 @@`
	`1`	`+DROP INDEX IF EXISTS workspace_app_statuses_app_id_idx;`
Original file line number	Diff line number	Diff line change
`@@ -0,0 +1 @@`
	`1`	`+CREATE INDEX workspace_app_statuses_app_id_idx ON workspace_app_statuses (app_id);`