---
title: "How to optimize a Postgres query"
slug: "optimize-postgres-query"
category: "howto"
tags: ["howto", "postgres", "performance", "explain", "indexing", "tutorial", "backend"]
status: "stable"
last_updated: 2026-05-14
summary: "Find the offender with pg_stat_statements, read the plan with EXPLAIN ANALYZE, add a targeted index, and verify the new plan matches expectations."
related: ["[[backend/postgres]]", "[[backend/postgres-explain]]", "[[backend/postgres-indexes]]", "[[backend/postgres-vacuum]]", "[[backend/postgres-partitioning]]", "[[backend/observability]]", "[[howto/debug-postgres-slow-query]]", "[[cheatsheets/postgres-explain]]"]
---

## Overview

Most Postgres performance problems fall into one of four categories: missing index, stale statistics, too-large result set, or wrong join order. This guide takes a query from identified-as-slow to verified-as-fast using `pg_stat_statements`, `EXPLAIN ANALYZE`, and a targeted index. For the deep reference on reading plans see [[backend/postgres-explain]]; for index design rules see [[backend/postgres-indexes]].

## Prerequisites

- Postgres 14 or newer. Postgres 16+ exposes the `pg_stat_io` view which adds I/O breakdown; the steps work on older versions without it.
- `pg_stat_statements` extension enabled. See [[howto/debug-postgres-slow-query]] step 1 for setup.
- A connection with `pg_read_all_stats` privilege or superuser access.
- Production-shaped data in the database. A table with ten rows will not reproduce a production plan.

## Steps

### 1. Identify the top offenders

Sort by `total_exec_time` to find queries that cost the most cumulative time, not just the slowest single execution.

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

Pick a query with high `total_ms` and many `calls`. That is where a fix pays off most. Note the normalized query text; you will need real parameter values next.

### 2. Capture a representative execution

Get the actual query with real parameters from the application log or from `log_min_duration_statement`:

```ini
# postgresql.conf — temporary for diagnosis only
log_min_duration_statement = 100   # log queries over 100 ms
```

Reload config without restart: `SELECT pg_reload_conf();`

### 3. Run EXPLAIN ANALYZE

Execute the plan with real parameters. Always use `ANALYZE` (which runs the query) and `BUFFERS` (which shows cache hits vs disk reads).

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, status, total_cents
FROM orders
WHERE customer_id = 4291
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
```

Read the output bottom-up: leaf nodes run first. Key signals:

- `Seq Scan` on a large table with a selective predicate: missing index.
- `actual rows` far exceeds `rows` estimate: stale statistics; run `ANALYZE`.
- `Buffers: shared read=N` still high on the second run: working set does not fit in `shared_buffers`.

The [[cheatsheets/postgres-explain]] page decodes every node type.

### 4. Add a targeted index

For the example query, the predicate filters on `customer_id` and `status`, then sorts by `created_at`. A composite index ordered equality-first, range-last covers the predicate and avoids a sort.

```sql
CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
ON orders (customer_id, status, created_at DESC)
INCLUDE (total_cents);
```

Rules:
- Use `CONCURRENTLY` in production to avoid a table lock.
- Equality predicates come before range predicates in the column list.
- `INCLUDE` adds payload columns so the index covers the projection; the plan becomes an Index Only Scan.
- One index per query pattern; do not add a redundant index if the leftmost columns already appear in an existing index.

See [[backend/postgres-indexes]] for partial indexes, expression indexes, and covering index tradeoffs.

### 5. Re-run and verify the plan

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents
FROM orders
WHERE customer_id = 4291
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
```

The new plan should show:

```
Index Only Scan using orders_customer_status_created_idx on orders
  (actual time=0.123..0.456 rows=20 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=4 read=0
```

`Heap Fetches: 0` confirms the index is covering. `read=0` on a warm cache confirms the working set fits. If `read` is still non-zero, run `VACUUM ANALYZE orders`; the visibility map may not be current. See [[backend/postgres-vacuum]].

### 6. Measure under real load

Reset `pg_stat_statements` and let the application run for 30 minutes:

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

Re-query the top 10. The fixed query should drop off the list. If it reappears, the planner is choosing a different plan under different parameter values. Capture those parameters and repeat the cycle.

## Verify it worked

```sql
-- Plan uses the new index with low actual time.
EXPLAIN (ANALYZE, BUFFERS) <the query>;

-- Query has dropped from the top 10 slow list.
SELECT query FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- No unexpected table locks from the CREATE INDEX.
SELECT pid, query FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
```

## Common errors

- **Index is created but not used**. The predicate is not selective enough; Postgres prefers a seq scan. Run `ANALYZE orders` to refresh statistics, then recheck.
- **`CONCURRENTLY` fails with "in a transaction"**. Run `CREATE INDEX CONCURRENTLY` outside a `BEGIN` block.
- **Plan is great in dev, slow in prod**. Statistics differ between environments. Compare `pg_stats.n_distinct` and run `ANALYZE` on the production table.
- **`mean_exec_time` is low but p99 is high**. A lock contention spike, not a plan problem. Check `pg_locks` and `pg_stat_activity` for blocking queries.
- **Index bloat after adding the index**. Monitor with `pg_stat_user_indexes`; if `idx_tup_read` is near zero after a week, the index is unused and wastes write overhead. Drop it.

## Related

- [[backend/postgres]]
- [[backend/postgres-explain]]
- [[backend/postgres-indexes]]
- [[backend/postgres-vacuum]]
- [[backend/postgres-partitioning]]
- [[backend/observability]]
- [[howto/debug-postgres-slow-query]]
- [[cheatsheets/postgres-explain]]