# What Is pg_stat_statements? — Finding the Slowest Queries in PostgreSQL

Canonical: https://commareports.com/glossary/pg-stat-statements
Published: 2026-11-10

> pg_stat_statements is the PostgreSQL extension that records execution statistics for every normalised query. How to enable it, the columns that matter (calls, total_exec_time, mean_exec_time, rows, shared_blks_read), the top-queries SQL, and what it can't tell you.

# pg_stat_statements

**`pg_stat_statements`** is a PostgreSQL extension that keeps running
statistics for every statement the server executes: how many times it
ran, the total and average time it took, how many rows it returned and
how many pages it read from cache or disk. It ships with PostgreSQL (in
`contrib`) and is the usual starting point for the question _"which
queries should we optimise?"_ — before
[EXPLAIN ANALYZE](/glossary/explain-analyze) answers _"why is this one
slow?"_

## Normalised queries

The extension doesn't store each execution. It strips constants out of
the statement and groups by the result, so

```sql
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 7;
```

become one row, `SELECT * FROM orders WHERE customer_id = $1`, with
`calls = 2`. Each row is identified by `userid`, `dbid` and `queryid`
(a hash of the parsed query), so the same text run by two roles is two
rows.

## Enabling it

The library has to be loaded when the server starts, because it
allocates shared memory:

```ini
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
```

Restart, then in the database you'll query it from:

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

Many managed PostgreSQL services already preload the library; there
only the `CREATE EXTENSION` step is needed. Useful settings:
`pg_stat_statements.max` (statements tracked, default 5000 — the least
used are evicted beyond that), `pg_stat_statements.track` (`top` by
default; `all` also counts statements inside functions) and
`pg_stat_statements.track_planning` (off by default; turn it on to fill
the planning-time columns).

## The columns that matter

| Column                                 | Meaning                                                          |
| -------------------------------------- | ---------------------------------------------------------------- |
| `query`                                | Normalised statement text, constants as `$1`, `$2`               |
| `calls`                                | How many times it ran                                            |
| `total_exec_time`                      | Total execution time, milliseconds                               |
| `mean_exec_time`                       | Average per call (also `min_`, `max_`, `stddev_exec_time`)       |
| `rows`                                 | Total rows returned or affected                                  |
| `shared_blks_hit` / `shared_blks_read` | Pages found in PostgreSQL's cache vs fetched from the OS or disk |
| `temp_blks_written`                    | Sorts and hashes that spilled to disk (`work_mem` too small)     |
| `total_plan_time`                      | Planning time — only filled with `track_planning = on`           |

PostgreSQL 13 renamed `total_time` and `mean_time` to
`total_exec_time` and `mean_exec_time`, so older blog posts and
dashboards fail with _column "total_time" does not exist_.

## The top-queries query

```sql
SELECT calls,
       round(total_exec_time)                        AS total_ms,
       round(mean_exec_time::numeric, 2)             AS mean_ms,
       round(100 * total_exec_time
             / sum(total_exec_time) OVER (), 1)      AS pct,
       rows,
       left(query, 100)                              AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
```

Sort by **total** time to find what costs the server most — a 2 ms
lookup called a million times outranks a 5-second report run twice.
Sort by **mean** time when you care about one request's latency. Two
more views worth a glance: queries with a poor cache ratio
(`shared_blks_read` large next to `shared_blks_hit`), and anything with
`temp_blks_written > 0`.

To measure a change, reset first and compare a clean window:

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

## What it can't tell you

- **The plan.** It records how long a statement took, not how it ran.
  Take the query text, fill in realistic values for `$1`, and run
  `EXPLAIN (ANALYZE, BUFFERS)` — or enable `auto_explain` to log the
  plans of slow statements as they happen.
- **One slow execution.** Averages hide outliers; `max_exec_time` shows
  the worst, but not when or with which parameters.
- **Lock waits as a cause.** Time spent waiting for a lock is counted in
  execution time; `pg_stat_activity` and `pg_locks` show who blocked
  whom.
- **History.** The numbers accumulate from the last reset or restart.
  For trends, snapshot the view on a schedule and diff the snapshots.

## From the top query to a fix

Once `pg_stat_statements` has named the query, `EXPLAIN ANALYZE` it and
paste the plan into Comma's free
[Postgres EXPLAIN visualizer](/tools/postgres-explain-viewer) to see
which node the time goes to. A Seq Scan reading a whole table to keep a
few rows usually means an index that the query can't use — the causes
are in [Postgres not using the index](/fix/postgres-not-using-index).
Publish the plan as a link for whoever approves the fix, and publish it
again afterwards to keep the before and after side by side.

## Related

- [EXPLAIN ANALYZE](/glossary/explain-analyze) — reading the plan of the query you found
- [Postgres not using the index](/fix/postgres-not-using-index) — the Seq Scan causes, and fixes
- [Postgres EXPLAIN visualizer](/tools/postgres-explain-viewer) — paste a plan, get the report
- [Flame graph](/glossary/flamegraph) — profiling the application side
- [Glossary](/glossary) — every term