# How to identify slow queries in PostgreSQL

> **Quick answer:** Enable the `pg_stat_statements` extension and sort by
> `total_exec_time` (which equals `calls × mean_exec_time`), not by average time.
> The query costing your database the most is usually a *fast* query that runs
> constantly, high `calls`, low `mean_exec_time`, enormous total.  Fix the top of
> that list for the biggest win.

## What makes a query "slow", total time, not average

There are two different questions, and they have different answers:

- **"Which single query is slowest?"** → sort by `mean_exec_time`.
- **"Which query is costing my database the most?"** → sort by `total_exec_time`.

The second one is what you actually want.  A report that takes 3 seconds but runs
twice a day costs 6 seconds of database time.  A 4-millisecond lookup that runs
two million times a day costs over two hours.  The 4ms query is invisible in any
"slowest query" view and is very often the real problem.

`total_exec_time = calls × mean_exec_time`. Optimizing by total time targets the
queries with the largest aggregate footprint, which is where the wins are.

## How do I enable pg_stat_statements?

`pg_stat_statements` is a contrib extension that records aggregated execution
statistics for every normalized query.  It must be loaded at server start:

1.  Add it to `shared_preload_libraries` in `postgresql.conf`:

   ```ini
   shared_preload_libraries = 'pg_stat_statements'
   ```

2.  Restart PostgreSQL (required, the library loads at startup).

3.  Create the extension in the database you want to inspect:

   ```sql
   CREATE EXTENSION pg_stat_statements;
   ```

It then tracks up to `pg_stat_statements.max` statements (5,000 by default),
evicting the least-executed when full.

## How do I find the slow queries?

Rank by cumulative execution time:

```sql
SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
```

On **PostgreSQL 12 and earlier**, the columns are `total_time` and `mean_time`.
**PostgreSQL 13** renamed them to `total_exec_time` / `mean_exec_time` (and added
`total_plan_time` / `mean_plan_time`), because planning and execution are now
timed separately.

Read the top of the list this way:

| Pattern | What it means |
|---|---|
| High `total_exec_time`, high `calls`, low `mean_exec_time` | A cheap query run constantly, the most common real culprit |
| High `total_exec_time`, low `calls`, high `mean_exec_time` | A genuinely heavy query, a report, a big aggregate, a missing index |
| High `rows / calls` | Returning more than the app uses, missing `LIMIT` or over-broad `SELECT`. `rows` is the **cumulative** total across every execution, so divide by `calls` for the per-call figure |

The numbers are **cumulative since the last reset**, not point-in-time.  To
measure a specific window, reset first and let it run:

```sql
SELECT pg_stat_statements_reset();
```

Called with no arguments, that wipes the statistics for **every** query, user,
and database on the instance, including whatever anyone else is currently
measuring.  Scope it when you can: `pg_stat_statements_reset(userid, dbid,
queryid)` resets only what you name.

## Why cannot my AI coding agent just find these?

Because the signal does not exist in your code, it only exists in production's
aggregate statistics.  Your coding agent (Claude Code, Cursor, Copilot) sees the
query in the source: `User.objects.get(id=...)` looks completely fine.  What it
cannot see is that this particular line runs two million times a day and now
dominates total database time.

"Which query costs the most" is an emergent, runtime property of your real
traffic.  It lives in `pg_stat_statements`, not in the repository.  An agent
reasoning only over the code has no way to rank queries by production impact, so it optimizes what *looks* expensive, not what *is* expensive.

## How do I keep slow queries from creeping back?

- Review the top of the `total_exec_time` list on a schedule, after every reset
  window, or weekly in a dashboard.
- Watch for new entries with a fast-growing `calls` count; those are usually a
  freshly shipped N+1 or an un-indexed lookup.
- Track the ratio of `rows` returned to rows the application actually uses.

## How DBGorilla helps

DBGorilla connects to your database read-only and gives your AI coding agent
(Claude Code, Cursor) the real `pg_stat_statements` data, so instead of
guessing from the source, your agent can rank queries by `total_exec_time`,
point at the one dominating production, and explain why.  It reads and explains;
it does not touch production. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**Should I sort pg_stat_statements by mean_exec_time or total_exec_time?**
Sort by `total_exec_time` to decide what to optimize.  It equals
`calls × mean_exec_time`, so it captures cheap-but-frequent queries that dominate
real load. `mean_exec_time` only shows the average cost of a single call.

**How do I enable pg_stat_statements?**
Add it to `shared_preload_libraries`, restart PostgreSQL, then run
`CREATE EXTENSION pg_stat_statements` in the target database.  The restart is
required because the module loads at server start.

**Why did the pg_stat_statements columns change name?**
PostgreSQL 13 separated planning from execution timing: `total_time`/`mean_time`
became `total_exec_time`/`mean_exec_time`, with `total_plan_time`/`mean_plan_time`
added.  Use `total_time`/`mean_time` on 12 and earlier.

**Are the pg_stat_statements numbers point-in-time?**
No.  They are cumulative since the last reset or server start.  Called with no
arguments, `pg_stat_statements_reset()` wipes stats for every query, user, and
database on the instance; scope it with `userid`/`dbid`/`queryid` if you only
need a slice, then measure a known window.

## Related

- [What pg_stat_statements tells you (and what it does not)](https://www.dbgorilla.com/learn/postgres/what-pg-stat-statements-tells-you/)
- [How to find N+1 queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-n-plus-1-queries/)
- [Why did my PostgreSQL query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)