This prevents a scan over the entireworkspace_build table by removing ajoin. This is still imperfect as we are still scanning over the number of builds for the workspaces in the arguments. Ideally we would have some index or something precomputed. Then we could skip scanning over the builds for the correct workspaces that are not the latest.

So this could be better, but this is a quick & cheap order of magnitude win.

Before: (69k rows scanned)

https://explain.dalibo.com/plan/d91d553ge694a18c

After: (2,500 rows scanned)

https://explain.dalibo.com/plan/6ec4eehde8c381a7#plan

Query used hits 2 workspaces with >1k builds.

EXPLAIN ANALYZESELECTDISTINCT ON (workspace_id)*FROMworkspace_build_with_user AS workspace_buildsWHEREworkspace_id = ANY(ARRAY['bb601d85-5354-4b72-a00a-a9293dcaf9fb', '192acfe6-339b-4745-8cd5-1b44a4e76348'] :: uuid[])ORDER BYworkspace_id, build_number DESC -- latest first;

Other notes

I tried to mess around with some indexes like: (plan)

CREATE INDEX idx_ws_build_latestON workspace_builds (workspace_id, build_number DESC)

This did not seem to help over theexisting index without theDESC. (plan)

ALTER TABLE ONLY workspace_builds    ADD CONSTRAINT workspace_builds_workspace_id_build_number_key UNIQUE (workspace_id, build_number);

Both indexs still scan over all the rows (105 in the plans).

Some latest evidence on dev

chore: improve performance of 'GetLatestWorkspaceBuildsByWorkspaceIDs'

b02dbc7

github-actionsbot assignedEmyrk

Aug 20, 2025

Emyrk added2 commits

August 20, 2025 12:47

add unit test

da5e441

make gen

cf13efd

Emyrk marked this pull request as ready for review

August 20, 2025 19:35

Copy link

Contributor

cstyan commentedAug 25, 2025

The query change makes sense.

I noticed that we're not enforcing a value to the limit parameter and we're also not logging (and I can't find tracing spans that have this info either) whether a limit value was passed, how many workspace IDs were passed, which pagination offset was passed, etc. All of which would help us identify whether the P99 is related to any of those.

Copy link

MemberAuthor

Emyrk commentedAug 25, 2025

I noticed that we're not enforcing a value to the limit parameter and we're also not logging (and I can't find tracing spans that have this info either) whether a limit value was passed, how many workspace IDs were passed, which pagination offset was passed, etc. All of which would help us identify whether the P99 is related to any of those.

We probably should have a sensible limit

Copy link

Member

johnstcn commentedAug 26, 2025

I noticed that we're not enforcing a value to the limit parameter and we're also not logging (and I can't find tracing spans that have this info either) whether a limit value was passed, how many workspace IDs were passed, which pagination offset was passed, etc. All of which would help us identify whether the P99 is related to any of those.
We probably should have a sensible limit

It's hard to know where to put the upper limit tbh without some real-world data. Let's revisit this idea when we do.