chore(coderd/database): optimize AuditLogs queries #18600
Conversation
…dit logs query with conditional joins
| workspace_builds.build_number = 1 | ||
| ) | ||
| LEFT JOIN organizations ON audit_logs.organization_id = organizations.id | ||
| SELECT sqlc.embed(audit_logs), |
There was a problem hiding this comment.
I'd a comment that CountAuditLogs also needs to be extended whenever we extend the filtering on GetAuditLogsOffset
There was a problem hiding this comment.
Or maybe better - add a test that compares the filter object for this method and count method
There was a problem hiding this comment.
I have added a new test which compares WHERE clause for both queries :)
There was a problem hiding this comment.
LGTM. I've based my implementation of the connection log queries off this PR without any trouble: #18629
coderd/audit.go
Outdated
| @@ -64,7 +64,21 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) { | |||
| filter.Username = "" | |||
| } | |||
|
|
|||
| dblogs, err := api.Database.GetAuditLogsOffset(ctx, filter) | |||
| // Use the same filters to count the number of audit logs | |||
| count, err := api.Database.CountAuditLogs(ctx, database.CountAuditLogsParams{ | |||
There was a problem hiding this comment.
It's going to be very easy for someone to forget to update this in both places. Maybe you should make the searchquery.AuditLogs package return both of these params types so they're constructed in the same place
Or add a comment in both places
There was a problem hiding this comment.
Moved to searchquery
| LEFT JOIN users ON audit_logs.user_id = users.id | ||
| LEFT JOIN organizations ON audit_logs.organization_id = organizations.id | ||
| -- First join on workspaces to get the initial workspace create | ||
| -- to workspace build 1 id. This is because the first create is | ||
| -- is a different audit log than subsequent starts. | ||
| LEFT JOIN workspaces ON | ||
| audit_logs.resource_type = 'workspace' AND | ||
| audit_logs.resource_id = workspaces.id |
There was a problem hiding this comment.
The indent formatting in this whole file seems messed up now
Closes #17689
This PR optimizes the audit logs query performance by extracting the count operation into a separate query and replacing the OR-based workspace_builds with conditional joins.
Query changes
workspace_buildsjoin with OR conditions with separate conditional joinswb_buildfor workspace_build audit logs (which is a direct lookup)wb_workspacefor workspace create audit logs (via workspace)Optimized AuditLogsOffset query:
https://explain.dalibo.com/plan/4g1hbedg4a564bg8
New CountAuditLogs query:
https://explain.dalibo.com/plan/ga2fbcecb9efbce3