Skip to content

Commit 0d2d188

Browse files
deansheatherdeansheather
committed
chore: optimize workspace_latest_builds view query
1 parent 7f056da commit 0d2d188

File tree

3 files changed

+186
-32
lines changed

3 files changed

+186
-32
lines changed

‎coderd/database/dump.sql

+44-32
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000322_workspace_latest_builds_optimization.down.sql

+58
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
DROP VIEW workspace_prebuilds;
2+
DROP VIEW workspace_latest_builds;
3+
4+
-- Revert to previous version from 000314_prebuilds.up.sql
5+
CREATE VIEW workspace_latest_builds AS
6+
SELECT DISTINCT ON (workspace_id)
7+
wb.id,
8+
wb.workspace_id,
9+
wb.template_version_id,
10+
wb.job_id,
11+
wb.template_version_preset_id,
12+
wb.transition,
13+
wb.created_at,
14+
pj.job_status
15+
FROM workspace_builds wb
16+
INNER JOIN provisioner_jobs pj ON wb.job_id = pj.id
17+
ORDER BY wb.workspace_id, wb.build_number DESC;
18+
19+
-- Recreate the depedent views
20+
CREATE VIEW workspace_prebuilds AS
21+
WITH all_prebuilds AS (
22+
SELECT w.id,
23+
w.name,
24+
w.template_id,
25+
w.created_at
26+
FROM workspaces w
27+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
28+
), workspaces_with_latest_presets AS (
29+
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
30+
workspace_builds.template_version_preset_id
31+
FROM workspace_builds
32+
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
33+
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
34+
), workspaces_with_agents_status AS (
35+
SELECT w.id AS workspace_id,
36+
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
37+
FROM (((workspaces w
38+
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
39+
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
40+
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
41+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
42+
GROUP BY w.id
43+
), current_presets AS (
44+
SELECT w.id AS prebuild_id,
45+
wlp.template_version_preset_id
46+
FROM (workspaces w
47+
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
48+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
49+
)
50+
SELECT p.id,
51+
p.name,
52+
p.template_id,
53+
p.created_at,
54+
COALESCE(a.ready, false) AS ready,
55+
cp.template_version_preset_id AS current_preset_id
56+
FROM ((all_prebuilds p
57+
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
58+
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));

‎coderd/database/migrations/000322_workspace_latest_builds_optimization.up.sql

+84
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
-- Drop the dependent views
2+
DROP VIEW workspace_prebuilds;
3+
-- Previously created in 000314_prebuilds.up.sql
4+
DROP VIEW workspace_latest_builds;
5+
6+
-- The previous version of this view had two sequential scans on two very large
7+
-- tables. This version optimized it by using index scans (via a lateral join)
8+
-- AND avoiding selecting builds from deleted workspaces.
9+
CREATE VIEW workspace_latest_builds AS
10+
SELECT
11+
latest_build.id,
12+
latest_build.workspace_id,
13+
latest_build.template_version_id,
14+
latest_build.job_id,
15+
latest_build.template_version_preset_id,
16+
latest_build.transition,
17+
latest_build.created_at,
18+
latest_build.job_status
19+
FROM workspaces
20+
LEFT JOIN LATERAL (
21+
SELECT
22+
workspace_builds.id AS id,
23+
workspace_builds.workspace_id AS workspace_id,
24+
workspace_builds.template_version_id AS template_version_id,
25+
workspace_builds.job_id AS job_id,
26+
workspace_builds.template_version_preset_id AS template_version_preset_id,
27+
workspace_builds.transition AS transition,
28+
workspace_builds.created_at AS created_at,
29+
provisioner_jobs.job_status AS job_status
30+
FROM
31+
workspace_builds
32+
JOIN
33+
provisioner_jobs
34+
ON
35+
provisioner_jobs.id = workspace_builds.job_id
36+
WHERE
37+
workspace_builds.workspace_id = workspaces.id
38+
ORDER BY
39+
build_number DESC
40+
LIMIT
41+
1
42+
) latest_build ON TRUE
43+
WHERE workspaces.deleted = false;
44+
45+
-- Recreate the depedent views
46+
CREATE VIEW workspace_prebuilds AS
47+
WITH all_prebuilds AS (
48+
SELECT w.id,
49+
w.name,
50+
w.template_id,
51+
w.created_at
52+
FROM workspaces w
53+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
54+
), workspaces_with_latest_presets AS (
55+
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
56+
workspace_builds.template_version_preset_id
57+
FROM workspace_builds
58+
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
59+
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
60+
), workspaces_with_agents_status AS (
61+
SELECT w.id AS workspace_id,
62+
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
63+
FROM (((workspaces w
64+
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
65+
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
66+
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
67+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
68+
GROUP BY w.id
69+
), current_presets AS (
70+
SELECT w.id AS prebuild_id,
71+
wlp.template_version_preset_id
72+
FROM (workspaces w
73+
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
74+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
75+
)
76+
SELECT p.id,
77+
p.name,
78+
p.template_id,
79+
p.created_at,
80+
COALESCE(a.ready, false) AS ready,
81+
cp.template_version_preset_id AS current_preset_id
82+
FROM ((all_prebuilds p
83+
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
84+
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));

0 commit comments

Comments
 (0)