How to read EXPLAIN ANALYZE, step by step

Running it without breaking anything

EXPLAIN shows the plan the database intends to use, with its estimates. EXPLAIN ANALYZE runs the query and adds what actually happened: rows produced, time taken, loops executed. The second is the one worth reading, and it comes with one warning.

To measure a change safely, run it inside a transaction and roll it back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

The ways to ask for a measured plan differ per database:

• PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) SELECT .... BUFFERS adds how many pages came from memory and how many from disk. Add FORMAT JSON if a tool will read it.
• MySQL 8.0.18 and later: EXPLAIN ANALYZE SELECT ..., which prints a tree.
• SQL Server: turn on Include Actual Execution Plan (Ctrl+M) in Management Studio, or run SET STATISTICS XML ON before the query.

Measure with realistic data. A plan taken on a development database with a thousand rows says little about production with fifty million: the planner makes different choices at different sizes.

Reading the tree: inside out

This is a PostgreSQL plan for a query that finds one customer's orders by email address:

 Sort  (cost=61234.10..61246.60 rows=5000 width=24) (actual time=412.308..412.314 rows=48200 loops=1)
   Sort Key: o.created_at DESC
   Sort Method: external merge  Disk: 10240kB
   ->  Nested Loop  (cost=0.00..61101.00 rows=5000 width=24) (actual time=0.041..398.120 rows=48200 loops=1)
         ->  Seq Scan on customers c  (cost=0.00..2041.00 rows=500 width=8) (actual time=0.020..38.400 rows=1 loops=1)
               Filter: (lower((email)::text) = 'ana@example.com'::text)
               Rows Removed by Filter: 99999
         ->  Seq Scan on orders o  (cost=0.00..118.00 rows=10 width=24) (actual time=0.012..352.900 rows=48200 loops=1)
               Filter: ((customer_id = c.id) AND (status = 'open'::text))
               Rows Removed by Filter: 1951800
 Planning Time: 0.412 ms
 Execution Time: 414.020 ms

Each line with an arrow is a step, called a node. Indentation shows which steps feed which: a node's inputs are the nodes indented beneath it. Rows flow upwards, from the innermost scans that read tables, through joins and sorts, to the top node that returns the result. So read it from the deepest line upwards. Here:

1. Scan customers, keeping rows whose lower-cased email matches.
2. For each customer found, scan orders for that customer's open orders (the nested loop).
3. Sort the result by date.

The indented lines without an arrow — Filter, Sort Method, Rows Removed by Filter — are details of the node above them.

The two sets of numbers on each line

Every node carries an estimate and, with ANALYZE, a measurement.

The trap is the word averaged. A node inside a nested loop may show rows=3 loops=10000: it returned 30,000 rows in total, not three. Always multiply by loops before comparing nodes. The same applies to time: actual time=0.010..0.400 loops=10000 is four seconds.

Estimated rows against actual rows

The single most useful comparison in a plan is the estimated rows against the actual rows on the same line. The planner chose join methods, join order and memory from its estimates. When an estimate is off by ten times or more, the plan was chosen for a different query than the one that ran.

In the example, the scan on orders was expected to return 10 rows and returned 48,200. With 10 rows, a nested loop and an in-memory sort are perfect choices. With 48,200 they are not, and both went wrong for exactly that reason.

Common causes, from most to least likely:

• Statistics that predate a large load or delete. Refresh them with ANALYZE, ANALYZE TABLE or UPDATE STATISTICS.
• A function or expression the planner cannot estimate, such as lower(email): it falls back to a fixed guess, which is where the 500 on the customers line comes from.
• Columns that are correlated but estimated as independent.

Find the deepest node where the estimate goes wrong. Nodes above it inherit the error, so fixing the first one often fixes the rest.

Rows removed by a filter

Rows Removed by Filter counts rows a node read and then discarded. Like rows, it is an average per loop. Next to a sequential scan it measures wasted work directly: the customers scan read 100,000 rows to keep one, and the orders scan read two million to keep 48,200. Both are asking for an index.

Next to an index scan it means something different: the index found candidate rows by some of the conditions, and the rest were checked afterwards. A large number there says the index covers part of the WHERE clause. Adding the remaining filtered columns to it, after the ones it already has, lets the index do all the filtering.

Nested loops, hash joins and merge joins

None of these is good or bad on its own. A nested loop is the fastest join there is for a handful of rows, and the slowest for a million. What makes it slow in the example is not the loop but the estimate that told the planner only one small lookup would be needed.

Sorts and hashes that spill to disk

Sorts and hash tables work in memory up to a limit (work_mem in PostgreSQL, per operation). Past that limit they write to temporary files, which is many times slower. The plan says so:

• Sort Method: external merge Disk: 10240kB — the sort spilled. quicksort or top-N heapsort with Memory: means it stayed in memory.
• Buckets: 4096 Batches: 8 under a Hash node — more than one batch means the hash table was split across disk.

The best fix is to give the operation fewer rows, or to let an index deliver them already sorted. Raising work_mem for the one query (SET LOCAL work_mem = '64MB' inside its transaction) is a reasonable second choice; raising it for the whole server multiplies by every sort in every connection.

Finding where the time goes

A node's actual time includes everything beneath it. To see what a node cost on its own, take its last time multiplied by its loops, and subtract the same for each of its children. In the example:

• Sort: 412.3 − 398.1 = about 14 ms of its own.
• Nested loop: 398.1 − 38.4 − 352.9 = about 7 ms.
• Seq Scan on orders: 352.9 ms, 85% of the 414 ms execution time.

That is where the work is. An index on orders (customer_id, status) would turn the two-million-row scan into a lookup and take most of the runtime with it; the bad estimate, the nested loop and the disk sort would stop mattering.

Three details keep this honest:

• Planning Time is separate from Execution Time. A very long planning time points at the query's complexity, not its data.
• In a parallel plan, times on nodes under a Gather are per worker and overlap, so the subtraction is approximate there.
• With BUFFERS, a large read= compared to hit= means the data came from disk. Running the same query again, when it is cached, can be ten times faster, so compare warm runs with warm runs.

The same ideas in MySQL and SQL Server

MySQL's EXPLAIN ANALYZE prints the same tree with different words:

-> Nested loop inner join  (cost=5231.40 rows=498) (actual time=0.120..209.900 rows=20 loops=1)
    -> Filter: (c.country = 'IN')  (cost=1021.25 rows=998) (actual time=0.060..12.400 rows=410 loops=1)
        -> Table scan on c  (cost=1021.25 rows=9980) (actual time=0.050..10.100 rows=100000 loops=1)
    -> Filter: (o.status = 'open')  (cost=0.25 rows=0.5) (actual time=0.450..0.480 rows=0 loops=410)
        -> Table scan on o  (cost=0.25 rows=5) (actual time=0.010..0.400 rows=2000 loops=410)

Filtering is its own node above the scan, so "rows removed" is the difference between the scan's rows and the filter's. Rows and times are per loop, as in PostgreSQL. The last line is the costliest pattern there is: a full table scan of o, 410 times, 820,000 rows read to find nothing.

SQL Server shows the plan graphically, with the numbers in each operator's properties. The same questions apply, with three differences in how to answer them:

• Actual Number of Rows is the total over all executions, not an average. Compare it with Estimated Number of Rows multiplied by Number of Executions.
• Number of Rows Read, where shown, against Actual Number of Rows is SQL Server's version of rows removed by a filter.
• A yellow warning triangle marks spills to tempdb and implicit conversions, and green text above the plan suggests a missing index. Read the suggestion as a hint; it is made for this one query only.

What to look at first

1. The node with the most time of its own, after multiplying by loops and subtracting its children.
2. The deepest node whose estimated and actual rows differ by ten times or more.
3. Scans with a large Rows Removed by Filter, and scans with a large loops.
4. Sorts and hashes that went to disk.

The SQL Query Analyzer's Advanced level reads plans from PostgreSQL (text or JSON), MySQL and SQL Server and reports exactly these, with the plan line each comes from. When the answer turns out to be "the index was not used", the guide to ignored indexes covers the reasons.