perf(coderd/database): add index on workspace_app_statuses.app_id (#21099)

mafredri · web-flow · commit cfdd4a9b88ae · 2025-12-04T17:56:13.000+02:00
diff --git a/coderd/aitasks_test.go b/coderd/aitasks_test.go
@@ -1680,8 +1680,8 @@ func TestTasksNotification(t *testing.T) {
 			require.NoError(t, err)
 			require.Len(t, workspaceAgent.Apps, 1)
 			require.GreaterOrEqual(t, len(workspaceAgent.Apps[0].Statuses), 1)
-			latestStatusIndex := len(workspaceAgent.Apps[0].Statuses) - 1
-			require.Equal(t, tc.newAppStatus, workspaceAgent.Apps[0].Statuses[latestStatusIndex].State)
+			// Statuses are ordered by created_at DESC, so the first element is the latest.
+			require.Equal(t, tc.newAppStatus, workspaceAgent.Apps[0].Statuses[0].State)
 
 			if tc.isNotificationSent {
 				// Then: A notification is sent to the workspace owner (memberUser)
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, created_at DESC);
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaceapps.sql b/coderd/database/queries/workspaceapps.sql
@@ -71,7 +71,8 @@ VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
 RETURNING *;
 
 -- name: GetWorkspaceAppStatusesByAppIDs :many
-SELECT * FROM workspace_app_statuses WHERE app_id = ANY(@ids :: uuid [ ]);
+SELECT * FROM workspace_app_statuses WHERE app_id = ANY(@ids :: uuid [ ])
+ORDER BY created_at DESC, id DESC;
 
 -- name: GetLatestWorkspaceAppStatusByAppID :one
 SELECT *

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, created_at DESC);`