Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitef745c0

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 largelyreplicates our already optimized query `GetWorkspaces` to fetch thelatest build.The original query and the new query were compared against the dogfooddatabase to ensure they return the exact same data in the exact sameorder (minus the new `workspaces.deleted = false` filter to improveperformance even more). The performance is massively improved evenwithout the `workspaces.deleted = false` filter, but it was added toimprove it even more.Note: these query times are probably inflated due to high database loadon 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 parentb2a1de9 commitef745c0

File tree

3 files changed

+188
-32
lines changed

3 files changed

+188
-32
lines changed

‎coderd/database/dump.sql

Lines changed: 45 additions & 32 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.
Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
DROPVIEW workspace_prebuilds;
2+
DROPVIEW workspace_latest_builds;
3+
4+
-- Revert to previous version from 000314_prebuilds.up.sql
5+
CREATEVIEWworkspace_latest_buildsAS
6+
SELECT DISTINCTON (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 pjONwb.job_id=pj.id
17+
ORDER BYwb.workspace_id,wb.build_numberDESC;
18+
19+
-- Recreate the dependent views
20+
CREATEVIEWworkspace_prebuildsAS
21+
WITH all_prebuildsAS (
22+
SELECTw.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_presetsAS (
29+
SELECT DISTINCTON (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_idIS NOT NULL)
33+
ORDER BYworkspace_builds.workspace_id,workspace_builds.build_numberDESC
34+
), workspaces_with_agents_statusAS (
35+
SELECTw.idAS workspace_id,
36+
bool_and((wa.lifecycle_state='ready'::workspace_agent_lifecycle_state))AS ready
37+
FROM (((workspaces w
38+
JOIN workspace_latest_builds wlbON ((wlb.workspace_id=w.id)))
39+
JOIN workspace_resources wrON ((wr.job_id=wlb.job_id)))
40+
JOIN workspace_agents waON ((wa.resource_id=wr.id)))
41+
WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
42+
GROUP BYw.id
43+
), current_presetsAS (
44+
SELECTw.idAS prebuild_id,
45+
wlp.template_version_preset_id
46+
FROM (workspaces w
47+
JOIN workspaces_with_latest_presets wlpON ((wlp.workspace_id=w.id)))
48+
WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
49+
)
50+
SELECTp.id,
51+
p.name,
52+
p.template_id,
53+
p.created_at,
54+
COALESCE(a.ready, false)AS ready,
55+
cp.template_version_preset_idAS current_preset_id
56+
FROM ((all_prebuilds p
57+
LEFT JOIN workspaces_with_agents_status aON ((a.workspace_id=p.id)))
58+
JOIN current_presets cpON ((cp.prebuild_id=p.id)));
Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,85 @@
1+
-- Drop the dependent views
2+
DROPVIEW workspace_prebuilds;
3+
-- Previously created in 000314_prebuilds.up.sql
4+
DROPVIEW 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+
CREATEVIEWworkspace_latest_buildsAS
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.idAS id,
23+
workspace_builds.workspace_idAS workspace_id,
24+
workspace_builds.template_version_idAS template_version_id,
25+
workspace_builds.job_idAS job_id,
26+
workspace_builds.template_version_preset_idAS template_version_preset_id,
27+
workspace_builds.transitionAS transition,
28+
workspace_builds.created_atAS created_at,
29+
provisioner_jobs.job_statusAS 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_numberDESC
40+
LIMIT
41+
1
42+
) latest_buildON TRUE
43+
WHEREworkspaces.deleted= false
44+
ORDER BYworkspaces.idASC;
45+
46+
-- Recreate the dependent views
47+
CREATEVIEWworkspace_prebuildsAS
48+
WITH all_prebuildsAS (
49+
SELECTw.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_presetsAS (
56+
SELECT DISTINCTON (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_idIS NOT NULL)
60+
ORDER BYworkspace_builds.workspace_id,workspace_builds.build_numberDESC
61+
), workspaces_with_agents_statusAS (
62+
SELECTw.idAS workspace_id,
63+
bool_and((wa.lifecycle_state='ready'::workspace_agent_lifecycle_state))AS ready
64+
FROM (((workspaces w
65+
JOIN workspace_latest_builds wlbON ((wlb.workspace_id=w.id)))
66+
JOIN workspace_resources wrON ((wr.job_id=wlb.job_id)))
67+
JOIN workspace_agents waON ((wa.resource_id=wr.id)))
68+
WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
69+
GROUP BYw.id
70+
), current_presetsAS (
71+
SELECTw.idAS prebuild_id,
72+
wlp.template_version_preset_id
73+
FROM (workspaces w
74+
JOIN workspaces_with_latest_presets wlpON ((wlp.workspace_id=w.id)))
75+
WHERE (w.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
76+
)
77+
SELECTp.id,
78+
p.name,
79+
p.template_id,
80+
p.created_at,
81+
COALESCE(a.ready, false)AS ready,
82+
cp.template_version_preset_idAS current_preset_id
83+
FROM ((all_prebuilds p
84+
LEFT JOIN workspaces_with_agents_status aON ((a.workspace_id=p.id)))
85+
JOIN current_presets cpON ((cp.prebuild_id=p.id)));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp