PostgreSQL EXPLAIN ANALYZE: Reading Query Plans and Fixing Slow Queries

EXPLAIN shows the plan PostgreSQL expects to run. EXPLAIN ANALYZE runs the statement and adds measured rows, loops, and timing. The useful question is not whether a plan contains a sequential scan or nested loop; it is whether estimates, work performed, I/O, memory, and elapsed time fit the query's result and service objective.

Capture a plan safely

-- Estimate only; the query is not executed
EXPLAIN (VERBOSE, COSTS, SETTINGS)
SELECT o.id, o.total
FROM orders AS o
WHERE o.status = 'pending'
  AND o.created_at >= current_date - 7;

-- Executes the SELECT and adds runtime evidence
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT TEXT)
SELECT o.id, o.total
FROM orders AS o
WHERE o.status = 'pending'
  AND o.created_at >= current_date - 7;

EXPLAIN ANALYZE discards the query result instead of sending it to the client, but the work still happens. For INSERT, UPDATE, DELETE, MERGE, and data-definition statements, use a disposable copy or a carefully reviewed transaction that is rolled back. A rollback does not reverse external side effects from user code and does not make every operation risk-free. Locks, resource consumption, triggers, sequences, and invoked functions all need consideration.

Capture the bind values or a representative value class, server version, relevant settings, table sizes, and whether caches were warm. Parameter-sensitive queries can legitimately produce different plans for different inputs.

Read costs and actuals correctly

cost=startup..total is the planner's estimate in configurable cost units, not milliseconds. rows is estimated output per execution of the node. Under ANALYZE, actual time=start..end and actual rows are averages per loop when a node executes more than once. Consider loops before deciding how much total work a child performed.

Nested Loop  (cost=... rows=...) (actual time=... rows=... loops=1)
  -> Index Scan on accounts  (... actual rows=120 loops=1)
  -> Index Scan on orders    (... actual rows=3 loops=120)

The inner index scan ran once for each outer account. That can be excellent when each lookup is selective. It becomes a problem when the outer input is much larger than expected or each probe reads many pages. Parent time includes child work, so adding every node's elapsed time double-counts execution.

Start with cardinality estimates

A large estimate error can lead PostgreSQL to choose the wrong join order, join algorithm, scan, or memory strategy. Check whether autovacuum has analyzed the table, whether values are skewed, and whether predicates are correlated.

ANALYZE orders;

-- For a persistently skewed column, raise detail selectively
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- When columns are correlated, test extended statistics
CREATE STATISTICS orders_status_region_stats
  (dependencies, ndistinct)
  ON status, region
  FROM orders;
ANALYZE orders;

Do not raise statistics targets everywhere by reflex: collection and planning cost increase. Re-run the plan with the same representative parameters and retain the change only if estimates and workload behavior improve.

Scans are choices, not diagnoses

• Sequential scan: appropriate when much of a table is needed, the table is small, or random heap access would cost more. It is not automatic proof of a missing index.
• Index scan: useful for selective, ordered access, but many scattered heap fetches can be expensive.
• Index-only scan: can avoid heap fetches only where the visibility map and selected columns allow it.
• Bitmap index and heap scan: batches heap access and can be a good middle ground for a moderate result set.

Use predicates, Rows Removed by Filter, page counts, and the required ordering to design a candidate index. Avoid creating a partial index around a transient literal without confirming that the application's predicate implies the index predicate and that write overhead is acceptable.

Joins, sorts, and memory

A nested loop fits a small outer relation and a cheap inner lookup. A hash join can fit large equality joins, but Batches greater than one and temporary I/O can reveal that the hash exceeded available memory. Merge joins benefit when both sides are already ordered or can be sorted efficiently.

For sorts, inspect Sort Method, memory, and whether temporary files were used. work_mem applies to individual plan operations and can be consumed multiple times by one query and across concurrent queries. Test session-local changes; do not multiply a large value globally without a concurrency budget.

Interpret buffers and I/O

BUFFERS reports PostgreSQL buffer activity. Shared hits came from PostgreSQL shared buffers. Shared reads requested blocks from the storage layer, but those requests may be served by the operating-system cache. When track_io_timing is enabled, I/O timing adds evidence at some overhead. Compare plans from equivalent cache states, and focus on pages avoided as well as time.

A database-wide cache-hit percentage has no universal healthy target. A large analytical scan and a selective OLTP lookup have different expected patterns. Diagnose the query and the storage system together.

A disciplined tuning loop

1. Confirm the slow statement and representative parameter values with pg_stat_statements or application traces.
2. Capture a safe plan plus settings and table statistics.
3. Find the first major estimate error or excess work near the leaves.
4. Test one change: statistics, index, predicate, join reduction, partition pruning, or query rewrite.
5. Compare execution, buffers, WAL, planning time, write cost, and concurrency under the same conditions.
6. Remove diagnostic planner overrides such as enable_nestloop=off; they are experiments, not permanent fixes.

Use auto_explain with an overhead budget

auto_explain can capture plans for statements that are difficult to reproduce. Set a meaningful duration and consider sampling. log_analyze collects runtime statistics; when it is enabled, per-node timing can affect every executed statement even if the statement is not ultimately logged. PostgreSQL documents log_timing=off as a way to reduce that overhead when row counts are sufficient. Also control parameter logging to avoid exposing sensitive values.

Official primary sources

• PostgreSQL EXPLAIN command
• Using EXPLAIN
• Planner statistics
• PostgreSQL auto_explain
• PostgreSQL pg_stat_statements

Working with JusDB on PostgreSQL plans

JusDB helps teams connect plan evidence to query, index, statistics, and memory changes, then validate those changes under representative concurrency.

Explore JusDB PostgreSQL services →  |  Talk to a PostgreSQL engineer