Closed
Description
Before reporting an issue
- I have read and understood the above terms for submitting issues, and I understand that my issue may be closed without action if I do not follow them.
Area
core
Describe the bug
When there are a lot of roles in the DB (in this case 400_000), the deletion of a role takes several seconds.
Due to that, deletion of a realm times out, even if that realm has only very few roles.
Version
26.2.1
Regression
- The issue is a regression
Expected behavior
The deletion should be fast, as all data in the database is indexed.
Actual behavior
It takes several seconds.
fetching via a "member of" in JPA with
select role from RoleEntity role where :compositeRole member of role.compositeRoles
lead to a query
select re1_0.ID,re1_0.CLIENT,re1_0.CLIENT_REALM_CONSTRAINT,re1_0.CLIENT_ROLE,re1_0.DESCRIPTION,re1_0.NAME,re1_0.REALM_ID from KEYCLOAK_ROLE re1_0 where ? in (select cr1_0.CHILD_ROLE from COMPOSITE_ROLE cr1_0 where re1_0.ID=cr1_0.COMPOSITE)
which was awfully slow with 400_000 KEYCLOAK_ROLE entries and 316 entries in COMPOSITE_ROLE on Aurora PostgreSQL 16.6:
Limit (cost=0.00..903683.68 rows=100000 width=167)
-> Seq Scan on keycloak_role re1_0 (cost=0.00..1827365.87 rows=202213 width=167)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on composite_role cr1_0 (cost=0.00..8.95 rows=6 width=37)
Filter: ((re1_0.id)::text = (composite)::text)
What I actually want is:
select * from KEYCLOAK_ROLE re1_0 where ID in (select cr1_0.CHILD_ROLE from COMPOSITE_ROLE cr1_0 where cr1_0.CHILD_ROLE = '5e0c1a84-2708-4876-b620-cb7a60eddbe8')
which is fast:
Nested Loop Semi Join (cost=0.70..16.74 rows=1 width=167)
-> Index Scan using constraint_a on keycloak_role re1_0 (cost=0.42..8.44 rows=1 width=167)
Index Cond: ((id)::text = '5e0c1a84-2708-4876-b620-cb7a60eddbe8'::text)
-> Index Only Scan using idx_composite_child on composite_role cr1_0 (cost=0.27..8.29 rows=1 width=37)
Index Cond: (child_role = '5e0c1a84-2708-4876-b620-cb7a60eddbe8'::text)
How to Reproduce?
Occurred in out nightly performance runs on KCB.
Anything else?
No response