Repository navigation
Conversation
This was referenced Nov 2, 2026
kylecarbs
marked this pull request as ready for review
November 3, 2026 22:11
kylecarbs
requested review from
Emyrk
and removed request for
ibetitsmike and
johnstcn
November 9, 2026 15:26
COUNT(*) OVER () pushed every matching user through a window and sorted all of them by lower(username) before the LIMIT applied. Filter in a CTE, page through users_username_lower_idx, and count separately. Search and role filters keep the single pass window count, since a second scan costs more there. sqlc numbers the parameters differently through the CTE, so GetAuthorizedUsers passes them in the new order, and the rbac_roles type override is repeated for the CTE column. Generated by Coder Agents.
kylecarbs
force-pushed
the
perf/db-users-list
branch
from
November 9, 2026 21:28
35c2b51 to
a323bf2
Compare
This branch has not been deployed
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
GetUsersusedCOUNT(*) OVER (), which pushed every matching user (about 99k wide rows) through a window aggregate that spilled to disk. It then sorted all of them bylower(username)before the LIMIT applied.The filters now go into a CTE. The page walks
users_username_lower_idxand stops after LIMIT rows, and the count is a separateCOUNT(*)over the same CTE, so it stays exact. Search and role filters keep the single-pass window count: they are unindexed and a second scan costs more there. Parameters are known at plan time, so Postgres drops the unused branch. If a generic plan were reused, results stay correct and speed matchesmain.GetUsers(warm)status:activeuser123End to end,
/userswent from 264 to 139 ms and?q=status:activefrom 275 to 137 ms.Output, including the count column, was hashed against
mainfor 30 parameter sets, both without and with a selective RBAC filter injected; all matched. sqlc numbers the parameters differently through the CTE, soGetAuthorizedUsersinmodelqueries.gopasses them in the new order (gentest checks this), and therbac_rolestype override is repeated for the CTE column insqlc.yaml.Depends on #31625.
Decision log
PaginatedOrganizationMemberswas also rewritten (paged 258 to 45 ms) but reverted. It got slower with no limit (307 to 571 ms), which the API allows when clients omitlimit, and no equivalent shape fixed both cases.(user_id, created_at DESC)index: 5k notifications for one user already list in about 1 ms.pg_trgmfor ILIKE search (80 ms floor) and approximate counts.PaginatedOrganizationMembers, anafter_idcursor for a user in more than one organization fails with "more than one row returned by a subquery".Generated by Coder Agents on behalf of @kylecarbs.