How to Read PostgreSQL EXPLAIN ANALYZE Output

When a PostgreSQL query runs slowly, EXPLAIN ANALYZE shows how PostgreSQL planned and executed it. The output is a tree of operations, such as table scans and joins, with estimates beside measured row counts and execution times. Comparing those estimates with the actual results helps you find where the database did more work than expected.

Unlike EXPLAIN, which reports the planner’s estimates without running the query, EXPLAIN ANALYZE executes the query and records what happened. That makes it useful for diagnosing performance, but it also means a statement that changes data, such as UPDATE or DELETE, will make those changes when analyzed. To measure such a statement without keeping its changes, run it inside a transaction and roll the transaction back: BEGIN;, then EXPLAIN ANALYZE ...;, then ROLLBACK;. PostgreSQL still changes the rows inside the transaction and keeps their locks until the rollback, so other sessions that try to change the same rows wait. PostgreSQL documents this approach and the available output options in its EXPLAIN reference.

Read the plan from the bottom up

PostgreSQL displays a query plan as an indented tree. Each line names a plan node, an operation PostgreSQL performs. Nodes lower in the tree produce rows for the nodes above them. Read from the deepest nodes upward to see how PostgreSQL builds the final result.

Scan nodes read rows from a table or index: Seq Scan reads a table, while Index Scan uses an index to find rows. Join nodes combine rows from two inputs. Sort nodes order rows; aggregate nodes calculate values such as counts or sums. The top node represents the final result returned by the plan.

Indentation shows which nodes feed into other nodes. A parent’s cost and time include work done by its children, so adding the values from every line will double-count work. Instead, look for the lower-level node where the unexpected work begins. PostgreSQL’s plan examples show how these nodes fit together.

Compare estimates with actual results

A typical plan line contains estimated cost and row count, followed by actual time and row count when you use ANALYZE. The width field estimates the average size, in bytes, of rows emitted by a node.

The estimated cost has two parts: startup cost, which covers work before the node can return its first row, and total cost, which estimates the work to return all its rows. These are planner units, not milliseconds. PostgreSQL uses them to compare plans based on its cost settings, so use them to understand why the planner chose a path rather than to predict elapsed time.

The estimated rows value is the number of rows the node expects to emit. It does not mean that many rows were scanned. A filter can make a scan read many rows and pass only a few to the next node. With ANALYZE, the plan reports actual rows as well. A large gap between estimated and actual rows usually means the planner chose its plan using a poor estimate. The gap is expected when a node stops early, for example because a LIMIT above it needs only a few rows. PostgreSQL estimates rows as if each node runs to completion, but the node returns only the rows its parent asks for. Otherwise, check whether table statistics are current; running the separate ANALYZE command on the table refreshes them. If the estimates remain inaccurate, investigate the query’s data distribution and filters before deciding that an index is missing.

Actual time is shown as a range: time to produce the first row and time to produce all rows for that node. When a node runs more than once, PostgreSQL reports average time and rows per loop. Read loops alongside those values: a node that returns few rows per loop can still do substantial work when it runs many times.

For example, a nested-loop join runs its inner node once for each row from its outer input. This works well for a small input, but thousands of loops deserve investigation. Compare the outer row count and inner-node work before deciding that another join type would be faster.

Find wasted work and I/O

Rows Removed by Filter reports rows that a node discarded after checking its filter condition. A sequential scan that removes many rows but returns only a few does a lot of scanning. If the filter is selective and the table is large, check whether an index that matches the filter would help. PostgreSQL can still choose a sequential scan when it estimates that reading the table costs less than using an index, so a sequential scan alone does not prove a problem.

To inspect buffer activity, run the query with EXPLAIN (ANALYZE, BUFFERS). A shared buffer hit means PostgreSQL found a requested page in its shared memory cache; a shared buffer read means it had to bring that page into the cache. These counts help show where the plan accessed data, but a buffer read does not by itself prove that the storage device performed a physical read, since the operating system can cache data too. Buffer counts on parent nodes include work from their children, so inspect the plan structure rather than summing counts across lines.

A top-N heapsort supports queries that sort results and return only a limited number of rows. It keeps only the best candidates in memory, but it still has to read all rows from its input to know which rows belong at the top. If the input scan returns many rows, the sort’s small memory use does not mean the query avoided scanning them.

Use the plan to choose the next change

Start with a node that takes substantial time and has a surprising row count or many repeated loops. Then check the estimates that led PostgreSQL to that plan. If estimated and actual rows disagree substantially, refresh statistics before changing indexes or forcing a different plan. If estimates are close, focus on the amount of data the query reads, the rows its filters discard, and repeated work in joins.

The plan’s cost is not elapsed time, and PostgreSQL does not include converting output values to text or sending them to the client in that cost. A query can therefore have a reasonable-looking plan and still feel slow when it returns a large result over a network. Compare the plan with the time your client observes, especially when PostgreSQL reports that execution finished quickly.

After changing a query, an index, or statistics, run EXPLAIN ANALYZE again and compare the actual rows, loops, buffer activity, and execution time. Keep the parameter values and other conditions the same so the comparison shows what the change accomplished.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com