[
https://issues.apache.org/jira/browse/GUACAMOLE-1573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sergey Shcherbina updated GUACAMOLE-1573:
-----------------------------------------
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
was:
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.
> 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
> Priority: Minor
>
> 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
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
