[GUACAMOLE-1573] Slow selection of users on scale on Postgres

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.4.0
• Component/s: guacamole-auth-jdbc-postgresql
• Assignee: Unassigned
• Reporter: Sergey Shcherbina
• Created: 31/Mar/22 11:25
• Updated: 31/Mar/22 11:30

Description

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.

Fix is already prepared here - https://github.com/apache/guacamole-client/pull/711