Skip to content

Commit 9db265d

Browse files
mafredrimafredri
authored
fix(coderd/database): optimize provisioner daemon with status query using index (#19703)
Fixes coder/internal#724 This change adds an index to optimize the `GetProvisionerDaemonsWithStatusByOrganization` query. Execution time dropped from `18s 838ms` to `107ms`.
1 parent 38028df commit 9db265d

File tree

3 files changed

+8
-0
lines changed

3 files changed

+8
-0
lines changed

‎coderd/database/dump.sql

Lines changed: 4 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000363_optimize_getprovisionerdaemonswithstatusbyorganization_query.down.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROP INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx;

‎coderd/database/migrations/000363_optimize_getprovisionerdaemonswithstatusbyorganization_query.up.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
CREATE INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx ON provisioner_jobs (worker_id, organization_id, completed_at DESC NULLS FIRST);
2+
3+
COMMENT ON INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx IS 'Support index for finding the latest completed jobs for a worker (and organization), nulls first so that active jobs have priority; targets: GetProvisionerDaemonsWithStatusByOrganization';

0 commit comments

Comments
 (0)