mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
ef745c0c5d
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>
86 lines
3.1 KiB
SQL
86 lines
3.1 KiB
SQL
-- Drop the dependent views
|
|
DROP VIEW workspace_prebuilds;
|
|
-- Previously created in 000314_prebuilds.up.sql
|
|
DROP VIEW workspace_latest_builds;
|
|
|
|
-- The previous version of this view had two sequential scans on two very large
|
|
-- tables. This version optimized it by using index scans (via a lateral join)
|
|
-- AND avoiding selecting builds from deleted workspaces.
|
|
CREATE VIEW workspace_latest_builds AS
|
|
SELECT
|
|
latest_build.id,
|
|
latest_build.workspace_id,
|
|
latest_build.template_version_id,
|
|
latest_build.job_id,
|
|
latest_build.template_version_preset_id,
|
|
latest_build.transition,
|
|
latest_build.created_at,
|
|
latest_build.job_status
|
|
FROM workspaces
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
workspace_builds.id AS id,
|
|
workspace_builds.workspace_id AS workspace_id,
|
|
workspace_builds.template_version_id AS template_version_id,
|
|
workspace_builds.job_id AS job_id,
|
|
workspace_builds.template_version_preset_id AS template_version_preset_id,
|
|
workspace_builds.transition AS transition,
|
|
workspace_builds.created_at AS created_at,
|
|
provisioner_jobs.job_status AS job_status
|
|
FROM
|
|
workspace_builds
|
|
JOIN
|
|
provisioner_jobs
|
|
ON
|
|
provisioner_jobs.id = workspace_builds.job_id
|
|
WHERE
|
|
workspace_builds.workspace_id = workspaces.id
|
|
ORDER BY
|
|
build_number DESC
|
|
LIMIT
|
|
1
|
|
) latest_build ON TRUE
|
|
WHERE workspaces.deleted = false
|
|
ORDER BY workspaces.id ASC;
|
|
|
|
-- Recreate the dependent views
|
|
CREATE VIEW workspace_prebuilds AS
|
|
WITH all_prebuilds AS (
|
|
SELECT w.id,
|
|
w.name,
|
|
w.template_id,
|
|
w.created_at
|
|
FROM workspaces w
|
|
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
|
|
), workspaces_with_latest_presets AS (
|
|
SELECT DISTINCT ON (workspace_builds.workspace_id) workspace_builds.workspace_id,
|
|
workspace_builds.template_version_preset_id
|
|
FROM workspace_builds
|
|
WHERE (workspace_builds.template_version_preset_id IS NOT NULL)
|
|
ORDER BY workspace_builds.workspace_id, workspace_builds.build_number DESC
|
|
), workspaces_with_agents_status AS (
|
|
SELECT w.id AS workspace_id,
|
|
bool_and((wa.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)) AS ready
|
|
FROM (((workspaces w
|
|
JOIN workspace_latest_builds wlb ON ((wlb.workspace_id = w.id)))
|
|
JOIN workspace_resources wr ON ((wr.job_id = wlb.job_id)))
|
|
JOIN workspace_agents wa ON ((wa.resource_id = wr.id)))
|
|
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
|
|
GROUP BY w.id
|
|
), current_presets AS (
|
|
SELECT w.id AS prebuild_id,
|
|
wlp.template_version_preset_id
|
|
FROM (workspaces w
|
|
JOIN workspaces_with_latest_presets wlp ON ((wlp.workspace_id = w.id)))
|
|
WHERE (w.owner_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0'::uuid)
|
|
)
|
|
SELECT p.id,
|
|
p.name,
|
|
p.template_id,
|
|
p.created_at,
|
|
COALESCE(a.ready, false) AS ready,
|
|
cp.template_version_preset_id AS current_preset_id
|
|
FROM ((all_prebuilds p
|
|
LEFT JOIN workspaces_with_agents_status a ON ((a.workspace_id = p.id)))
|
|
JOIN current_presets cp ON ((cp.prebuild_id = p.id)));
|