Skip to content

Commit 9811abe

Browse files
deansheatherdeansheather
committed
chore: optimize workspace_latest_builds view query
1 parent 7f056da commit 9811abe

File tree

3 files changed

+188
-32
lines changed

3 files changed

+188
-32
lines changed

‎coderd/database/dump.sql

+45-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 dependent 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

+85
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,85 @@
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+
ORDER BY workspaces.id ASC;
45+
46+
-- Recreate the dependent views
47+
CREATE VIEW workspace_prebuilds AS
48+
WITH all_prebuilds AS (
49+
SELECT w.id,
50+
w.name,
51+
w.template_id,
52+
w.created_at
53+
FROM workspaces w
54+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
55+
), workspaces_with_latest_presets AS (
56+
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
57+
workspace_builds.template_version_preset_id
58+
FROM workspace_builds
59+
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
60+
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
61+
), workspaces_with_agents_status AS (
62+
SELECT w.id AS workspace_id,
63+
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
64+
FROM (((workspaces w
65+
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
66+
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
67+
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
68+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
69+
GROUP BY w.id
70+
), current_presets AS (
71+
SELECT w.id AS prebuild_id,
72+
wlp.template_version_preset_id
73+
FROM (workspaces w
74+
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
75+
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
76+
)
77+
SELECT p.id,
78+
p.name,
79+
p.template_id,
80+
p.created_at,
81+
COALESCE(a.ready, false) AS ready,
82+
cp.template_version_preset_id AS current_preset_id
83+
FROM ((all_prebuilds p
84+
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
85+
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));

0 commit comments

Comments
 (0)