Quick answer
PostgreSQL EXPLAIN shows the plan tree selected for a statement. Read it from the leaves toward the root while following data flow: each node receives rows from its children, performs work such as scanning, filtering, joining, sorting, or aggregating, and sends rows upward. Estimated startup cost, total cost, rows, and width describe what the planner expected. With ANALYZE, actual time, rows, and loops describe an executed observation. BUFFERS, WAL, SETTINGS, and structured output add context.
Never reduce a plan to “index good, sequential scan bad.” Compare estimated and actual cardinality, multiply per-loop observations when interpreting repeated work, check rows removed by filters, heap fetches, sort method and spill, buffer hit/read/write, WAL, and the query’s returned result. Then connect the plan to application pool wait, lock wait, concurrency, and the user SLI.
Plain EXPLAIN plans without executing the target statement. EXPLAIN ANALYZE executes it and adds instrumentation overhead. Use a disposable or representative staging database by default. For production, start with non-executing plans and existing workload telemetry. Only execute a known-safe read inside bounded time, resource, permission, and correctness controls. Do not assume wrapping a data-changing statement in BEGIN makes its locks or operational load harmless.
Shared order-history incident stage
In the shared order-history incident, the API filters by tenant, status, and recent creation time, sorts newest first, limits the page, and joins order items. Data skew changed after a flash sale and backfill. The planner’s cardinality estimate for the dominant tenant is too small, so the chosen query plan expects a cheap path but processes far more rows and repeated inner work than predicted.
The first useful plan is not an unbounded production execution. A non-executing EXPLAIN (COSTS, VERBOSE, FORMAT JSON) confirms the chosen tree and estimates for the exact statement shape. Existing pg_stat_statements, application duration, wait events, and safe replica evidence establish whether this query family actually owns the incident. Only then does a controlled execution in a representative environment compare actual rows, loops, buffers, temporary I/O, and WAL.
The plan reveals consequences rather than the entire cause. A nested loop may repeat an inner node because the outer estimate was wrong. A bitmap heap scan may visit many heap pages because visibility or selectivity differs from assumptions. A sort may spill because the input is larger than estimated. Partition scans may remain because the predicate did not prune. Those observations lead into Query Planner Statistics and Cardinality Estimation, advanced indexing, MVCC, and partition analysis.
The same query also waits for a connection and sometimes a lock. EXPLAIN does not include time spent waiting in the application pool before the statement starts. A fast controlled execution therefore cannot disprove production saturation. The incident runbook preserves the full clock chain.
Core mechanism and evidence boundary
A plan is a tree, not an ordered list of independent lines. Indentation represents parent-child relationships. A parent cannot finish until it receives the rows required from its child. Some nodes stream rows, while blocking nodes such as many sorts or aggregates need substantial input before producing output. Startup cost estimates work before the first row; total cost estimates work to completion under planner assumptions. Both use PostgreSQL-specific relative cost units, not elapsed time.
rows is the planner’s estimated rows emitted by a node per execution, after that node’s filters. With ANALYZE, actual rows and actual time are displayed per loop when a node runs repeatedly; use loops to understand total repeated work. A large estimate error near a join can multiply downstream work. Zero actual loops can mean a branch was never executed, not that its cost was free in every workload.
Scan nodes answer how tuples are found. A sequential scan can be rational when a table is small or a large fraction is needed. An index scan can support ordering and selective access but may cause random heap visits. A bitmap index and heap scan can batch heap access. An index-only scan still relies on visibility information and can report heap fetches. Join nodes express strategies under estimated sizes: nested loop, hash join, or merge join. Their suitability depends on inputs, memory, order, indexes, and repeated execution.
A nested loop executes its inner path for qualifying outer rows, which makes loops and parameterized index access central evidence. A hash join builds a hash table from one input and probes it with the other; batches or temporary I/O can reveal that the build exceeded its working memory. A merge join consumes suitably ordered inputs and advances through matching keys, sometimes benefiting from existing index order and sometimes paying for sorts first. None of these names is a verdict. Compare actual input sizes, repetitions, memory behavior, and output cardinality.
Filters belong to specific nodes. Index Cond describes predicates used to navigate an index, while Filter can remove rows only after they reach that node. Rows Removed by Filter exposes discarded work, but it also follows loop semantics. A predicate shown at a later node may explain why an apparently selective query still fetches many tuples. Check predicate placement before designing another index.
BUFFERS distinguishes shared/local/temp hits, reads, dirties, and writes. A hit means the page was found in PostgreSQL-managed cache; it still costs CPU and memory access. A read indicates a request to the storage layer, but plan output alone does not prove physical media latency because operating-system caching exists. Temporary blocks and sort details reveal spill. WAL reports records, full-page images, and bytes generated by executed work where applicable.
This article uses PostgreSQL 18 behavior. JSON keys, node types, settings, and measurement details are PostgreSQL-specific, not a SQL standard or universal database interface. Teaching fields such as plan_rows, actual_rows, plan_loops, shared_blks_read, and temp_blks_written are not a stable OpenTelemetry semantic convention. Keep trace IDs and query fingerprints out of metric labels; use them only in restricted correlation data.
Minimal reproducible PostgreSQL 18 example
This focused plan-reading example reuses the synthetic orders and order_items fixture defined in Database Query Performance for Backend Systems. Create skewed synthetic orders in a disposable database, run ANALYZE, and inspect a read query first without execution:
EXPLAIN (COSTS, VERBOSE, SETTINGS, FORMAT JSON)
SELECT o.id, o.created_at, o.total_cents
FROM orders AS o
WHERE o.tenant_bucket = 7
AND o.status = 'PAID'
AND o.created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;Save the JSON plan with the PostgreSQL version, schema revision, statistics timestamp, fixture description, and relevant settings. Do not store real parameter values. On a disposable database, execute with local safety bounds:
BEGIN READ ONLY;
SET LOCAL statement_timeout = '3s';
SET LOCAL lock_timeout = '250ms';
EXPLAIN (
ANALYZE,
BUFFERS,
WAL,
SETTINGS,
TIMING,
SUMMARY,
FORMAT JSON
)
SELECT o.id, o.created_at, o.total_cents
FROM orders AS o
WHERE o.tenant_bucket = 7
AND o.status = 'PAID'
AND o.created_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;
ROLLBACK;The transaction is read-only, but the query can still consume resources. Repeat for literal classes chosen from sanitized workload analysis: dominant, typical, and rare tenants; common and rare statuses; narrow and wider time windows. Randomize trial order and distinguish warm from colder cache conditions.
For a repeated inner node, calculate a diagnostic total from reported averages:
approximate emitted rows = actual rows per loop × loops
estimate ratio = actual rows / max(estimated rows, 1)Retain both original values and loops. The ratio is a pointer to investigate, not a universal alert threshold. A node can estimate well and still be expensive because it legitimately processes many rows.
Failure modes and dangerous misconceptions
Reading only the most indented or most expensive-looking line. Cost is cumulative according to plan structure, so adding every node’s total cost double-counts child work. Follow data flow and identify where row counts, loops, and buffers expand.
Comparing cost to milliseconds. Planner cost ranks alternatives under configured assumptions. Actual time is measured execution evidence with instrumentation overhead. Their numeric similarity carries no unit conversion.
Ignoring loops. A tiny inner-node time repeated hundreds of thousands of times can dominate execution. Conversely, an expensive branch with zero loops did not run in that observation.
Assuming cache hits are free. Millions of shared-buffer hits can consume CPU and memory bandwidth. Measure resource and concurrency impact.
Running EXPLAIN ANALYZE on a write because rollback is available. The statement still executes, locks rows, fires applicable triggers, generates work and possibly WAL, and can block other sessions before rollback. Use a disposable copy or a deliberately designed safety procedure.
Forcing a node type. Disabling sequential scans or joins is useful for controlled comparison, not a permanent cure. It can hide bad statistics, skew, or a missing access path and harm other query classes.
Publishing raw plans. Literals, schema names, expressions, and topology may expose sensitive data. Sanitize and authorize sharing.
Security, privacy, capacity, and cost implications
Plans and activity views can expose PII or personal data embedded in SQL literals, tenant predicates, comments, function arguments, and object names. Use parameterized application SQL, restrict monitoring roles, redact before export, and set a retention policy. pg_stat_statements limits visibility of other users’ SQL and query identifiers to privileged roles; preserve that boundary rather than granting broad access for convenience.
Raw SQL, queryid, PID, tenant, user, order, request, trace, and span identifiers are unsuitable metric labels. Their cardinality and sensitivity can damage both the monitoring system and privacy posture. Aggregate by bounded route, operation, environment, and database role. Store high-cardinality artifacts in a protected diagnostic system with audit trails.
Execution analysis adds overhead. TIMING can increase per-node measurement cost on some platforms. A heavy test can evict useful cache pages, consume I/O and temporary space, generate WAL, or compete with autovacuum and replicas. Bound concurrency, timeout, result retention, and data volume. Prefer an isolated restored dataset when the query’s safety or cost is uncertain.
Plan artifacts also create operational cost: version-aware parsers, storage, sanitization, comparison, and expiration. Store only what supports a defined investigation. A screenshot without query shape, version, settings, and data conditions is cheap to collect but expensive to misinterpret.
Testing and production validation
Validate the query’s result set and order before comparing speed. Pagination queries need stable tie-breaking, authorization filters, and no skipped or duplicated rows across pages. Joins must preserve intended cardinality. A faster plan that drops a predicate or changes semantics is a correctness failure.
Create literal test cases by workload class. For each, record estimated and actual rows, loops, execution time, buffers, temporary blocks, WAL, returned rows, and relevant settings. Repeat trials to see variance. Test concurrent reads and writes, not only a single session. Observe application pool wait and database wait events during load.
When a proposed index, statistics object, query rewrite, or partition predicate changes the plan, compare resource tradeoffs as well as latency. Check write throughput, WAL, storage, vacuum behavior, planning time, and other statements. Re-run after adding representative data and after ANALYZE; do not require a frozen plan when a different plan is appropriate for a different distribution.
In production, first confirm the deployed query shape and affected normalized family. Compare a bounded before/after window at similar demand. Verify p50/p95/p99 user latency, timeouts, errors, retries, pool wait, lock wait, database duration, and resource saturation. The plan explains database work; the user SLI verifies recovery.
Automate semantic comparisons instead of diffing volatile plan text. Preserve node type, relation, join relationship, estimates, actuals, loops, buffers, spill, and settings as structured fields, but allow harmless formatting changes. Alerting on every plan change creates noise; alert on sustained user impact or bounded resource signals, then use the plan artifact to explain the change.
Operations checklist
- Capture route, operation, deployment, database role, time window, user impact, and normalized query family.
- Separate application queue, pool acquisition, database execution, lock wait, and response time.
- Inspect active waits and cumulative statement data before running new diagnostic work.
- Use plain
EXPLAINfirst and record PostgreSQL version, schema, statistics age, settings, and data class. - Execute
ANALYZEmode only for a known-safe statement in a controlled resource envelope. - Read the tree from children to parents and follow emitted rows upward.
- Compare estimated rows, actual rows, and loops at cardinality-changing nodes.
- Review scan selectivity, rows removed, heap fetches, join repetitions, sort method, spill, buffers, WAL, and output rows.
- Sanitize SQL and plans; keep PII and identifiers out of metric labels.
- Test representative skew, cache conditions, concurrency, data growth, and correctness.
- Evaluate the effect on other reads, writes, maintenance, replicas, storage, and planning time.
- Close only after user SLI and resource evidence recover in production.
Official sources
- PostgreSQL 18: EXPLAIN — options, execution behavior, and output; accessed 2026-08-04.
- PostgreSQL 18: Using EXPLAIN — plan nodes, estimates, joins, sorting, and examples; accessed 2026-08-04.
- PostgreSQL 18: pg_stat_statements — normalized statement statistics and access boundaries; accessed 2026-08-04.
- PostgreSQL 18: Monitoring Statistics — collection and freshness boundaries; accessed 2026-08-04.
Continue the learning path
Place plan evidence inside Database Query Performance for Backend Systems, then explain row-estimate errors with Query Planner Statistics and Cardinality Estimation and access paths with Composite, Covering, and Partial Indexes. N+1 Query Problem shows how application query shape can multiply otherwise reasonable plans. Continue through System Design or browse Topics.