PostgreSQL’s EXPLAIN and EXPLAIN ANALYZE commands turn a slow SQL statement into a plan you can inspect. This guide presents a repeatable workflow for finding expensive scans, inaccurate row estimates, inefficient joins, memory-heavy sorts, and avoidable application work—then testing improvements safely.
Overview
A PostgreSQL query plan is the optimizer’s proposed sequence of operations for producing a result. The plan is a tree: the top node returns the final result, while child nodes scan tables, apply filters, join rows, sort data, or aggregate values.
Use EXPLAIN when you want to inspect the optimizer’s prediction without running the query:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;
Use EXPLAIN ANALYZE when you need to compare that prediction with what happened during execution:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42;
EXPLAIN ANALYZE executes the statement. That distinction matters. It is generally safe for a read-only SELECT, but it can perform an INSERT, UPDATE, or DELETE unless the statement is placed inside a transaction that you roll back. Even then, triggers, external effects, locks, and resource usage deserve separate consideration.
Do not judge a plan by one line in isolation. Start with the slowest measured branch, confirm whether estimates are realistic, and compare the complete execution time before and after a change. For broader SQL patterns, the SQL joins, aggregations, and window functions cheatsheet is a useful companion.
How to estimate
Read a plan from the bottom up for execution flow, then from the top down for the result delivered to the client. A typical node contains values such as:
- Startup cost: the optimizer’s estimated work before the node can emit its first row.
- Total cost: the estimated work to produce all rows. These are planner cost units, not milliseconds.
- Plan rows: the estimated number of rows produced.
- Width: the estimated average row size.
- Actual time: measured startup and completion time when using
ANALYZE. - Actual rows and loops: the observed rows per execution and how often the node ran.
A useful first comparison is estimated rows versus actual rows multiplied by loops. A large difference can lead the optimizer to select an unsuitable join order, join type, or scan method. It is a signal to investigate, not automatic proof that an index is missing.
Common scan nodes have different implications:
| Plan symptom | What it may mean | What to check |
|---|---|---|
| Sequential Scan | PostgreSQL reads the table in order. | Is the table small? Does the filter match a large share of rows? Would a selective index help? |
| Index Scan | The index identifies rows, followed by table lookups. | Is the predicate selective? Are many random heap fetches making the scan expensive? |
| Index Only Scan | The index can provide the required columns, subject to visibility checks. | Does the query select unnecessary columns? Is the table visibility information current? |
| Bitmap Heap Scan | PostgreSQL gathers matching locations before fetching table pages. | Are many rows matched, or is the filter combining useful index conditions? |
| Sort with external or disk-related behavior | The operation may exceed available working memory. | Check row counts, selected columns, sort requirements, and appropriate session-level memory settings. |
Join nodes deserve the same treatment. A nested loop can be efficient when the outer side is small and the inner side has a fast lookup, but costly when repeated loops scan many rows. A hash join may suit larger equality joins, while a merge join can benefit from ordered inputs. Do not force a join type as a first response; verify the inputs, predicates, estimates, and indexes that led to the choice.
With BUFFERS, inspect shared hits, reads, dirtied pages, and written pages. High reads can indicate that the working data was not already in memory, an expensive access pattern, or simply a large amount of data that must be processed. Compare plans under representative data and cache conditions rather than drawing conclusions from one run.
Inputs and assumptions
A reliable PostgreSQL slow query investigation starts with controlled inputs. Capture the exact SQL, bind values or representative parameters, database version, relevant indexes, table sizes, and whether the test runs against production-like data. The same query text can produce different plans for different parameter values.
- Run
EXPLAINfirst to inspect the proposed plan without executing the statement. - Run
EXPLAIN (ANALYZE, BUFFERS)for a read query when execution is safe. - Repeat the test enough to distinguish a consistent issue from cache or system noise.
- Check statistics freshness with the database maintenance process and consider
ANALYZEafter substantial data distribution changes. - Test one meaningful change at a time: an index, a query rewrite, a narrower projection, or a corrected predicate.
- Compare total execution time, row counts, buffers, and plan stability—not only whether an index appears.
Indexes have costs: storage, write work, maintenance, and possible overlap with existing indexes. Before creating one, check whether the query filters or joins on a selective column, whether column order matches the access pattern, and whether a partial or covering design is appropriate. Validate the benefit against representative queries instead of relying on a planner hint or a forced scan.
For write statements, use a non-production environment when possible. If you must inspect a write plan, make the transaction boundary explicit and account for triggers, foreign keys, locks, and side effects. A rollback is not a universal undo mechanism for actions outside the database.
Worked examples
Example 1: a filter that returns too many rows
Suppose a report filters events by account_id and a time range. The plan shows an estimated 20 rows but actual output of 200,000 rows. An index on account_id alone may not solve the problem if the account contains much of the table. Compare a composite index that reflects the filter, inspect the selected columns, and verify whether the report genuinely needs all matching rows. If the result set is large, pagination or pre-aggregation may matter more than changing the scan type.
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_type, occurred_at
FROM events
WHERE account_id = 42
AND occurred_at >= TIMESTAMP '2026-01-01'
AND occurred_at < TIMESTAMP '2026-02-01';
After testing an index or statistics change, compare estimated and actual rows at the filter node. A closer estimate can improve later join decisions even if the filter itself was not the largest time consumer.
Example 2: repeated inner work in a join
Imagine a plan with a nested loop whose inner node runs thousands of times. If each loop performs a sequential scan, the combined work can dominate the query. Check whether the join predicate has a usable index, whether the outer input is unexpectedly large, and whether stale statistics caused the planner to underestimate it. Rewrite only after confirming the data shape; a query that looks awkward may already be efficient when the outer side is small.
A practical before-and-after record is:
-- Before: record plan, execution time, rows, and buffers
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;
-- Change one variable, then repeat
CREATE INDEX CONCURRENTLY ...;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;
Use CREATE INDEX CONCURRENTLY only when its operational trade-offs fit your environment, and follow your team’s migration process. Keep the original plan so that a later regression can be traced.
When to recalculate
Revisit a PostgreSQL query plan whenever response time changes, table cardinality grows, data distribution shifts, a schema or index changes, a PostgreSQL version changes, or a report begins timing out. Recalculate after bulk loads and substantial deletes because estimates and access patterns may no longer represent the earlier test.
Use this action-oriented checklist:
- Capture the slow query and representative parameters.
- Run
EXPLAIN, then safeEXPLAIN ANALYZEwith buffers. - Find the node with the largest measured contribution and inspect loops.
- Compare estimated rows with actual rows at each major filter and join.
- Check statistics, predicates, indexes, selected columns, sorts, and memory behavior.
- Test one change in a representative environment and preserve both plans.
- Recheck normal, selective, and unusually broad parameter values.
Query plans are operational evidence, not a scorecard. The best tuning decision is the one that reduces measured work for the real workload while keeping writes, maintenance, correctness, and future data growth in view.