This query below take very long time to respond when users are more than 2M and its very expensive since LIKE is used here.
SELECT userentity0_.ID AS ID1_72_,
userentity0_.CREATED_TIMESTAMP AS CREATED_2_72_,
userentity0_.EMAIL AS EMAIL3_72_,
userentity0_.EMAIL_CONSTRAINT AS EMAIL_CO4_72_,
userentity0_.EMAIL_VERIFIED AS EMAIL_VE5_72_,
userentity0_.ENABLED AS ENABLED6_72_,
userentity0_.FEDERATION_LINK AS FEDERATI7_72_,
userentity0_.FIRST_NAME AS FIRST_NA8_72_,
userentity0_.LAST_NAME AS LAST_NAM9_72_,
userentity0_.NOT_BEFORE AS NOT_BEF10_72_,
userentity0_.REALM_ID AS REALM_I11_72_,
userentity0_.SERVICE_ACCOUNT_CLIENT_LINK AS SERVICE12_72_,
userentity0_.USERNAME AS USERNAM13_72_
FROM public.USER_ENTITY userentity0_
WHERE userentity0_.REALM_ID = 'test'
AND userentity0_.USERNAME like '%user%'
LIMIT 1;
The database I am using is Postgres enterprise edition. I tried creating indexes however that does not worked.
Is there any solution?
Should I upgrade my keycloak version? if yes, which version with minimum side effect?
Please help