[Bug]: A slow SQl query is causing high database load

Is there an existing issue for this?

[X] I have searched the existing issues

OS/Web Information

N/A

Steps to Reproduce

We found this issue by checking AWS RDS metrics.

Expected

Reasonable query time and database load.

Actual

Data shows this query is causing high db load and average query time is 42s. -- name: GetDeploymentDAUs :many SELECT (created_at at TIME ZONE cast($1::integer as text))::date as date, user_id FROM workspace_agent_stats WHERE connection_count > ? GROUP BY date, user_id ORDER BY date ASC

Logs

No response

Screenshot/Video

Does this issue happen in VS Code or GitHub Codespaces?

[ ] I tested this in native VS Code.
[ ] This does not happen in native VS Code.
[ ] I tested this in GitHub Codespaces.
[ ] This does not happen in GitHub Codespaces.

Are you accessing code-server over a secure context?

[ ] I am using a secure context.

Notes

We keep getting AWS RDS high load alert. By checking the metrics, we found this query is very slow and takes up most of the db resource. We may have a query or table to optimise here.

-- name: GetDeploymentDAUs :many SELECT (created_at at TIME ZONE cast($1::integer as text))::date as date, user_id FROM workspace_agent_stats WHERE connection_count > ? GROUP BY date, user_id ORDER BY date ASC

coder / code-server