Skip to content

Commit ef745c0

Browse files
deansheathermafredridannykopping
authored
chore: optimize workspace_latest_builds view query (#17789)
Avoids two sequential scans of massive tables (`workspace_builds`, `provisioner_jobs`) and uses index scans instead. This new view largely replicates our already optimized query `GetWorkspaces` to fetch the latest build. The original query and the new query were compared against the dogfood database to ensure they return the exact same data in the exact same order (minus the new `workspaces.deleted = false` filter to improve performance even more). The performance is massively improved even without the `workspaces.deleted = false` filter, but it was added to improve it even more. Note: these query times are probably inflated due to high database load on our dogfood environment that this intends to partially resolve. Before: 2,139ms ([explain](https://explain.dalibo.com/plan/997e4fch241b46e6)) After: 33ms ([explain](https://explain.dalibo.com/plan/c888dc223870f181)) Co-authored-by: Cian Johnston <cian@coder.com> --------- Signed-off-by: Danny Kopping <dannykopping@gmail.com> Co-authored-by: Mathias Fredriksson <mafredri@gmail.com> Co-authored-by: Danny Kopping <dannykopping@gmail.com>
1 parent b2a1de9 commit ef745c0

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/000323_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/000323_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)