How to read a PostgreSQL EXPLAIN plan

Scope and versions

This guide covers PostgreSQL 16, 17, and 18. The plan shape and estimates below are illustrative; real output depends on schema, statistics, settings, PostgreSQL version, and data distribution. Check the documentation for the server release you run.

Get a plan without executing the SELECT

Start with plain EXPLAIN to ask the planner how it expects to execute the query. For a SELECT, this plans the query without running it:

EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Try EXPLAIN (VERBOSE, COSTS, SETTINGS) when you need more detail. Use FORMAT JSON if a tool needs structured output. Plan costs are estimates used to compare alternatives; they are not wall-clock durations.

Read the plan tree

Suppose the table has an index on (customer_id, created_at DESC). This is a simplified illustrative text plan for the query above:

Limit  (cost=0.42..18.90 rows=20 width=24)
  ->  Index Scan using orders_customer_created_idx on orders
        (cost=0.42..92.80 rows=100 width=24)
        Index Cond: (customer_id = 42)

• Limit: The top node returns at most 20 rows. It can stop its child once it has enough rows.
• Index Scan: PostgreSQL uses the named index to find matching entries and fetch table rows. The child node feeds rows upward to Limit.
• Index Cond: The equality test is applied as part of the index lookup.
• cost=startup..total: Estimated planner costs in arbitrary units. The top node’s total can be lower than the scan node’s total because the limit is expected to stop the scan early.
• rows and width: Estimated output rows and average row width, not actual measurements.

Node estimates and the selected path in this example are illustrative, not a promise about what PostgreSQL will choose. A sequential scan can be the right choice when a table is small or a query needs much of its data. See why PostgreSQL may not use an index and how to investigate row estimate errors.

What to inspect first

• Scan nodes: Look for Seq Scan, Index Scan, Index Only Scan, or Bitmap Heap Scan. Judge each in relation to how many rows the query needs.
• Join nodes: Nested Loop, Hash Join, and Merge Join have different costs and suit different input sizes and orderings. A node name alone does not identify a bad plan.
• Estimated rows: Compare estimates to actual rows when you can safely gather an analyzed plan. Large differences can affect join order and scan choices.
• Filters: A Filter is applied after rows are found; Rows Removed by Filter in an analyzed plan can show how much candidate work was discarded.
• Sort and buffers: A Sort node is not automatically expensive. With BUFFERS, shared buffer hits and reads help show data access; buffer counts are not elapsed time.

An Index Only Scan can still visit the heap when visibility information is not available in the visibility map. Read why an index-only scan can fetch table rows before assuming that its name means zero heap access.

Verify what the plan predicts

1. Record the exact SQL, parameter values, PostgreSQL version, table definitions, indexes, and representative table sizes.
2. Compare plain EXPLAIN for common and selective parameter values. A plan can differ with data distribution.
3. When actual row counts and timing are needed, use EXPLAIN (ANALYZE, BUFFERS) only when executing the statement is acceptable. It runs the query and its timings include instrumentation overhead.
4. Compare actual rows at each node, remembering that node row and timing values are generally reported per loop. Validate any proposed change with result correctness, representative latency measurements, and workload impact.

For the safety details, read how to use PostgreSQL EXPLAIN ANALYZE safely. The official manual also describes how to use EXPLAIN and its PostgreSQL 16 behavior.

Primary sources

• PostgreSQL 18: Using EXPLAIN
• PostgreSQL 17: Using EXPLAIN
• PostgreSQL 16: Using EXPLAIN
• PostgreSQL 18: EXPLAIN command reference

About TuningLens

TuningLens editorial team · Published 29 September 2026.

TuningLens analyzes evidence you submit; it does not connect to or run queries on your database. Recommendations require review and testing in your environment.