Skip to content

Commit 3e78ee8

Browse files
johnstcnjohnstcn
committed
chore(coderd/database): optimize GetRunningPrebuiltWorkspaces
1 parent 48bb534 commit 3e78ee8

File tree

2 files changed

+151
-22
lines changed

2 files changed

+151
-22
lines changed

‎coderd/database/queries.sql.go

Lines changed: 75 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: 76 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -49,18 +49,83 @@ 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
53+
latest_prebuilds
54+
AS (
55+
SELECT
56+
workspaces.name,
57+
workspaces.template_id,
58+
latest_build.id,
59+
latest_build.workspace_id,
60+
latest_build.template_version_id,
61+
latest_build.job_id,
62+
latest_build.template_version_preset_id,
63+
latest_build.created_at
64+
FROM
65+
workspaces
66+
LEFT JOIN LATERAL (
67+
SELECT
68+
workspace_builds.id,
69+
workspace_builds.workspace_id,
70+
workspace_builds.template_version_id,
71+
workspace_builds.job_id,
72+
workspace_builds.template_version_preset_id,
73+
workspace_builds.transition,
74+
workspace_builds.created_at,
75+
provisioner_jobs.job_status
76+
FROM
77+
workspace_builds
78+
JOIN provisioner_jobs ON
79+
provisioner_jobs.id
80+
= workspace_builds.job_id
81+
WHERE
82+
workspace_builds.workspace_id
83+
= workspaces.id
84+
ORDER BY
85+
workspace_builds.build_number
86+
DESC
87+
LIMIT
88+
1
89+
)
90+
AS latest_build ON true
91+
WHERE
92+
workspaces.deleted = false
93+
AND workspaces.owner_id
94+
= 'c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID
95+
AND latest_build.transition
96+
= 'start'::workspace_transition
97+
AND latest_build.job_status
98+
= 'succeeded'::provisioner_job_status
99+
),
100+
agent_readiness
101+
AS (
102+
SELECT
103+
latest_prebuilds.workspace_id AS workspace_id,
104+
BOOL_AND(workspace_agents.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)::boolean AS ready
105+
FROM
106+
latest_prebuilds
107+
LEFT JOIN workspace_resources ON
108+
workspace_resources.job_id = latest_prebuilds.job_id
109+
LEFT JOIN workspace_agents ON
110+
workspace_agents.resource_id = workspace_resources.id
111+
GROUP BY
112+
latest_prebuilds.workspace_id
113+
)
52114
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);
115+
latest_prebuilds.id,
116+
latest_prebuilds.name,
117+
latest_prebuilds.template_id,
118+
latest_prebuilds.template_version_id,
119+
latest_prebuilds.template_version_preset_id AS current_preset_id,
120+
agent_readiness.ready,
121+
latest_prebuilds.created_at
122+
FROM
123+
latest_prebuilds
124+
JOIN agent_readiness ON
125+
agent_readiness.workspace_id = latest_prebuilds.workspace_id
126+
WHERE
127+
agent_readiness.ready
128+
;
64129

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

0 commit comments

Comments
 (0)