Skip to content

Commit 9131d78

Browse files
johnstcnjohnstcn
committed
chore(coderd/database): optimize GetRunningPrebuiltWorkspaces
1 parent 02d1576 commit 9131d78

File tree

2 files changed

+104
-22
lines changed

2 files changed

+104
-22
lines changed

‎coderd/database/queries.sql.go

Lines changed: 52 additions & 11 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/prebuilds.sql

Lines changed: 52 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -49,18 +49,59 @@ WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a pre
4949
AND (t.id = sqlc.narg('template_id')::uuid OR sqlc.narg('template_id') IS NULL);
5050

5151
-- name: GetRunningPrebuiltWorkspaces :many
52+
WITH latest_prebuilds AS (
53+
SELECT
54+
latest_build.workspace_id,
55+
workspaces.name,
56+
workspaces.template_id,
57+
latest_build.template_version_id,
58+
latest_build.template_version_preset_id,
59+
latest_build.job_id,
60+
workspaces.created_at
61+
FROM workspaces
62+
JOIN LATERAL (
63+
SELECT
64+
workspace_builds.id,
65+
workspace_builds.workspace_id,
66+
workspace_builds.template_version_id,
67+
workspace_builds.job_id,
68+
workspace_builds.template_version_preset_id,
69+
workspace_builds.transition,
70+
workspace_builds.created_at,
71+
provisioner_jobs.job_status
72+
FROM workspace_builds
73+
JOIN provisioner_jobs ON provisioner_jobs.id = workspace_builds.job_id
74+
WHERE workspace_builds.workspace_id = workspaces.id
75+
AND workspace_builds.transition = 'start'::workspace_transition
76+
AND provisioner_jobs.job_status = 'succeeded'::provisioner_job_status
77+
ORDER BY workspace_builds.build_number DESC
78+
LIMIT 1
79+
) AS latest_build ON true
80+
WHERE workspaces.deleted = false
81+
AND workspaces.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID
82+
),
83+
ready_agents AS (
84+
SELECT
85+
latest_prebuilds.job_id,
86+
BOOL_AND(workspace_agents.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)::boolean AS ready
87+
FROM latest_prebuilds
88+
JOIN workspace_resources ON workspace_resources.job_id = latest_prebuilds.job_id
89+
JOIN workspace_agents ON workspace_agents.resource_id = workspace_resources.id
90+
WHERE workspace_agents.deleted = false
91+
AND workspace_agents.parent_id IS NULL
92+
GROUP BY latest_prebuilds.job_id
93+
)
5294
SELECT
53-
p.id,
54-
p.name,
55-
p.template_id,
56-
b.template_version_id,
57-
p.current_preset_id AS current_preset_id,
58-
p.ready,
59-
p.created_at
60-
FROM workspace_prebuilds p
61-
INNER JOIN workspace_latest_builds b ON b.workspace_id = p.id
62-
WHERE (b.transition = 'start'::workspace_transition
63-
AND b.job_status = 'succeeded'::provisioner_job_status);
95+
latest_prebuilds.workspace_id AS id,
96+
latest_prebuilds.name,
97+
latest_prebuilds.template_id,
98+
latest_prebuilds.template_version_id,
99+
latest_prebuilds.template_version_preset_id AS current_preset_id,
100+
COALESCE(ready_agents.ready, false)::boolean AS ready,
101+
latest_prebuilds.created_at
102+
FROM latest_prebuilds
103+
LEFT JOIN ready_agents ON ready_agents.job_id = latest_prebuilds.job_id
104+
ORDER BY latest_prebuilds.workspace_id ASC;
64105

65106
-- name: CountInProgressPrebuilds :many
66107
-- CountInProgressPrebuilds returns the number of in-progress prebuilds, grouped by preset ID and transition.

0 commit comments

Comments
 (0)