How to Read a Postgres EXPLAIN ANALYZE Query Plan

EXPLAIN ANALYZE is the PostgreSQL command that shows exactly how the database plans to execute a query and, critically, how long each step actually took when it ran. Where EXPLAIN alone shows the planned execution — the optimizer’s estimate — EXPLAIN ANALYZE actually runs the query and reports real timing and row counts alongside the plan, which is what makes it useful for finding out why a specific query is slow.

This walks through running it and reading the output, node by node.

Running it

Prefix any query with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

One caveat worth internalizing before you run this against production: EXPLAIN ANALYZE actually executes the query, including any writes if it’s an INSERT, UPDATE, or DELETE. For a read-only SELECT that’s harmless beyond the load of running it once; for a write, wrap it in a transaction you roll back if you just want to see the plan:

BEGIN;
EXPLAIN ANALYZE
UPDATE orders SET status = 'shipped' WHERE customer_id = 4821;
ROLLBACK;

Reading the output, top to bottom

A plan is a tree of nodes, each representing one operation — a scan, a join, a sort, an aggregate. Postgres prints it indented, with the outermost (final) operation at the top and the operations that feed into it nested below. A representative node looks like this:

Index Scan using orders_customer_id_idx on orders
  (cost=0.42..8.44 rows=20 width=96)
  (actual time=0.031..0.089 rows=20 loops=1)
  Index Cond: (customer_id = 4821)

Breaking down each part:

• Node type (Index Scan) — the operation. Common ones include Seq Scan (reads the whole table), Index Scan and Index Only Scan (uses an index), Nested Loop, Hash Join, and Merge Join (the three join strategies), and Sort or Aggregate for those respective operations.
• cost=0.42..8.44 — the planner’s estimated cost, in arbitrary units, not milliseconds. The first number is the estimated cost to return the first row; the second is the estimated cost to return all rows. These are the optimizer’s predictions, made before the query ever runs.
• rows=20 — the planner’s estimated row count for this node.
• actual time=0.031..0.089 — the real measured time in milliseconds, same first/last-row structure as cost. This is only present with ANALYZE, not plain EXPLAIN, because it requires actually running the query.
• rows=20 (in the actual-time line) — the real number of rows this node produced, as opposed to the planner’s guess.
• loops=1 — how many times this node executed. For a node nested inside a loop-based join, this can be greater than one, and the actual-time values shown are the average per loop — multiply by loops to get the total time that node spent.

The single most useful thing to check: estimate vs. reality

The fastest way to find the root cause of a slow query is comparing the planner’s estimated rows against the actual rows at each node. When they’re close, the optimizer had good information and likely made a good decision. When they’re wildly different — the planner expected 20 rows and got 200,000 — every decision built on top of that estimate (which join strategy to use, which table to scan first) may be wrong, and this is very often the actual root cause of a bad plan.

That mismatch is typically fixed by running ANALYZE tablename; to refresh the table’s statistics, or by checking whether autovacuum’s statistics collection is falling behind on a heavily-written table. It’s a database-maintenance problem well before it’s a query-rewriting problem.

Seq Scan vs Index Scan

Seeing Seq Scan on a large table is the most common thing people flag as “the problem,” but it isn’t automatically wrong — for a query that needs most of the table’s rows anyway, a full sequential scan can genuinely be faster than the overhead of an index lookup per row. The planner chooses between them based on its cost estimates, which is exactly why the estimate-vs-actual comparison above matters more than the node type alone.

That said, an unexpected Seq Scan on a large table for a highly selective filter (a query that should only match a handful of rows) is a real signal — it usually means there’s no index on the filtered column, or an existing index isn’t being used for a reason worth investigating (a type mismatch, a function wrapped around the column, or stale statistics). See Postgres index types for which index type actually fits the query pattern you’re optimizing for — a B-tree index doesn’t help a query that isn’t using it in a way the planner can exploit.

Join nodes

For queries joining multiple tables, the join strategy matters:

• Nested Loop — for each row from the outer input, scans the inner input looking for matches. Efficient when the outer side is small.
• Hash Join — builds an in-memory hash table from one side, then probes it with the other. Efficient for larger, unsorted inputs.
• Merge Join — requires both inputs sorted on the join key, then walks them in tandem. Efficient when the inputs are already sorted, e.g. by an index.

A N+1 query pattern shows up differently — not as a single expensive plan, but as many separate simple plans issued in a loop from application code. EXPLAIN ANALYZE on any one of those individual queries will look fine; the problem is visible in query logs or a tracing tool, not in a single plan.

A worked comparison

Signal	Likely meaning
Seq Scan on a small table	Usually fine — a full scan of a small table is cheap
Seq Scan on a large table with a selective filter	Missing or unused index
Large gap between estimated and actual rows	Stale table statistics — run ANALYZE
Sort node with high actual time	Consider an index that matches the ORDER BY, avoiding the sort entirely
High loops count with meaningful per-loop time	The inner side of a nested loop is expensive — check if it can use an index

Using EXPLAIN (ANALYZE, BUFFERS)

Adding BUFFERS to the option list shows how many disk-page reads each node caused, split into cache hits and actual reads:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4821;

This surfaces I/O-bound problems that raw timing alone can hide — a node might look fast in a warm cache during testing but hit disk under real production load with a cold cache. For teams working through this regularly, the official PostgreSQL documentation on EXPLAIN covers every output option in full.

The takeaway

EXPLAIN ANALYZE turns “this query is slow” into a specific, node-by-node account of where the time actually went. Read the plan from the innermost nodes outward, compare each node’s estimated row count against its actual row count first, and treat a big mismatch as a statistics problem before assuming it’s a missing index. Once the estimates and reality line up, the remaining node types — scans, joins, sorts — point directly at what to fix, whether that’s an index, a rewritten predicate, or a table that just needs a fresh ANALYZE.