chore(coderd/database): optimize GetRunningPrebuiltWorkspaces #18588
Conversation
| AS ( | ||
| SELECT | ||
| latest_prebuilds.job_id, | ||
| BOOL_AND(workspace_agents.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)::boolean AS ready |
There was a problem hiding this comment.
| BOOL_AND(workspace_agents.lifecycle_state = 'ready'::workspace_agent_lifecycle_state)::boolean AS ready | |
| workspace_agents.lifecycle_state = 'ready'::workspace_agent_lifecycle_state AS ready |
What's this, it actually works?
There was a problem hiding this comment.
I just copied it from the workspace_prebuilds view.
| latest_prebuilds.created_at | ||
| FROM | ||
| latest_prebuilds | ||
| LEFT JOIN ready_agents ON |
There was a problem hiding this comment.
This join will duplicate rows when there are multiple agents since there is no group-by here or in the ready_agents CTE, was this considered?
There was a problem hiding this comment.
That's a good point. I'll add some unit tests to compare the behaviour of the previous query.
There was a problem hiding this comment.
The 'correct' behaviour seems to be one row per provisioner job / workspace build.
ab80dc0 to
9131d78
Compare
| @@ -3888,6 +3888,95 @@ func TestWorkspacePrebuildsView(t *testing.T) { | |||
| } | |||
| } | |||
|
|
|||
| func TestGetRunningPrebuiltWorkspaces(t *testing.T) { | |||
There was a problem hiding this comment.
reivew: adapted from TestWorkspacePrebuildsView.
| latest_prebuilds.created_at | ||
| FROM latest_prebuilds | ||
| LEFT JOIN ready_agents ON ready_agents.job_id = latest_prebuilds.job_id | ||
| ORDER BY latest_prebuilds.workspace_id ASC; |
There was a problem hiding this comment.
Nice, formatting is chef's kiss
|
The updated plan is slower than the previous one, but correct according to the added unit tests. |
Interesting, it could be due to moving WHERE conditions. It's always interesting how a hypothesis for optimization can crumble in the face of actual data layout. And where naive improvements may actually stomp on the query planners feet. 😅 Either way, the "slower" improvement is still great, no need to micro optimize IMO. 👍🏻 |
|
Revert here: #18688 This caused the prebuilds reconciler to continuously attempt to delete failed prebuilds. |
Fixes coder/internal#715
After this change, the only use of the
workspace_prebuildsview is theClaimPrebuiltWorkspacequery. A subsequent PR will update the view.Before: ~44ms https://explain.dalibo.com/plan/76cbe21d1a4c9329#plan
~After:
3.7ms https://explain.dalibo.com/plan/f61beg93f9306fc0#planAfter: 7.3ms https://explain.dalibo.com/plan/5abbdf926315677e#plan