Missing lower index on users table - needed for postgres

[strehle]

What version of UAA are you running?

75.7.0 , develop

How are you deploying the UAA?

I am deploying the UAA

• locally only using gradlew

What did you do?

Executed a SQL manually because we had a postgres issue in bigger landscapes
Example

select count from users where ((LOWER(username) = LOWER('mariassa') and LOWER(identity_zone_id) = LOWER('uaa')))

With a DB ide you can execute

EXPLAIN ANALYZE select count from users where ((LOWER(username) = LOWER('mariassa') and LOWER(identity_zone_id) = LOWER('uaa')))

What did you expect to see? What goal are you trying to achieve with the UAA?

Expected is an indexed scan in the query plan to ensure that we run with the index

What did you see instead?

Sequenced scan is done because we have no lower index on idz and origin, see
https://github.com/cloudfoundry/uaa/blob/develop/server/src/main/resources/org/cloudfoundry/identity/uaa/db/postgresql/V2_0_4__Identity_Provider_Adjustments.sql#L27

Reason found in git log

dca40d9

This is an issue on postgres, on mysql I think no issue, because found serveral similar issues, see
https://github.com/cloudfoundry/uaa/search?q=index+lower&type=issues

[strehle]

@torsten-sap FYI