Files
318acd4bc3 perf(query): speed up ListUsers login name equality filters (#12460)
<!-- CURSOR_AGENT_PR_BODY_BEGIN -->
## Summary

`ListUsers` with `LoginNameQuery` + equals/equals-ignore-case was very
slow on large orgs (e.g. ~330k users) because the generated SQL filtered
the `projections.login_names3` **view** on the computed
`login_name_lower` expression and correlated that subquery on
`users14.id`. Postgres nested-looped every user and never used
`login_names3_users_search (instance_id, user_name_lower)`.

This change rewrites the **query planner** for that hot path: when an
equals/equals-ignore-case login-name filter is present (and not under
`OR`/`NOT`), the user list query **INNER JOINs** an indexed matches
subquery instead of filtering via the view expression. The matches SQL
mirrors `user_by_login_name.sql` (`user_name_lower` / domain paths +
`preferred` / `is_primary`).

Non-equals methods and OR combinations keep the previous view-based
filter so semantics stay unchanged.

Also adds a k6 use case that mirrors login v2 discovery
(`loginNameQuery` EQUALS_IGNORE_CASE + `organizationIdQuery`, `limit:
2`):

```bash
cd benchmark
make users_by_login_name USER_AMOUNT=100000 VUS=10 DURATION=60s
```

## Approach

1. `NewLoginNameSearchQuery` for equals / equals-ignore-case returns a
marker `loginNameEqualsFilter` (other methods unchanged).
2. `prepareUsersQuery` extracts that marker when safe, then:
- builds the usual `sq.SelectBuilder` **without** the login-name view
predicate
- adds `JoinClause` to `user_login_name_matches(.sql)` /
`_case_sensitive.sql` as `login_name_matches`
- keeps metadata JOIN/`DISTINCT` only when metadata filters are present
(same as before)
3. Embedded SQL files under `internal/query/` for the matches subquery.

Local smoke against ~330k synthetic users: baseline ~1392ms → rewritten
path ~0.4ms for a single equals-ignore-case lookup.

## Test plan

- [x] `go test ./internal/query/ -run
'TestLoginName|TestUsers|TestUserByLoginName'` (after generate-stubs)
- [x] Existing `user_test` expected SQL updated (no always-on metadata
join; login-name equals uses JOIN)
- [ ] Run k6 before/after on a large `USER_AMOUNT` (e.g. 50k–100k+) and
compare `list_users_duration` p50/p95/p99
- [x] Manual login v2 username discovery against a large org
- [x] Confirm OR / NOT / CONTAINS login-name queries still return
expected results

<!-- CURSOR_AGENT_PR_BODY_END -->

<div><a
href="https://cursor.com/agents/bc-252883bd-48d9-492e-b619-5ccfa93cf9c3"><picture><source
media="(prefers-color-scheme: dark)"
srcset="https://cursor.com/assets/images/open-in-web-dark.png"><source
media="(prefers-color-scheme: light)"
srcset="https://cursor.com/assets/images/open-in-web-light.png"><img
alt="Open in Web" width="114" height="28"
src="https://cursor.com/assets/images/open-in-web-dark.png"></picture></a>&nbsp;<a
href="https://cursor.com/background-agent?bcId=bc-252883bd-48d9-492e-b619-5ccfa93cf9c3"><picture><source
media="(prefers-color-scheme: dark)"
srcset="https://cursor.com/assets/images/open-in-cursor-dark.png"><source
media="(prefers-color-scheme: light)"
srcset="https://cursor.com/assets/images/open-in-cursor-light.png"><img
alt="Open in Cursor" width="131" height="28"
src="https://cursor.com/assets/images/open-in-cursor-dark.png"></picture></a>&nbsp;</div>

---------

Co-authored-by: Cursor Agent <cursoragent@cursor.com>
Co-authored-by: Silvan <adlerhurst@users.noreply.github.com>
2026-07-28 11:56:07 +00:00
..
2025-10-08 10:27:02 +02:00
2025-10-08 10:27:02 +02:00

Benchmarks

This package contains code for benchmarking specific endpoints of the API using k6.

Prerequisite

  • npm
  • k6
  • go
  • xk6 (make sure ~/go/bin is in your ${PATH})
  • running the API

Structure

The use cases under tests are defined in src/use_cases. The implementation of ZITADEL resources and calls are located under src.

Execution

Env vars

  • VUS: Amount of parallel processes execute the test (default is 20)
  • DURATION: Defines how long the tests are executed (default is 200s)
  • ZITADEL_HOST: URL of ZITADEL (default is http://localhost:8080)
  • ADMIN_LOGIN_NAME: Loginanme of a human user with IAM_OWNER-role
  • ADMIN_PASSWORD: password of the human user
  • USER_AMOUNT: Number of users created during setup for list-users benchmarks (default is 2500)
  • SETUP_CONCURRENCY: Max in-flight user-create requests during list-users setup (default is 50). Large USER_AMOUNT with unbounded parallelism can exhaust ephemeral ports (can't assign requested address).

To setup the tests we use the credentials of management console and log in using an admin. The user must be able to create organizations and all resources inside organizations.

  • ADMIN_LOGIN_NAME: zitadel-admin@zitadel.localhost
  • ADMIN_PASSWORD: Password1!

Test

Before you run the tests you need an initialized user. The tests don't implement the change password screen during login.

  • make human_password_login
    setup: creates human users
    test: uses the previously created humans to sign in using the login ui
  • make machine_pat_login
    setup: creates machines and a pat for each machine
    test: calls user info endpoint with the given pats
  • make machine_client_credentials_login
    setup: creates machines and a client credential secret for each machine
    test: calls token endpoint with the client_credentials grant type.
  • make user_info
    setup: creates human users and signs them in
    test: calls user info endpoint using the given humans
  • make manipulate_user
    test: creates a human, updates its profile, locks the user and then deletes it
  • make introspect
    setup: creates projects, one api per project, one key per api and generates the jwt from the given keys
    test: calls introspection endpoint using the given JWTs
  • make add_session
    setup: creates human users
    test: creates new sessions with user id check
  • make oidc_session
    setup: creates a service account to create the auth request and session.
    test: creates an auth request, a session and links the session to the auth request. Implementation of this flow.
  • make otp_session
    setup: creates 1 human user for each VU and adds OTP Email to it
    test: creates a session based on the login name of the user, sets the OTP Email challenge to the session and afterwards checks the OTP code
  • make password_session
    setup: creates 1 human user for each VU and adds OTP Email to it
    test: creates a session based on the login name of the user and checks for the password on a second step
  • make machine_jwt_profile_grant
    setup: generates private/public key, creates service accounts, adds a key
    test: creates a token and calls user info
  • make machine_jwt_profile_grant_single_user
    setup: generates private/public key, creates service account, adds a key
    test: creates a token and calls user info in parallel for the same user
  • make users_by_metadata_key
    setup: creates for half of the VUS a human user and a machine for the other half, adds 3 metadata to each user test: calls the list users endpoint and filters by a metadata key
  • make users_by_metadata_value
    setup: creates for half of the VUS a human user and a machine for the other half, adds 3 metadata to each user test: calls the list users endpoint and filters by a metadata value
  • make users_by_login_name
    setup: creates USER_AMOUNT human users (default 2500) in a new org, with SETUP_CONCURRENCY parallel creates (default 50)
    test: calls ListUsers the same way as login v2 (loginNameQuery with EQUALS_IGNORE_CASE, organizationIdQuery, limit: 2)
    note: to reproduce multi-second latency on the old query plan, use a large dataset, e.g. USER_AMOUNT=100000 VUS=10 DURATION=60s
  • make verify_all_user_grants_exists
    setup: creates 50 projects, 1 machine per VU test: creates a machine and grants all projects to the machine teardown: the organization is not removed to verify the data of the projections are correct. You can find additional information at the bottom of this file