Sergey Shcherbina created GUACAMOLE-1573:
--------------------------------------------
Summary: Slow selection of users on scale on Postgres
Key: GUACAMOLE-1573
URL: https://issues.apache.org/jira/browse/GUACAMOLE-1573
Project: Guacamole
Issue Type: Improvement
Components: guacamole-auth-jdbc-postgresql
Affects Versions: 1.4.0
Reporter: Sergey Shcherbina
I started to notice performance degradation on given queries:
* Select multiple users by username
* Select multiple users by username only if readable
* Select single user by username
When user has large number of records in `guacamole_user_history` execution
time for query become unacceptable, seconds vs milliseconds in regular case.
For example select for user with 4.5 million rows executes around 5sec vs 10ms
on regular user. This is because index on start date is not working properly on
left join + max.
It can be solved by dropping left join and replacing `MAX(start_date)` with
`select last record in history by start_date`. In that case index is properly
used.
This solution work faster in all of the real scenarios. It can be slower only
in case of selecting all of users.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
