Skip to content

chore: remove excess join in GetQuotaConsumedForUser query #15338

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.

Sign up for GitHub

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

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 5 commits into from
Nov 5, 2024
Merged

chore: remove excess join in GetQuotaConsumedForUser query #15338

merged 5 commits into from
Nov 5, 2024

Conversation

Emyrk
Copy link
Member

@Emyrk Emyrk commented Nov 1, 2024 •
edited
Loading

Followup of #15261

Filter is applied in original workspace query. We do not need to join workspaces twice. Also used build_number instead of created_at for determining the last build.

Plans below run on dev.coder.com database. Interestingly the seq scan on workspaces goes away. It was happening on the second join? Because of this, our SIReadLock becomes finer on the workspaces table. It goes from workspaces/relation/SIReadLock to workspaces/tuple/SIReadLock: page=0 tuple=1

Before

Plan

SIReadLock Locks:

7866-<nil> [granted] workspace_builds/tuple/SIReadLock: page=0 tuple=1
7866-<nil> [granted] workspace_builds_workspace_id_build_number_key/page/SIReadLock: page=1
7866-<nil> [granted] workspaces/relation/SIReadLock: 
7866-<nil> [granted] workspaces_owner_id_lower_idx/page/SIReadLock: page=1
Raw locks 
7867-<nil> [granted] <nil>/virtualxid/ExclusiveLock: waiting to acquire virtual tx id lock
7866-<nil> [granted] <nil>/virtualxid/ExclusiveLock: waiting to acquire virtual tx id lock
7867-<nil> [granted] pg_locks/relation/AccessShareLock: 
7866-<nil> [granted] workspace_builds/relation/AccessShareLock: 
7866-<nil> [granted] workspace_builds/tuple/SIReadLock: page=0 tuple=1
7866-<nil> [granted] workspace_builds_job_id_key/relation/AccessShareLock: 
7866-<nil> [granted] workspace_builds_pkey/relation/AccessShareLock: 
7866-<nil> [granted] workspace_builds_workspace_id_build_number_key/relation/AccessShareLock: 
7866-<nil> [granted] workspace_builds_workspace_id_build_number_key/page/SIReadLock: page=1
7866-<nil> [granted] workspaces/relation/AccessShareLock: 
7866-<nil> [granted] workspaces/relation/SIReadLock: 
7866-<nil> [granted] workspaces_owner_id_lower_idx/relation/AccessShareLock: 
7866-<nil> [granted] workspaces_owner_id_lower_idx/page/SIReadLock: page=1
7866-<nil> [granted] workspaces_pkey/relation/AccessShareLock:

After

Plan

SIReadLock Locks:

8052-<nil> [granted] workspace_builds/tuple/SIReadLock: page=0 tuple=1
8052-<nil> [granted] workspace_builds_workspace_id_build_number_key/page/SIReadLock: page=1
8052-<nil> [granted] workspaces/tuple/SIReadLock: page=0 tuple=1
8052-<nil> [granted] workspaces_owner_id_lower_idx/page/SIReadLock: page=1
Raw locks 
8053-<nil> [granted] <nil>/virtualxid/ExclusiveLock: waiting to acquire virtual tx id lock
8052-<nil> [granted] <nil>/virtualxid/ExclusiveLock: waiting to acquire virtual tx id lock
8053-<nil> [granted] pg_locks/relation/AccessShareLock: 
8052-<nil> [granted] workspace_builds/tuple/SIReadLock: page=0 tuple=1
8052-<nil> [granted] workspace_builds/relation/AccessShareLock: 
8052-<nil> [granted] workspace_builds_job_id_key/relation/AccessShareLock: 
8052-<nil> [granted] workspace_builds_pkey/relation/AccessShareLock: 
8052-<nil> [granted] workspace_builds_workspace_id_build_number_key/relation/AccessShareLock: 
8052-<nil> [granted] workspace_builds_workspace_id_build_number_key/page/SIReadLock: page=1
8052-<nil> [granted] workspaces/relation/AccessShareLock: 
8052-<nil> [granted] workspaces/tuple/SIReadLock: page=0 tuple=1
8052-<nil> [granted] workspaces_owner_id_lower_idx/relation/AccessShareLock: 
8052-<nil> [granted] workspaces_owner_id_lower_idx/page/SIReadLock: page=1
8052-<nil> [granted] workspaces_pkey/relation/AccessShareLock:

Emyrk
Emyrk commented Nov 1, 2024
View reviewed changes
coderd/database/queries/quotas.sql
Comment on lines 28 to +33
INNER JOIN
workspaces on wb.workspace_id = workspaces.id
WHERE
-- Only return workspaces that match the user + organization.
-- Quotas are calculated per user per organization.
NOT workspaces.deleted AND
Copy link
Member Author

Choose a reason for hiding this comment

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

This workspaces.deleted being placed in this join sub query prevents the workspaces seq scan from what I can tell.

The alternative place is to place it in the final query as NOT workspaces.deleted.

This is equivalent though because we are doing an INNER JOIN on workspace_builds and workspaces. If the workspace is deleted, it's builds are completely omitted from latest_builds

@Emyrk
Copy link
Member Author

Emyrk commented Nov 1, 2024 •
edited
Loading

@spikecurtis You did ask in the other thread if a Bitmap Heap Scan locks the entire workspaces table. Experimentally it only locks the rows it fetches. I added a second workspace to my test, and got these SIReadLocks. You can see both tuples.

8167-<nil> [granted] workspace_builds/tuple/SIReadLock: page=0 tuple=1
8167-<nil> [granted] workspace_builds/tuple/SIReadLock: page=0 tuple=2
8167-<nil> [granted] workspace_builds_workspace_id_build_number_key/page/SIReadLock: page=1
8167-<nil> [granted] workspaces/tuple/SIReadLock: page=0 tuple=1
8167-<nil> [granted] workspaces/tuple/SIReadLock: page=0 tuple=2
8167-<nil> [granted] workspaces_owner_id_lower_idx/page/SIReadLock: page=1

I do not know at what scale these fine locks get more coarse.

I imagine adding indices would just do a page lock on an index? Not really sure, but I am going to hold off adding more indices at this time.

@Emyrk Emyrk requested a review from spikecurtis November 1, 2024 16:24
spikecurtis
spikecurtis approved these changes Nov 5, 2024
View reviewed changes
Copy link
Contributor

@spikecurtis spikecurtis left a comment

Choose a reason for hiding this comment

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

LGTM!

@Emyrk Emyrk merged commit 2d00b50 into main Nov 5, 2024
29 checks passed
@Emyrk Emyrk deleted the stevenmasley/get_quota branch November 5, 2024 14:12
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@spikecurtis spikecurtis spikecurtis approved these changes

Assignees

@Emyrk Emyrk

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@Emyrk @spikecurtis