bug: GetLatestWorkspaceBuilds creates lots of DB load

[spikecurtis]

In our dogfood deployment over 5 days in early June:

Avg Execution Time: 349.5ms
Num Times Called: 25805
Avg Rows: 4677

https://www.notion.so/coderhq/GetLatestWorkspaceBuilds-209d579be5928025a686ec5a4a2defd2?source=copy_link

[mafredri]

It seems the linked explain analyze is for GetLatestWorkspaceBuildsByWorkspaceIDs rather than GetLatestWorkspaceBuilds, which makes sense to me. The GetLatestWorkspaceBuilds is so simple there's barely any chances for optimization.

There's only simple fix we can do that should greatly reduce the cost of the query:

diff --git coderd/database/queries/workspacebuilds.sql coderd/database/queries/workspacebuilds.sql
index be76b6642..85218e2a4 100644
--- coderd/database/queries/workspacebuilds.sql
+++ coderd/database/queries/workspacebuilds.sql
@@ -81,7 +81,7 @@ FROM (
     SELECT
         workspace_id, MAX(build_number) as max_build_number
     FROM
-		workspace_build_with_user AS workspace_builds
+		workspace_builds
     WHERE
         workspace_id = ANY(@ids :: uuid [ ])
     GROUP BY

This needs to be validated, but it should work on the premise that we start by creating a filter from a simple table (workspace_builds) rather than a view that needs to preform a join (workspace_build_with_user). This also allows an index scan using (workspace_id, build_number) reducing the number of workspace builds. As can be seen in the explain, the only index scan is used on the users table, which is unfortunate since that won't reduce a single row in the workspace builds.

[spikecurtis]

It seems the linked explain analyze is for GetLatestWorkspaceBuildsByWorkspaceIDs rather than GetLatestWorkspaceBuilds, which makes sense to me.

I'm not sure I follow. If you click to the Query tab it should show which query is being analyzed. When I follow the link I see the query for GetLatestWorkspaceBuilds

[mafredri]

I tested this change and it seems PostgreSQL is already doing an OK job on the query optimizer side to avoid needless work, so the proposed change isn't very helpful.

One option that I could imagine helping is modifying the index on the workspace_builds table.

Right now we have:

CONSTRAINT workspace_builds_workspace_id_build_number_key UNIQUE (workspace_id, build_number);

This does automatically create a backing index, but build_number will be sorted ASC which is not the common use case.

The following index would be much better suited:

CREATE INDEX CONCURRENTLY idx_workspace_builds_latest 
ON workspace_builds (workspace_id, build_number DESC);

Technically I guess it could be a unique index as well but perhaps not needed since we already have the constraint.

With the index, there's an opportunity for rewriting the query like this:

SELECT DISTINCT ON (workspace_id)
	wb.*
FROM
	workspace_build_with_user wb
WHERE
	workspace_id = ANY(@ids :: uuid[])
ORDER BY
	workspace_id, build_number DESC;

(But without the index, the cost of the order by may be too high.)

[mafredri]

@spikecurtis in Notion there's a link to an explain, that one is not for GetLatestWorkspaceBuilds.

Also, the link to Google Cloud console doesn't show me anything.

[mafredri]

@spikecurtis actually never mind, I mistook the query for another one, not sure which one tbh 😅. In any case, fortunately I've been working with both GetLatestWorkspaceBuilds and GetLatestWorkspaceBuildsByWorkspaceIDs as they are identical except for the one additional filter.

[mafredri]

Looking at the use-case (only one use, in prometheusmetrics.Workspaces), there are probably other optimizations that could be done as well. It seems deleted workspaces are included and I question the benefit of tracking the job status of the latest build of a deleted workspace. 🤔

[mafredri]

After some testing I'm ready to declare this query as not optimizable. I've tried targeted indexes, but in most query variations they won't be used. In other query variations, we pay the price in some other way (i.e. disk reads).

To improve the performance of GetLatestWorkspaceBuilds we would have to make one of the following changes:

1. Update the database structure to create a more efficient method checking the latest build than having to select MAX(build_number) (e.g. WHERE latest = TRUE, with subsequent handling in provisioner)
2. Reduce the dataset for GetLatestWorkspaceBuilds -- does it actually need to return deleted workspaces?

When filtering out deleted workspaces for instance, I managed to cut query execution ~in half, but that was with a single naive attempt.

@spikecurtis I'm closing this for now, please reopen if you're unhappy with the result.

(It's also very likely that fixing some other query or behavior will get rid of the execution time spikes for this one as it seems correlated to table locking.)

[spikecurtis]

The scope of this issue is to reduce the DB load, not narrowly to optimize a query.

We need to look more broadly at the problem if necessary, including examining the use case for this query. It's not really acceptable that one of our top 10 DB load-makers is just pushing some prometheus metrics. E.g. can we get the data for the metrics in some other, less expensive way?