NotificationsYou must be signed in to change notification settings
Fork1k
Star11.1k

fix(coderd/database): optimize provisioner daemon with status query using index#19703

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Jump to bottom

Merged

mafredri merged 2 commits intomainfrommafredri/fix-optimize-provisioner-daemon-with-status-query

Sep 8, 2025

Merged

fix(coderd/database): optimize provisioner daemon with status query using index#19703

mafredri merged 2 commits intomainfrommafredri/fix-optimize-provisioner-daemon-with-status-query

Sep 8, 2025

Conversation

Copy link

Member

mafredri commentedSep 4, 2025•
edited
Loading

Fixescoder/internal#724

This change adds an index to optimize theGetProvisionerDaemonsWithStatusByOrganization query.

On dogfood, this index does not add a lot of weight to the database and reduces execution time.

Execution time:18s 838ms =>107ms.
Old plan:https://explain.dalibo.com/plan/eb54e284g5age6a1
New plan:https://explain.dalibo.com/plan/3fh6defecffhbg49

coder=> \di+ provisioner_jobs_worker_id_organization_id_completed_at_idxList of relations-[ RECORD 1 ]-+------------------------------------------------------------Schema        | publicName          | provisioner_jobs_worker_id_organization_id_completed_at_idxType          | indexOwner         | coderTable         | provisioner_jobsPersistence   | permanentAccess method | btreeSize          | 4720 kBDescription   |

fix(coderd/database): optimize GetProvisionerDaemonsWithStatusByOrgan…

23638c2

…ization query via indexFixescoder/internal#724

github-actionsbot assignedmafredri

Sep 4, 2025

mafredri changed the title~~fix(coderd/database): optimize GetProvisionerDaemonsWithStatusByOrganization query via index~~fix(coderd/database): optimize provisioner daemon with status query via index

Sep 4, 2025

mafredri changed the title~~fix(coderd/database): optimize provisioner daemon with status query via index~~fix(coderd/database): optimize provisioner daemon with status query using index

Sep 4, 2025

mafredri marked this pull request as ready for review

September 4, 2025 13:25

mafredri requested review fromjohnstcn,spikecurtis anddeansheather

September 4, 2025 13:26

johnstcn approved these changes

Sep 4, 2025

View reviewed changes

Copy link

Member

johnstcn left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Missing amake gen but otherwise nice add!

...tabase/migrations/000363_optimize_getprovisionerdaemonswithstatusbyorganization_query.up.sql

		@@ -0,0 +1,3 @@
		CREATE INDEX provisioner_jobs_worker_id_organization_id_completed_at_idx ON provisioner_jobs (worker_id, organization_id, completed_at DESC NULLS FIRST);

Copy link

Member

johnstcnSep 4, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

What impact didNULLS FIRST show versus without in your testing?

Copy link

MemberAuthor

mafredriSep 4, 2025•
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

It's negligible because there are 0 uncompleted jobs in the clone I have. It does get rid of one sort, and should provide a benefit for larger datasets and lots of in flight jobs.