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

We have more than 10 million active client sessions in our database. When we're now trying to delete a client, it leads to a very expensive SQL statement that doesn't hit an index.
https://github.com/keycloak/keycloak/blob/1c05c8df630bc025d328f4b986798575d3cce7a5/model/jpa/src/main/java/org/keycloak/models/jpa/session/PersistentClientSessionEntity.java#L37

Version

26.4.0

Regression

[ ] The issue is a regression

Expected behavior

Deletion should be fast

Actual behavior

Deletion takes sometimes even longer than the lifetime of a token and fails into a 401.

How to Reproduce?

1. Have a couple million (or even less) client sessions
2. Delete a client

It should be observable that performance degrades the more client session exist.

Anything else?

Output from

EXPLAIN ANALYZE

DELETE FROM offline_client_session WHERE client_id = '693a0dc3-8e39-4d7f-92af-31a49e1559e8';

Delete on offline_client_session  (cost=0.56..575418.01 rows=0 width=0) (actual time=297912.707..297912.708 rows=0 loops=1)
  ->  Index Scan using constraint_offl_cl_ses_pk3 on offline_client_session  (cost=0.56..575418.01 rows=1242 width=6) (actual time=297912.705..297912.706 rows=0 loops=1)

Index Cond: ((client_id)::text = '693a0dc3-8e39-4d7f-92af-31a49e1559e8'::text)

Planning Time: 1.134 ms
Execution Time: 297915.965 ms