Before reporting an issue

[x] 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

[x] 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