---
title: "How to debug a slow Postgres query"
slug: "debug-postgres-slow-query"
category: "howto"
tags: ["howto", "postgres", "performance", "explain", "tutorial", "backend"]
status: "stable"
last_updated: 2026-05-14
summary: "Enable pg_stat_statements to find the offender, run EXPLAIN ANALYZE BUFFERS, add the right index, and verify the new plan before declaring done."
related: ["[[backend/postgres]]", "[[backend/postgres-explain]]", "[[backend/postgres-indexes]]", "[[backend/postgres-vacuum]]", "[[backend/observability]]", "[[howto/optimize-core-web-vitals]]"]
---

## Overview

A slow query in Postgres has a small number of likely causes: missing index, stale statistics, cold cache, wrong join order, or a query that is just doing too much work. This guide finds the offender, reads the plan, fixes one cause, and confirms the fix sticks. The umbrella rules live in [[backend/postgres-explain]] and [[backend/postgres-indexes]].

## Prerequisites

- Postgres 14 or newer. Postgres 17 is the assumed baseline; older versions miss WAL reporting in `EXPLAIN`.
- A user with `pg_read_all_stats` or superuser access. Reading `pg_stat_statements` requires it.
- A reproducible slow query. "Sometimes it is slow" is not a starting point; capture the query text and parameters first.
- Production-shaped data. Ten rows in dev will not show what 10 million rows in prod do.

## Steps

### 1. Enable `pg_stat_statements`

Edit `postgresql.conf`, add to `shared_preload_libraries`, restart.

```ini
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
```

Then, once, in the target database:

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

The view starts collecting on next restart.

### 2. Find the offender

The top queries by `total_exec_time` are the right tuning targets. Mean time hides queries that are cheap individually but run a million times an hour.

```sql
SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows,
  query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;
```

Pick the first query with high `total_ms` and high `calls`. That is the one with the biggest payback.

### 3. Run `EXPLAIN ANALYZE` with `BUFFERS`

`EXPLAIN` returns the planner's estimate; `EXPLAIN ANALYZE` runs the query and reports actual rows and time; `BUFFERS` adds cache-hit and disk-read counts.

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, total_cents FROM orders
WHERE user_id = $1 AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 50;
```

Read the plan top-down to follow data flow; debug bottom-up because the leaves run first. See [[backend/postgres-explain]] for the full grammar.

### 4. Spot the failure mode

Three patterns cover most slow queries.

- Sequential scan on a large table with a selective predicate. The planner did not find a useful index, or the index was disqualified by a function call like `LOWER(email) = $1` without a matching expression index.
- Index scan with `actual rows` ten times the estimate. Statistics are stale. Run `ANALYZE <table>` or raise `default_statistics_target`.
- High `Buffers: shared read=N` on the second run. The working set does not fit in `shared_buffers`. Either raise the setting, narrow the query, or partition cold data.

### 5. Add the right index, not any index

The right index is the one that makes the predicate selective and covers the projection.

```sql
-- Predicate on (user_id, created_at) with an ORDER BY created_at DESC.
CREATE INDEX CONCURRENTLY orders_user_created_idx
ON orders (user_id, created_at DESC)
INCLUDE (total_cents);
```

- `CONCURRENTLY` avoids the long lock; required for production.
- Column order matches the predicate equality first, then the range.
- `INCLUDE` adds non-key columns so the index covers the projection and the plan becomes an index-only scan.

See [[backend/postgres-indexes]] for the full rules.

### 6. Re-run `EXPLAIN ANALYZE` and confirm

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents FROM orders
WHERE user_id = $1 AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 50;
```

The new plan should show `Index Only Scan using orders_user_created_idx` near the top, `actual time` under 5 ms, and `Buffers: shared hit=N read=0` on a warm cache. If `read` is still large, autovacuum has not visited the table; run `VACUUM ANALYZE orders`. See [[backend/postgres-vacuum]].

### 7. Reset and re-measure under real load

`pg_stat_statements_reset()` clears the stats; let the application run for an hour, then re-query the top 20. The fixed query should be off the list. If it is back, the planner is choosing a different plan under parameter values you did not test; capture those and repeat.

## Verify it worked

```sql
-- 1. The new plan is an Index Only Scan with low actual time.
EXPLAIN (ANALYZE, BUFFERS) <the query>;
-- expected: "Index Only Scan", "actual time=...ms" under target

-- 2. pg_stat_statements no longer ranks the query in the top 20.
SELECT query FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- 3. Application p95 latency drops on the affected route.
-- Check the dashboard wired to [[backend/observability]].
```

## Common errors

- Missing index after `CREATE INDEX CONCURRENTLY`. The command failed silently in a transaction; `CONCURRENTLY` cannot run inside `BEGIN`. Run it outside any transaction.
- `pg_stat_statements` shows the wrong query. Statements are normalized; the literal `?1` placeholder hides parameters. Use the `parameterization` view (Postgres 16+) or grab the text from the application log.
- New index is not used. Postgres prefers a sequential scan for non-selective predicates. Run `ANALYZE`; if it still does not pick the index, check that the column types in the query match the index.
- Plan looks great in dev, slow in prod. Statistics differ. Compare `pg_stats.n_distinct` and `most_common_vals` between environments.
- Query is fast at 5 ms warm, slow at 500 ms cold. IO problem, not plan problem. Increase `shared_buffers` or partition the cold range; see [[backend/postgres-partitioning]].

## Related

- [[backend/postgres]]
- [[backend/postgres-explain]]
- [[backend/postgres-indexes]]
- [[backend/postgres-vacuum]]
- [[backend/observability]]
- [[backend/postgres-partitioning]]