Repository navigation
fix(keycloak-core): Only use DISTINCT in user searches when the query joins other tables - #54026
Open
SamTV12345 wants to merge 4 commits into
Open
SamTV12345 wants to merge 4 commits into
SamTV12345 wants to merge 4 commits into
Conversation
searchForUserStream always issued SELECT DISTINCT since the fix for multi-valued attribute searches (keycloak#39246). For a plain listing the query selects from USER_ENTITY alone, so the primary key already makes every row unique and DISTINCT only forces the database to sort the whole realm before applying the LIMIT instead of walking the (realm_id, username) index. On a realm with 380k users this turned a 0.1 ms query into a 1.3 s query that spilled to disk. Apply the same rule the count queries already use since keycloak#44860: DISTINCT is only needed when predicates() joined USER_ATTRIBUTE or FEDERATED_IDENTITY, which can match several rows per user. The new model test captures the generated SQL and checks that a plain listing runs without DISTINCT while attribute and IdP searches keep it and still return each user once. Closes keycloak#51385 Signed-off-by: SamTV12345 <40429738+SamTV12345@users.noreply.github.com>
Author
Yay |
Author
|
@ssilvert What about the mr? :) |
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.
Hi,
this fixes my issue that I linked below. I added the AI recommended fix as this is actually the solution. Only when a join happens we can get duplicated results. I also verified this behaviour with the below tests.
CC: @stianst and @ssilvert
Closes #51385