Skip to content

perf(coderd/database): expand only the returned page in GetWorkspaces - #31627

Open
kylecarbs wants to merge 1 commit into
perf/db-users-listfrom
perf/db-workspaces-list
Open

kylecarbs wants to merge 1 commit into
perf/db-users-listfrom
perf/db-workspaces-list

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

GetWorkspaces and GetAuthorizedWorkspaces filtered, sorted, and counted on the wide workspaces_expanded view, including its per-row ACL subqueries, for every matching workspace (about 189k rows here), only to return 25. The inflated cost estimate also triggered about 0.8 s of JIT compilation.

The filter, sort, and count now run on the base workspaces table plus users.username, and a new filtered_workspaces_page CTE joins workspaces_expanded for the returned page only. Filters, the RBAC placeholder, and output columns are unchanged, as are the generated row type and the scan in modelqueries.go. The template filter uses = ANY(ARRAY(SELECT ...)) so the planner uses workspace_template_id_idx instead of assuming 50% selectivity. GetDeploymentWorkspaceStats counts in one pass with FILTER instead of scanning a materialized CTE five times.

Query (warm) Before After
Admin, no filter (offset 0 / 500) 5139 / 5034 ms 3051 / 3021 ms
template:X (6k workspaces) 1012 ms 145 ms
status:running 4623 ms 3183 ms
Organization filter 3887 ms 2454 ms
deleted:true 1323 ms 346 ms
Regular user, RBAC filter only 172 ms 126 ms
GetDeploymentWorkspaceStats 1975 ms 1760 ms

Owner filters were already a few ms and are unchanged.

Review focus: this is the most-used list query.

  • Output was byte-identical in 44 scenarios: offsets, every filter type, sharing, has-agent, and the regular user with the real compiled RBAC filter.
  • Rows that tie on every sort key can come back in a different order. That order was already arbitrary.

Depends on #31626.

Decision log
  • The remaining about 3 s for the unfiltered and status:running lists is the latest build and job lookup for every visible workspace (about 189k index pairs, not parallelized), plus sorting by lowercased username and name.
  • Proposal, not implemented: a trigger-maintained latest-build table, one row per workspace. A prototype took the admin list from 3051 to 608 ms, status:running from 3183 to 543 ms, and deployment stats from 1760 to 86 ms. It touches the provisioner job lifecycle, so it should be its own change.

Generated by Coder Agents on behalf of @kylecarbs.

@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-834

The filter, sort, and count CTE read the wide workspaces_expanded view,
including per-row ACL subqueries, for every matching workspace. Filter
and sort on the base table and join workspaces_expanded for the returned
page only. The template filter uses ANY(ARRAY(...)) so the planner can use
workspace_template_id_idx.

GetDeploymentWorkspaceStats counts in one pass with FILTER instead of
scanning a materialized CTE five times.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-workspaces-list branch from 2215316 to b42a426 Compare November 9, 2026 21:28

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant