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

Commitef0d74f

Browse files
authored
chore: improve performance of 'GetLatestWorkspaceBuildsByWorkspaceIDs' (#19452)
Closescoder/internal#716This prevents a scan over the entire `workspace_build` table by removinga `join`. This is still imperfect as we are still scanning over thenumber of builds for the workspaces in the arguments. Ideally we wouldhave some index or something precomputed. Then we could skip scanningover the builds for the correct workspaces that are not the latest.
1 parent63c1325 commitef0d74f

File tree

3 files changed

+92
-28
lines changed

3 files changed

+92
-28
lines changed

‎coderd/database/querier_test.go‎

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ import (
3232
"github.com/coder/coder/v2/coderd/provisionerdserver"
3333
"github.com/coder/coder/v2/coderd/rbac"
3434
"github.com/coder/coder/v2/coderd/rbac/policy"
35+
"github.com/coder/coder/v2/coderd/util/slice"
3536
"github.com/coder/coder/v2/codersdk"
3637
"github.com/coder/coder/v2/provisionersdk"
3738
"github.com/coder/coder/v2/testutil"
@@ -6579,3 +6580,75 @@ func TestWorkspaceBuildDeadlineConstraint(t *testing.T) {
65796580
}
65806581
}
65816582
}
6583+
6584+
// TestGetLatestWorkspaceBuildsByWorkspaceIDs populates the database with
6585+
// workspaces and builds. It then tests that
6586+
// GetLatestWorkspaceBuildsByWorkspaceIDs returns the latest build for some
6587+
// subset of the workspaces.
6588+
funcTestGetLatestWorkspaceBuildsByWorkspaceIDs(t*testing.T) {
6589+
t.Parallel()
6590+
6591+
db,_:=dbtestutil.NewDB(t)
6592+
6593+
org:=dbgen.Organization(t,db, database.Organization{})
6594+
admin:=dbgen.User(t,db, database.User{})
6595+
6596+
tv:=dbfake.TemplateVersion(t,db).
6597+
Seed(database.TemplateVersion{
6598+
OrganizationID:org.ID,
6599+
CreatedBy:admin.ID,
6600+
}).
6601+
Do()
6602+
6603+
users:=make([]database.User,5)
6604+
wrks:=make([][]database.WorkspaceTable,len(users))
6605+
exp:=make(map[uuid.UUID]database.WorkspaceBuild)
6606+
fori:=rangeusers {
6607+
users[i]=dbgen.User(t,db, database.User{})
6608+
dbgen.OrganizationMember(t,db, database.OrganizationMember{
6609+
UserID:users[i].ID,
6610+
OrganizationID:org.ID,
6611+
})
6612+
6613+
// Each user gets 2 workspaces.
6614+
wrks[i]=make([]database.WorkspaceTable,2)
6615+
forwi:=rangewrks[i] {
6616+
wrks[i][wi]=dbgen.Workspace(t,db, database.WorkspaceTable{
6617+
TemplateID:tv.Template.ID,
6618+
OwnerID:users[i].ID,
6619+
})
6620+
6621+
// Choose a deterministic number of builds per workspace
6622+
// No more than 5 builds though, that would be excessive.
6623+
forj:=int32(1);int(j)<= (i+wi)%5;j++ {
6624+
wb:=dbfake.WorkspaceBuild(t,db,wrks[i][wi]).
6625+
Seed(database.WorkspaceBuild{
6626+
WorkspaceID:wrks[i][wi].ID,
6627+
BuildNumber:j+1,
6628+
}).
6629+
Do()
6630+
6631+
exp[wrks[i][wi].ID]=wb.Build// Save the final workspace build
6632+
}
6633+
}
6634+
}
6635+
6636+
// Only take half the users. And only take 1 workspace per user for the test.
6637+
// The others are just noice. This just queries a subset of workspaces and builds
6638+
// to make sure the noise doesn't interfere with the results.
6639+
assertWrks:=wrks[:len(users)/2]
6640+
ctx:=testutil.Context(t,testutil.WaitLong)
6641+
ids:=slice.Convert[[]database.WorkspaceTable, uuid.UUID](assertWrks,func(pair []database.WorkspaceTable) uuid.UUID {
6642+
returnpair[0].ID
6643+
})
6644+
6645+
require.Greater(t,len(ids),0,"expected some workspace ids for test")
6646+
builds,err:=db.GetLatestWorkspaceBuildsByWorkspaceIDs(ctx,ids)
6647+
require.NoError(t,err)
6648+
for_,b:=rangebuilds {
6649+
expB,ok:=exp[b.WorkspaceID]
6650+
require.Truef(t,ok,"unexpected workspace build for workspace id %s",b.WorkspaceID)
6651+
require.Equalf(t,expB.ID,b.ID,"unexpected workspace build id for workspace id %s",b.WorkspaceID)
6652+
require.Equal(t,expB.BuildNumber,b.BuildNumber,"unexpected build number")
6653+
}
6654+
}

‎coderd/database/queries.sql.go‎

Lines changed: 9 additions & 14 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/workspacebuilds.sql‎

Lines changed: 10 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -76,20 +76,16 @@ LIMIT
7676
1;
7777

7878
-- name: GetLatestWorkspaceBuildsByWorkspaceIDs :many
79-
SELECT wb.*
80-
FROM (
81-
SELECT
82-
workspace_id,MAX(build_number)as max_build_number
83-
FROM
84-
workspace_build_with_userAS workspace_builds
85-
WHERE
86-
workspace_id= ANY(@ids :: uuid [ ])
87-
GROUP BY
88-
workspace_id
89-
) m
90-
JOIN
91-
workspace_build_with_userAS wb
92-
ONm.workspace_id=wb.workspace_idANDm.max_build_number=wb.build_number;
79+
SELECT
80+
DISTINCTON (workspace_id)
81+
*
82+
FROM
83+
workspace_build_with_userAS workspace_builds
84+
WHERE
85+
workspace_id= ANY(@ids :: uuid [ ])
86+
ORDER BY
87+
workspace_id, build_numberDESC-- latest first
88+
;
9389

9490
-- name: InsertWorkspaceBuild :exec
9591
INSERT INTO

0 commit comments

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