How to make a slow Postgres query fast, in order

Rule zero: do not change anything yet

The most expensive mistake in query tuning is adding an index based on a guess. Indexes are not free. Every one is maintained on every insert, update and delete, forever, and a speculative index that did not help is a permanent tax nobody remembers to remove.

Read the plan first. Always.

Step 1. Get a plan that reflects reality

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

ANALYZE executes the query and reports what actually happened. BUFFERS shows how much data was read and whether it came from cache or disk, which distinguishes a query that is slow from one that is merely cold.

Run it against production scale data. A plan taken on a development database with two thousand rows tells you almost nothing about the same query over two million, because the planner will choose a completely different strategy at that size.

Step 2. Find where the estimate diverged from reality

This is the actual diagnostic and everything else follows from it.

Every node in the plan shows rows=N estimated and actual rows=M. Scan for the node where those two diverge most sharply.

Nested Loop  (cost=... rows=12) (actual rows=48213 ...)
                       ^^^^^^^              ^^^^^^^^^^^
                       planner thought 12   reality was 48,213

That divergence is the root cause. The planner chose a nested loop because it expected twelve rows, which is an excellent choice for twelve rows and a catastrophe for forty eight thousand. The nested loop is not the bug. The bad estimate is the bug, and fixing the loop without fixing the estimate just moves the problem.

Common causes of a bad estimate, in order of how often I find them:

• Stale statistics. Run ANALYZE tablename and re-check before doing anything else.
• Correlated columns the planner assumes are independent, so it multiplies selectivities and gets a number far too small. Fixed with extended statistics.
• A function or a cast wrapped around a column, which makes the planner unable to use its statistics at all.
• A query against a view that hides a join it cannot see through.

Step 3. Fix the access path before reaching for an index

Look at how each table is being read.

A sequential scan on a large table with a selective predicate usually means the predicate cannot use an index. The most common reason is that the column is wrapped:

WHERE date_trunc('day', created_at) = '2026-07-26'   -- cannot use an index on created_at
WHERE created_at >= '2026-07-26'                      -- can
  AND created_at <  '2026-07-27'

Rewriting the predicate to leave the column bare is free, requires no new index, and is very often the entire fix.

A sequential scan is also correct and desirable when the query genuinely returns a large fraction of the table. Do not index your way out of that. If you are reading forty percent of the rows, reading them in order is the fastest thing available.

Step 4. Add the narrowest index that serves the whole query

Only now, and only if the plan says so.

Column order in a composite index matters and follows a rule: equality predicates first, then the range predicate, then anything you sort by.

-- WHERE tenant_id = $1 AND status = $2 AND created_at > $3 ORDER BY created_at DESC
CREATE INDEX ON invoices (tenant_id, status, created_at DESC);

Two refinements worth knowing.

Partial indexes when the query always filters on the same subset. Much smaller, much cheaper to maintain:

CREATE INDEX ON invoices (tenant_id, created_at DESC) WHERE status = 'PENDING';

Covering indexes when adding one or two columns lets the query be answered from the index alone, avoiding the trip to the heap. Watch the size, because a wide covering index stops being cheap.

In a multi-tenant system, the tenant column goes first in almost every index, because it is in almost every predicate.

Step 5. Re-measure, and measure the right thing

Re-run EXPLAIN ANALYZE. Confirm the plan actually changed. An index that exists and is not used is pure cost, and the planner will ignore one whose column order does not match the query.

Then measure the distribution rather than a single run:

• Median, which is what most requests feel.
• Ninety fifth percentile, which is what your unhappiest users feel and what wakes people up.
• Cold cache, by running it after the buffers have been evicted, because the first request after a deploy is somebody’s request.

A change that improves the median and worsens the tail has made the system worse for the people already having the worst time. That trade is common and almost always accidental.

Step 6. Pay for the index honestly

Before keeping it, check what it cost.

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'invoices'
ORDER BY idx_scan;

Any index with idx_scan near zero after a week of real traffic is not earning its keep and should be dropped. Doing this audit quarterly is how you avoid a table with fourteen indexes where four are doing the work.

The order, condensed

1. EXPLAIN (ANALYZE, BUFFERS) at production data volume.
2. Find the largest gap between estimated and actual rows.
3. Fix statistics first, since it is free.
4. Unwrap predicates so columns can be indexed.
5. Only then add the narrowest index that serves predicate and sort.
6. Re-plan and confirm the index is actually used.
7. Measure median, ninety fifth percentile, and cold.
8. Audit index usage later and drop what is not scanned.

What good looks like

The plan uses the index you expected. Estimated and actual rows are within an order of magnitude at every node. The ninety fifth percentile improved as much as the median. And you can say in one sentence why the query is now fast, which means the next person can too.