PostgreSQL EXPLAIN: A Slow-Query Checklist Before Adding Indexes

A slow PostgreSQL query doesn't automatically need another index. It might read too many rows, multiply work through a join, wait on locks, sort a large result, or return more data than the application needs. An index can help the right problem and add write overhead when it doesn't.

PostgreSQL EXPLAIN makes the planner's chosen approach visible. Used carefully, it turns a vague complaint into questions you can test. This guide focuses on interpreting evidence and choosing a next experiment, not on declaring one plan node universally good or bad.

Capture the actual query and its context

Find the query that is slow in the real request path, including its parameters. A query with a highly selective customer identifier may behave differently from the same query for a customer with millions of records. Sanitise sensitive values while preserving the distribution needed for analysis.

Record server version, relevant schema, existing indexes, data volume, and the symptom. Is the delay consistent, only under load, or limited to a few parameter values? Also distinguish database execution time from network transfer and application processing.

Use SQL Formatter to make a sanitised query readable. It won't identify a slow plan or validate semantics. Check that the formatted text still represents the query your application sends, rather than an oversimplified version that removes the expensive condition.

Start with EXPLAIN before executing diagnostics

Plain EXPLAIN shows a planned execution approach without running the query. EXPLAIN ANALYZE executes it and reports observed behaviour. That distinction is essential for statements that modify data or call functions with side effects.

The PostgreSQL EXPLAIN documentation explains estimates, actual rows, and plan nodes. Use a controlled environment and an appropriate timeout before collecting execution evidence. Never assume that adding EXPLAIN makes an arbitrary production statement harmless.

For an authorised read query in staging, an illustrative diagnostic is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

The tenant value is fictional. Prefer representative staging data, and account for the diagnostic overhead when comparing measurements. Don't run repeated expensive analyses against a busy service just because the command returns useful-looking numbers.

Compare estimated and actual rows

Large differences between estimated and actual rows can explain why the planner chose an awkward strategy. Look at where the difference begins, not only at the final total. An early underestimate can lead to repeated downstream work.

Check whether statistics are current and whether the data is unusually skewed or correlated. A condition that is selective for most customers may not be selective for one very large customer. The planner's assumptions deserve investigation before you force a particular plan.

When a node executes many times, consider its loops alongside row counts and timings. A small inner operation repeated thousands of times can dominate the request. Don't compare isolated node numbers without understanding how the plan composes them.

Inspect filters, sorting, and returned data

Ask how many rows are examined and then discarded. A query returning 50 rows might still scan a large portion of a table. Review whether the filter can be supported by an appropriate index and whether its expression matches the index design.

Check sorting work and whether the query retrieves unnecessary columns. Large payloads can cost time even after the database finds the right rows. Pagination design matters too: repeatedly skipping a large offset can become expensive as the result set grows.

A sequential scan isn't automatically a mistake. Reading much of a small table can be efficient. An index scan isn't automatically efficient either if it causes extensive random access or returns a large share of the table. Judge the whole workload and observed performance.

Add one hypothesis-driven change

State the hypothesis before editing: for example, “a composite index matching tenant filtering and time ordering may reduce the work needed for the latest 50 events.” Test it with representative data and query variants.

Include write cost and storage in the result. An index affects inserts, updates, maintenance, and disk usage, not just one SELECT. Review whether an existing index already covers part of the requirement and whether the proposed addition is redundant.

For production changes, use the separate safe database migration plan. Choosing an index and deploying it safely are different tasks. A correct index idea can still be rolled out in a disruptive way.

Compare results without fooling yourself

Run before-and-after measurements under comparable conditions. Warm caches, concurrent traffic, and changed data can distort a simple stopwatch comparison. Preserve the query, parameters, schema version, and environment alongside the result.

Text Diff can highlight changes between sanitised plan text. It doesn't know which plan is better. Use the diff to locate changed estimates or operations, then interpret them with measured latency and resource use.

Also test the application-level result. A faster query returning different rows isn't a successful optimisation. Include correctness checks and verify that permissions and ordering remain intact. For full-text workloads, see PostgreSQL search implementation.

Conclusion

Read plans as evidence, not as a collection of good and bad node names. Capture the real workload, compare estimates with observations, and test one explanation at a time. Add an index when the measured problem supports it, and include correctness and operational cost in the decision.