Find slow queries
See what the database spends its time on with query insights, act on its findings, end runaway sessions, and log slow statements with their plans.
The Queries tab is query insights: statements, findings with an action each, running sessions, the slow query log and table health. Replace ewdb-0123456789ab with your database's id.
Prerequisites
pg_stat_statementsenabled under Extensions (see Enable an extension). The first numbers appear a minute or two later.rds:DescribeDBQueryInsightsto read;rds:CancelDBSessionto cancel a statement or end a session;rds:ModifyDBQueryInsightsto change the slow query log or show values. Withoutrds:CancelDBSessionthe sessions have no actions menu; withoutrds:ModifyDBQueryInsightsthe slow query log's controls are disabled.- For Running now, Tables and indexes, a statement's plan and the slow query log settings,
the database is available. Otherwise they answer 409
This database is <status>; it answers once it is available. - In the console, also
rds:DescribeDBInstancesto open the database's page (without it the page is not found).
Look at the statements and findings
- Open the database, then choose Queries.
- Choose Statements, and choose a statement to open it.
- Choose Findings for what to do about it, and Tables and indexes for table health.
dcloud rds query-insights get ewdb-0123456789ab --range 24h
dcloud rds query-insights get-statement ewdb-0123456789ab statement-id
dcloud rds query-insights list-tables ewdb-0123456789abGET /rds/api/instances/ewdb-0123456789ab/insights?range=24hNot available in Terraform. Query insights are read, not configured.
- Statements. Once a minute the platform reads each node's
pg_stat_statementscounters and stores what changed since the minute before, for the 50 statements that took the most time. The chart shows statement time per minute (1 h), 15 minutes (24 h) or 2 hours (7 d), stacked by the five busiest statements. Each statement shows calls, total and mean time, the slowest call and the spread, rows per call, how much came from cache, temporary files and WAL written, the database and role, and when it was first and last seen, with a trend line. Postgres does not count percentiles, so there is no p95; the slowest call and the spread arepg_stat_statements' own since its last reset. - Findings, each with its numbers and an action: slow on average, slow sometimes (a large
spread), a missing index (from the statement's generic plan,
EXPLAIN (GENERIC_PLAN), neverANALYZE, with aCREATE INDEX CONCURRENTLYto copy), full scans of large tables, unused and duplicate indexes, sorts that spill to disk (work_mem), N+1 (aSELECTof about one row, under 1 ms, run 600 or more times a minute), long-running statements, sessions idle in a transaction, sessions waiting for a lock, and vacuum overdue with a bloat estimate. Suggested SQL is never run for you. - Tables and indexes: rows, dead rows, size, full and index scans and the last vacuum of each table, with the table and index findings.
End a runaway statement or session
Running now shows your sessions every five seconds, with what they wait for and who blocks them. The platform's own sessions are not shown and cannot be ended.
- On the Queries tab, choose Running now.
- Open the session's actions menu (Actions for session 12345) and choose Cancel statement (only for an active session) or End session.
- In the dialog, choose Cancel statement or End session again, or Keep it running.
dcloud rds query-insights list-sessions ewdb-0123456789ab
dcloud rds query-insights cancel-session ewdb-0123456789ab pid --how cancel--how terminate ends the session.
POST /rds/api/instances/ewdb-0123456789ab/insights/sessions/12345
{ "how": "cancel" }Not available in Terraform. Sessions are live state, not configuration.
Cancel statement is pg_cancel_backend: the application gets an error, its transaction rolls
back and it keeps its connection. End session is pg_terminate_backend: the connection closes
and an open transaction rolls back. Either is recorded in Activity (phase sessions) and the
audit log. Refusals (409): No such session (it may have ended).,
Only client sessions can be cancelled here., That is the platform’s own session.
Log slow statements
- On the Queries tab, choose Slow query log.
- For Log statements, choose Slower than … a threshold (100 ms, 250 ms, 500 ms, 1 s or 5 s), or Off.
- (Optional) Turn on Plans of slow statements. On a cluster the switch is disabled (Single-node databases for now).
- (Optional) Turn on Show values and plans.
Each control applies as soon as you change it; there is no Save.
dcloud rds query-insights update-settings ewdb-0123456789ab --slow-log-ms 500 --auto-explain
dcloud rds query-insights get-slow-log ewdb-0123456789ab --range 24hPATCH /rds/api/instances/ewdb-0123456789ab/insights/settings
{ "slowLogMs": 500, "autoExplain": true }Not available in Terraform. Set `log_min_duration_statement` with `dcloud_db_parameters` instead; the other settings are not in Terraform.
The threshold is log_min_duration_statement (also under Parameters; -1 turns it off):
Postgres writes every slower statement to the server log. Plans of slow statements loads
auto_explain for new connections, with no restart, on single-node databases for now; on a
cluster the API answers 409
auto_explain is switched here on single-node databases only for now. Plans are logged for
statements slower than the threshold (1 s when the log is off). The tab lists what was captured in
the range.
Privacy
Statements are shown normalised: every value is replaced by ?. Plans and live statements can
carry values from your data, so they are shown only after Show values and plans is turned on
(plansWithValues, rds:ModifyDBQueryInsights); while it is on, every read of the tab is recorded
in the audit log.
Alarm on a slowdown
Every five minutes each node records query_slowdown: how many times slower the most slowed-down
statement ran in the last 15 minutes than over its own previous 7 days. Only statements with at
least 20 recent and 100 earlier calls that now take 1 ms or more per call count, so a microsecond
lookup's jitter does not; the value is capped at 1000, and 1 is as usual. An alarm on
query_slowdown max > 3 tells you when a statement got three times slower.
Error Tracking
Each statement has a fingerprint (sqlfp1:…) that Error Tracking computes the same way for the
database spans of your traces, so a statement links to the traces that ran it and back.
Result
Samples are kept 7 days, at most 50 statements per node per minute. The platform's own statements
are never counted. MCP tools are named
dcloud_rds_query-insights_*.
Related
Related
- GET /rds/api/instances/{id}/insights
- GET /rds/api/instances/{id}/insights/statements/{statementId}
- GET /rds/api/instances/{id}/insights/tables
- GET /rds/api/instances/{id}/insights/sessions
- POST /rds/api/instances/{id}/insights/sessions/{pid}
- GET /rds/api/instances/{id}/insights/slow-log
- PATCH /rds/api/instances/{id}/insights/settings