Missing index for username and identity_zone on users table (Postgres)

[torsten-sap]

What version of UAA are you running?

75.9.0

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('marissa') and LOWER(identity_zone_id) = LOWER('uaa')))

You may execute explain analyze.

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.

Reason found in git log

Second query that way adapted in the following commit: dca40d9

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/180190790

The labels on this github issue will be updated when the story is started.

[strehle]

@torsten-sap since this seemed to be related to dca40d9
The issue #1663 solved one, now additional one, but is there still one issue missing ? because there were 3 SQL changed to lower....?

[torsten-sap]

Yes, we should also check the third one. In the best case changing the order of the recently created index might also solve the others.

[strehle]

@torsten-sap I verified with a colleague in a larger DB that existing index from
issue #1713 and fix
https://github.com/cloudfoundry/uaa/pull/1663/files

solves also this.
If you have other findings , please re-open

[torsten-sap]

Finding from productive CF UAA showed that there are situations where the existing index is not hit. The proposed index improved the situation significantly. Thus, reopening the issue.

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/180664577

The labels on this github issue will be updated when the story is started.

[torsten-sap]

Affected queries:
select count(*) from users where ((LOWER(username) = LOWER($1) AND LOWER(identity_zone_id) = LOWER($2)))

select id,version,created,lastModified,username,email,givenName,familyName,active,phoneNumber,verified,origin,external_id,identity_zone_id,salt,passwd_lastmodified,last_logon_success_time,previous_logon_success_time from users where ((LOWER(username) = LOWER($1) AND LOWER(identity_zone_id) = LOWER($2))) ORDER BY userName ASC limit ? offset ?

[torsten-sap]

Command for index creation:
create index concurrently users_username_idz_tmp on users(LOWER(username), LOWER(identity_zone_id))

[strehle]

@torsten-sap thanks, so can you please create PR for that