How to Read PostgreSQL EXPLAIN ANALYZE Output

When a PostgreSQL query runs slowly, its EXPLAIN ANALYZE output shows how PostgreSQL ran it and how many rows each step processed. A query plan is a tree of operations, such as scanning a table, joining rows, or sorting results. The plan shows where PostgreSQL did more or less work than the planner expected.

Unlike plain EXPLAIN, EXPLAIN ANALYZE runs the query and reports actual row counts and timing alongside estimates. Read the plan from its leaf nodes upward. Compare estimated and actual row counts, then use buffer and filter details to find expensive work. Because the command runs the query, take care with statements that change data.

Start with the plan tree

The top node represents PostgreSQL’s final result. The indented child nodes beneath it provide the input. Read from the leaves upward to see how PostgreSQL retrieves and combines data. A PostgreSQL plan is a tree. Some operations pass rows upward as they work; others, such as sorts, must read all their input before returning results.

A scan node reads table data, and a join node combines rows from two inputs. A sort or aggregate node changes rows before sending them to its parent. The top node is not always where the query does most of its work. An expensive operation may be several levels below it.

For example, a Nested Loop node has two child plans. PostgreSQL gets rows from the outer child, then looks for matching rows through the inner child. If the inner child runs many times, inspect that child’s actual rows and loops. The node’s name alone does not tell you whether the join is efficient.

Separate estimates from measured results

Each plan node reports planner estimates and, with ANALYZE, observed results. The estimate contains a startup cost, a total cost, an estimated row count, and an estimated average row width.

Costs use planner units, not milliseconds. Startup cost estimates the work before a node returns its first row. Total cost estimates the work to finish the operation. A parent node’s cost includes its children’s costs, so do not add the costs from every plan line. Costs help explain the planner’s choices within one plan. They are less useful for comparing different queries or servers. The PostgreSQL documentation also notes that planner costs exclude the time to send results to the client and convert them to text.

The estimated row count is how many rows a node expects to return, not how many it scans. A filter can make the scanned and returned row counts very different. Estimated width is the average size of returned rows in bytes. Width estimates are less reliable for variable-length values.

With ANALYZE, each node also reports actual time, actual rows, and loops. Actual time is in milliseconds per loop. Multiply actual rows by loops to estimate the total rows the node emitted. This also shows how much work repeated execution caused. Do not add node times to calculate total query time: parent nodes include their children’s work, and operations can overlap as rows move through the plan.

Check closely when estimated and actual row counts differ substantially. A mismatch of about ten times or more points to a possible planning problem. PostgreSQL uses table statistics to estimate row counts. If the statistics are stale, running ANALYZE refreshes them. Then compare the plan again. If the mismatch remains, inspect the relevant filter or join.

Check scans, filters, and joins

A Seq Scan reads a table sequentially. That is a reasonable choice when PostgreSQL expects to return a large share of the table, so the node’s name alone does not make it a problem. Check how many rows the scan emits and whether the plan reports many rows removed by a filter. If PostgreSQL reads many rows and discards most of them, inspect whether the filter and available indexes match the query.

An Index Scan uses an index to find table rows. A Bitmap Heap Scan first builds a bitmap of matching locations and then reads the corresponding table pages. For an Index Only Scan, PostgreSQL can return values from the index without reading the table when it can confirm row visibility from its visibility map. If the plan reports heap fetches, PostgreSQL had to visit the table for some rows, reducing the benefit of the index-only scan. The execution plan node reference describes these scan types and their behavior.

For joins, look at how many times each child runs and how many rows it returns. A nested loop becomes a likely source of excess work when its inner child runs repeatedly and scans many rows each time. An index on the inner side’s join column can help PostgreSQL find matching rows without repeatedly scanning a large table. Hash joins build a lookup structure from one input and probe it with the other; the number of batches and buffer details help reveal whether that work spilled to disk.

Use buffers to find reads and spills

Run EXPLAIN (ANALYZE, BUFFERS) to see block activity alongside row counts and timing. Buffer counts show how PostgreSQL accessed data during the query. A shared hit means PostgreSQL found a block in its shared buffer cache. A shared read means PostgreSQL loaded the block into shared buffers. The count alone does not show whether the operating system also had the data cached.

Temporary block reads or writes indicate that PostgreSQL used temporary files, commonly because a sort or hash operation did not fit in memory. A hash join with multiple batches is another sign that PostgreSQL divided the hash work into chunks. A lossy bitmap scan stores matches at the page level rather than tracking every matching row, so PostgreSQL must recheck rows on those pages. These signs point to memory pressure, but they do not by themselves prove that a global memory setting should change. Test a local work_mem adjustment against the query and inspect whether the plan improves; raising it globally can increase memory use across concurrent operations.

The PostgreSQL tuning reference explains buffer details and common plan patterns. Use buffer counts to identify where PostgreSQL did work, and connect each count to the node that produced it instead of reading it in isolation.

Run the query safely and compare plans

Use EXPLAIN (ANALYZE, BUFFERS) to measure an actual execution. PostgreSQL also supports FORMAT JSON, which is useful when a plan viewer or other tool needs structured output. The Dalibo plan viewer can help display a plan as a tree.

ANALYZE executes the statement. For an INSERT, UPDATE, or DELETE, that means the statement changes data. Run it only where those changes are acceptable, or use a transaction you can roll back when that fits the statement and its effects.

When a query is slow, compare its plan with the reported row counts and buffers. Start with the slow or unexpectedly large subtree. Check whether estimates match actual results, then look for repeated scans or filtering. Check joins and temporary-file activity as well to find what accounts for the work. Change one likely cause at a time, then run the query and inspect the new plan. An index helps only when it reduces the work PostgreSQL does for that query.

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