Reading PostgreSQL EXPLAIN: Tune Queries with Evidence

A slow query does not automatically mean a missing index. It may return too much data, use different parameters from a test or run under heavier load. EXPLAIN reveals PostgreSQL's chosen plan so you can form a concrete hypothesis before changing anything.

This article assumes an illustrative orders table with id, customer_id and created_at. Examples belong in a suitable test environment; no production benchmarks or speedup promises are claimed.

1. Start with an investigation question

Record the endpoint, SQL, slow parameters, expected row count and observed latency. In a multi-customer system, include both small and large customers. A tiny test account does not represent operational data.

Keep a compact evidence record: relevant schema and indexes, PostgreSQL version, measurement time and load conditions. Remove sensitive information before sharing SQL or plans outside the authorized team.

2. EXPLAIN is different from EXPLAIN ANALYZE

The EXPLAIN reference distinguishes showing a plan from using ANALYZE to execute the statement and collect measurements. Do not add ANALYZE casually to unreviewed SQL.

EXPLAIN (FORMAT TEXT)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

This displays a plan without ordinary SELECT execution. Measure actual execution only under controlled conditions with representative data. ANALYZE consumes resources; writes or side-effecting functions can also change state. A transaction followed by rollback is not a guarantee against every external effect.

3. Read the units correctly

The plan-reading guide explains that planner cost is not milliseconds and estimated rows are node output, not necessarily rows scanned. With ANALYZE, actual rows and node times for repeated executions are per-loop averages: inspect loops too.

This is an investigation framework, not an index prescription. Do not blindly add every node's time: parent and child measurements overlap, and parallel plans need worker context.

4. A sequential scan is not automatically failure

Small tables or queries retrieving much of a table can make sequential scanning reasonable. An Index Scan label likewise does not prove optimal performance. Seek correct results at appropriate cost, not a particular plan label.

For the orders example, examine customer filtering and the created_at/id ordering before considering a composite index. Preserve real tenant and status conditions. Removing authorization filters to improve a benchmark changes the problem.

5. Change one thing and preserve comparability

1. Capture the baseline plan, parameters and expected result.
2. Form one hypothesis about the access pattern.
3. Test one change in a controlled environment.
4. Repeat the same case and additional representative parameters.
5. Verify the result set, order and row count.
6. Review write overhead, storage and other queries before rollout.

Repeat measurements and record cache and load conditions. A faster second run can reflect cached data, not the proposed improvement. Do not clear production caches simply to create a test condition.

6. A plan is not end-to-end application latency

If the database is fast while users wait, examine connection-pool waits, query count, transfer, serialization and rendering. Many small queries can make an endpoint slow even when individual plans appear reasonable.

Correlate database evidence with request logs or traces. Keep observations separate from hypotheses: a row-estimate mismatch is evidence; adding a particular index remains a proposal to test.

EXPLAIN works best within a controlled measurement process. Correct units, representative data and correctness checks turn tuning into an evidence-based task.