# 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_statements` enabled under **Extensions** (see
  [Enable an extension](enable-an-extension.md)). The first numbers appear a minute or two later.
- `rds:DescribeDBQueryInsights` to read; `rds:CancelDBSession` to cancel a statement or end a
  session; `rds:ModifyDBQueryInsights` to change the slow query log or show values. Without
  `rds:CancelDBSession` the sessions have no actions menu; without `rds:ModifyDBQueryInsights` the
  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:DescribeDBInstances` to open the database's page (without it the page is
  not found).

## Look at the statements and findings

**Console**
1. Open the database, then choose **Queries**.
2. Choose **Statements**, and choose a statement to open it.
3. Choose **Findings** for what to do about it, and **Tables and indexes** for table health.

**CLI**
```sh
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-0123456789ab
```

**API**
```http
GET /rds/api/instances/ewdb-0123456789ab/insights?range=24h
```

**Terraform:** not available. Query insights are read, not configured.

- **Statements.** Once a minute the platform reads each node's `pg_stat_statements` counters 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 are `pg_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)`, never
  `ANALYZE`, with a `CREATE INDEX CONCURRENTLY` to copy), full scans of large tables, unused and
  duplicate indexes, sorts that spill to disk (`work_mem`), N+1 (a `SELECT` of 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.

**Console**
1. On the **Queries** tab, choose **Running now**.
2. Open the session's actions menu (**Actions for session 12345**) and choose
   **Cancel statement** (only for an active session) or **End session**.
3. In the dialog, choose **Cancel statement** or **End session** again, or
   **Keep it running**.

**CLI**
```sh
dcloud rds query-insights list-sessions ewdb-0123456789ab
dcloud rds query-insights cancel-session ewdb-0123456789ab pid --how cancel
```

`--how terminate` ends the session.

**API**
```http
POST /rds/api/instances/ewdb-0123456789ab/insights/sessions/12345
{ "how": "cancel" }
```

**Terraform:** not available. 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

**Console**
1. On the **Queries** tab, choose **Slow query log**.
2. For **Log statements**, choose **Slower than …** a threshold (100 ms, 250 ms, 500 ms, 1 s or
   5 s), or **Off**.
3. (Optional) Turn on **Plans of slow statements**. On a cluster the switch is disabled
   (**Single-node databases for now**).
4. (Optional) Turn on **Show values and plans**.

Each control applies as soon as you change it; there is no Save.

**CLI**
```sh
dcloud rds query-insights update-settings ewdb-0123456789ab --slow-log-ms 500 --auto-explain
dcloud rds query-insights get-slow-log ewdb-0123456789ab --range 24h
```

**API**
```http
PATCH /rds/api/instances/ewdb-0123456789ab/insights/settings
{ "slowLogMs": 500, "autoExplain": true }
```

**Terraform:** not available. 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

- [Read the server log](read-the-server-log.md)
- [Tune parameters](tune-parameters.md)
- `rds.query-insights.get` · `dcloud rds query-insights get`
