# Find slow database queries

See which database statements your app spends its time on, how often each runs, its slowest calls, and where it runs as an N+1 loop.

The statements come from the db spans of your sampled transactions, so tracing must be on (see
[View traces and transactions](view-traces-and-transactions.md)). The SDK's integration for your database client
(Postgres, MySQL, Prisma and others) creates those spans. To see every statement a Deutsche Cloud
PostgreSQL database runs, not only those of sampled requests, and with no traces at all, connect
the database to the project (see [Connect a database to a project](connect-a-database.md)): the
**Queries** tab then marks each statement **Database + traces**, **Database only** or **Traces
only**. This page describes the tab without a connected database. Replace _project-id_ with
the project's id and _fingerprint_ with a statement's fingerprint (`sqlfp1:` and 16 hexadecimal
characters).

## Prerequisites

- Permission for `errors:GetEvents` on the project. Without it, the **Queries** tab says "You can
  see this project but not its queries."
- In the console, also `errors:ListProjects` on the project to open it (without it the project is
  not found).

## List the statements

Statements are grouped by their text without values, so `WHERE id = 7` and `WHERE id = 8` are one
statement, and the **Queries** tab keeps and shows only that text. The db spans of the stored
transactions keep the statement as the SDK sent it.

**Console**
1. Open the project, then the **Queries** tab.
2. Choose **24 h** or **7 days**.

Two charts sit above the table. **Calls per minute** shows the calls of sampled requests, or,
with a database connected, every call the database measured. **Duration** shows the p95 of the
slowest call of each sampled request (a statement run once per request is exact; an N+1 reads
high), and with a database connected also the database's mean per call as a dashed line. Each
line's legend says which of the two it is.

Each row shows the **Statement**, its **Time share** of all database time in the project, the
**Calls/min** (calls divided by the minutes of the range; marked **sampled** when only traces
counted them), the **Total**, the **Calls**, the **Mean** and the **Slowest** call, and, when it
ran as an N+1, in how many transactions.

**CLI**
```sh
dcloud errors queries list project-id --range 7d
```

**API**
```http
GET /errors/api/projects/project-id/queries?range=7d
```

Rows come by total time spent, up to `limit` (50 by default, at most 200).

An N+1 is a transaction that ran the same statement five or more times one after another under the
same parent span. That is usually a loop that should be one query: a join, or `WHERE id IN (…)`.

## Open one statement

**Console**
1. On the **Queries** tab, choose a statement.

The page shows the statement's calls, calls per minute, mean, p95 and N+1 count, the same two
charts for this statement alone (with a database connected, **Calls per minute** draws both
lines: the gap between them is your sampling rate), and then:

- **Measured by the database**: for each connected database you may read with
  `rds:DescribeDBQueryInsights` that ran it, what `pg_stat_statements` counted in the range:
  calls, calls per minute, mean, total, rows per call, cache hit and temporary data written, by
  database and role. **Max** and **Std. deviation** are since `pg_stat_statements` was last
  reset, not only the range. A link opens the statement in the database's Query insights.
- **The database at** _time_: the database in the minute of the slowest request, or of the
  request you chose with **Database at that minute** (the minute in the middle of its slowest
  call, so a call that waited a minute on a lock shows the minute it waited in): connections in use against
  `max_connections`, active and idle-in-transaction sessions, sessions waiting on a lock, the
  longest open transaction, the cache hit, its calls and statement time, and the five statements
  that took the most time in that minute. Each number is compared with the median of the hour
  around it ("usually 3"). Sessions and locks are counted once, when the minute is sampled, so a
  lock shorter than a minute can fall between two counts. A minute the database was not sampled
  in says "No snapshot for this minute". Time a statement waits for a table lock before it starts
  is not in `pg_stat_statements`: a call the trace shows taking seconds can have a mean of under a
  millisecond in the database's numbers, and the lock waits of its minute are the place to look.
- **Slowest requests**, **Samples** (the 20 most recent sampled requests that ran it) and **N+1**;
  each opens its trace.
- **By project** and **Where it ran**, with a link to the database when it is one of yours.

A statement only the database ran (a background job, a migration, another app) has a page too: it
says "No sampled request ran this statement." A statement no transaction ran and no database
measured in the range says "This statement was not seen in the last 24 hours." with a link to
**Show 7 days**. In a trace, a db span's details link to **The database at this request's
minute**.

**CLI**
```sh
dcloud errors queries get --fp fingerprint --range 24h
```

`--request` picks the request whose minute `minute` describes (its transaction event id), `--at`
a minute instead.

**API**
```http
GET /errors/api/queries?fp=sqlfp1:0123456789abcdef&range=24h
```

The statement across every project whose events you may read; `project` keeps one. Through the
API this needs `errors:GetEvents` on the service, `rn:ews:de1:<organization id>:errors:*` (else
`403`), and answers `404` when no project you may read ran it and no database you may read
measured it in the window. `databases`, `chart` and `minute` carry what the console shows; a
database you may not read with `rds:DescribeDBQueryInsights` is left out of all three.

## Related

- [Connect a database to a project](connect-a-database.md)
- [Find slow queries](/docs/rds/guide/find-slow-queries) in Postgres
- [View traces and transactions](view-traces-and-transactions.md)
- `errors.queries.list` · `dcloud errors queries get`
