Reading an EXPLAIN Plan in MySQL and PostgreSQL

The plan says rows=5007, the query returns five rows, and the report runs for twenty seconds. Nothing in the plan is lying; the number is a prediction, not a count, and reading it as a measurement is the mistake that makes people distrust EXPLAIN.

The examples ran on MySQL 9.6.0 with InnoDB, and the output is pasted as it came back. MySQL 9.6 defaults to the tree format for EXPLAIN, and both formats carry the same fields under different names, so this lesson uses both and says which is which. PostgreSQL 16.14 is installed here but has no running server, so its node names come from its own manual and no psql output appears.

EXPLAIN estimates. EXPLAIN ANALYZE runs the query and measures

EXPLAIN asks the planner what it would do and returns without executing the statement. EXPLAIN ANALYZE executes it, throwing away the result rows, and annotates every node with what actually happened. The difference is the whole discipline of reading plans, because an estimate can be wrong and a measurement cannot.

Here is the same query both ways. The table is big_orders: 50,000 rows, a primary key, and a secondary index on (customer_id, created_at). The predicate is on a column that is not indexed.

EXPLAIN SELECT id FROM big_orders WHERE amount_cents = 5000;

-> Filter: (big_orders.amount_cents = 5000)  (cost=5047 rows=5007)
    -> Table scan on big_orders  (cost=5047 rows=50065)

EXPLAIN ANALYZE SELECT id FROM big_orders WHERE amount_cents = 5000;

-> Filter: (big_orders.amount_cents = 5000)  (cost=5047 rows=5007) (actual time=4.77..20.5 rows=5 loops=1)
    -> Table scan on big_orders  (cost=5047 rows=50065) (actual time=0.85..17.5 rows=50000 loops=1)

The estimated five thousand and seven rows is off by a factor of a thousand. The planner had no distribution statistics for amount_cents, because nothing had ever asked it to build any, so it applied the default guess of ten percent selectivity to the 50,065 estimated table rows. The measured run says five.

Add the index and run the same pair again:

CREATE INDEX idx_amount ON big_orders (amount_cents);
ANALYZE TABLE big_orders;
EXPLAIN ANALYZE SELECT id FROM big_orders WHERE amount_cents = 5000;

-> Covering index lookup on big_orders using idx_amount (amount_cents = 5000)
     (cost=1.51 rows=5) (actual time=0.00633..0.00771 rows=5 loops=1)

The estimate is now exact, because building the index built a histogram of the values at the same time. This is not a coincidence to rely on; it is the reason ANALYZE TABLE is part of loading data, and the reason a plan can change for the better without the query changing at all. It is also a trap: index statistics go stale, and a plan chosen from bad statistics is executed with the same confidence as a plan chosen from good ones.

A plan is a tree, and it runs from the inside out

Every plan is a tree of nodes. The indentation shows who feeds whom, and execution starts at the deepest node and moves outward, which is the opposite of how the text reads. In the tree format MySQL prints the operator and its predicate, then the cost, then, under analyzing, the timing and the loop count.

SET SESSION explain_format = 'TRADITIONAL';
EXPLAIN SELECT id FROM big_orders WHERE customer_id = 137;

table       type  key                   key_len  ref    rows  filtered  Extra
big_orders  ref   idx_customer_created        4  const   250    100.00  Using index

The traditional format puts six columns in front of you. type is the access method, and it is ref here. key is the index the planner chose, key_len is how many bytes of that index the seek uses, and four bytes is one integer, so only customer_id is positioning the scan. The ref column says what the index columns are matched against, a constant in this case. rows is the estimate. Using index in Extra means the query was answered from the index without reading the clustered index, which the tree format spells out as "Covering". Switch explain_format to TREE and the same plan reads -> Covering index lookup on big_orders using idx_customer_created (customer_id = 137) (cost=25.4 rows=250). Cost is a unitless number compared against alternatives, not milliseconds.

Under EXPLAIN ANALYZE in the tree format, every node gains four measurements:

-> Covering index lookup on big_orders using idx_customer_created (customer_id = 137)
     (cost=25.4 rows=250) (actual time=0.0266..0.0397 rows=250 loops=1)

The two times are the time to the first row and the time to the last row from that node, in milliseconds accumulated over all its loops. rows inside actual is the measured row count per loop. loops is how many times the node ran. When loops is larger than one, multiplying the two is the total work, and that multiplication is where nested loop disasters hide.

Five access types, and what the corresponding PostgreSQL node is called

MySQL names the access type per table in the traditional format, and the tree format embeds it in the operator name. There are five worth recognising, and the difference between them is how much of the index or table the engine has to touch.

EXPLAIN SELECT id, customer_id, amount_cents FROM big_orders WHERE id = 12345;
EXPLAIN SELECT id, amount_cents FROM big_orders WHERE customer_id = 137;
EXPLAIN SELECT id, amount_cents FROM big_orders WHERE id BETWEEN 20000 AND 20100;
EXPLAIN SELECT customer_id FROM big_orders WHERE customer_id + 1 > 0;
EXPLAIN SELECT id, amount_cents FROM big_orders WHERE amount_cents = 5000;

table       type   key                    rows  Extra
big_orders  const  PRIMARY                   1   NULL
big_orders  ref    idx_customer_created    250   NULL
big_orders  range  PRIMARY                 101   Using where
big_orders  index  idx_customer_created  50065   Using where; Using index
big_orders  ALL    NULL                  50065   Using where

const means at most one row matches a unique or primary key lookup, and the planner may even resolve the row during planning. ref is equality against a non-unique index: one seek, many rows. range is a bounded interval in the index, read in order. index is a full scan of an index, which is cheaper than a table scan only because the index is narrower; the fourth query uses it because customer_id + 1 is not a searchable expression, so the engine reads every index entry and applies the expression to each. ALL is a full table scan, and it means no index could help with this predicate.

Those five map onto SQLite and PostgreSQL node names, which are worth knowing because a plan pasted from another engine reads differently. SQLite reports SEARCH ... USING INDEX, SCAN, and USING COVERING INDEX. PostgreSQL names the operators instead of the access paths. A Seq Scan is a sequential scan of the heap, the equivalent of ALL. An Index Scan walks an index and then visits the heap for each matching row, one row at a time. A Bitmap Heap Scan reads the index first, builds a bitmap of the heap pages it needs, and then visits those pages in physical order. The PostgreSQL manual describes the bitmap as a way to reduce the random access cost of an index scan, and which of the two the planner picks is a decision it makes per query. An Index Only Scan is the index-only case, and only a Seq Scan is an unqualified cause for concern, exactly as ALL is in MySQL.

rows and filtered predict. The actual block and the counters measure

In the traditional format the prediction lives in two columns: rows is the estimated number of rows the node will emit, and filtered is the estimated percentage of those that survive the table's own conditions. Their product is the number the next node up plans with.

EXPLAIN SELECT id, amount_cents FROM visits2
 WHERE customer_id = 137 AND created_at > '2026-01-01 10:00:00' AND status = 'paid';

table    type   key                     rows  filtered  Extra
visits2  range  idx_cust_created_status   70     10.00  Using index condition

Seventy rows from the range, ten percent of them expected to pass the remaining condition, so the planner tells the node above it to expect seven. The measured truth arrives from three places. EXPLAIN ANALYZE gives the actual rows per node. SHOW SESSION STATUS gives the handler counters, where Handler_read_next counts entries read in order and Handler_read_rnd_next counts rows read by scanning. And SHOW SESSION STATUS LIKE 'Sort%' covers sorting. Those counters are the only way to see work a plan does not print as a node.

The one rule worth carrying: when rows and actual rows disagree by more than an order of magnitude, the planner chose the plan using a number that was wrong, and every cost comparison above that node inherited the error. Fix the statistics before rewriting the query.

A sequential scan on a large table is trouble only when it reads more than it needs

This is the most misunderstood of the three. A sequential scan of a table that fits in the buffer pool and returns a large fraction of its rows is the correct plan, and forcing an index would make it slower. The scan becomes a problem when the predicate is selective and the table is large, because then the engine reads every page to reject almost all of them.

The twenty-millisecond table scan at the top of this lesson reads 50,000 rows to return five. On this machine that is fast, because the whole table is 2 MB and sits in memory. On a table of 200 million rows it is the difference between a report and an outage, and nothing about the plan shape changes except the row count. The diagnostic is arithmetic rather than a keyword: multiply the rows the scan reads by the row size, and ask whether that is more data than the query needs. The index that cuts this query from 50,000 rows to five is the answer, and the estimate error is why the planner did not ask for one.

A sort that spilled is invisible in the plan and visible in the counters

Sorting happens in memory up to sort_buffer_size per session. Past that, MySQL writes runs of sorted rows to temporary files and merges them. The plan says Using filesort and says nothing about which of the two happened, so the merge is only visible in the status counters, where Sort_merge_passes counts the merges and a non-zero value means the sort did not fit.

SET SESSION sort_buffer_size = 32768;
FLUSH STATUS;
INSERT INTO sorted_copy
SELECT id, customer_id, amount_cents FROM big_orders
ORDER BY amount_cents, customer_id, id;
SHOW SESSION STATUS LIKE 'Sort%';

Variable_name       Value
Sort_merge_passes      12
Sort_range              0
Sort_rows           50000
Sort_scan               1

Twelve merge passes to order 50,000 rows with a 32 KB buffer. The plan for the same statement is a table scan with Using filesort, which is true and uninformative. A sort of the same size that fits entirely in memory reports Sort_merge_passes at zero, and the fix for the spill is either a larger sort_buffer_size on that connection or, better, an index whose order matches the ORDER BY so no sort happens at all. Lesson eleven showed the plan for that case: Using index where this one says Using filesort.

A nested loop is only a problem when the outer side is large

Nested loops are the default join strategy and they are excellent when the inner side is an index lookup. The engine takes each row from the outer side and seeks into the inner side, so the cost is the outer row count times one seek. That product is what explodes when the inner side has no usable index, because one seek becomes one full scan.

A demonstration with the counter values that make it concrete. The tables are customers_big with 20,000 rows and big_orders_noidx with 50,000 rows and no index on the join column. STRAIGHT_JOIN forces the 20,000-row table to be the outer side, and the hash join is disabled so the nested loop is the only option:

SET SESSION optimizer_switch = 'block_nested_loop=off,hash_join=off';
EXPLAIN ANALYZE SELECT COUNT(*) FROM customers_big c
  STRAIGHT_JOIN big_orders_noidx o ON o.customer_id = c.id;

-> Aggregate: count(0)  (cost=117e+6 rows=1) (actual time=60072..60072 rows=0 loops=1)
    -> Nested loop inner join  (cost=95.6e+6 rows=94.8e+6) (actual time=3.7..60071 rows=50000 loops=1)
        -> Covering index scan on c using PRIMARY  (cost=1963 rows=19391) (actual time=0.695..9.29 rows=9787 loops=1)
        -> Filter: (o.customer_id = c.id)  (cost=40.3 rows=4890) (actual time=6.01..6.14 rows=5.11 loops=9787)
            -> Table scan on o  (cost=40.3 rows=48900) (actual time=0.252..4.98 rows=49997 loops=9787)

The query did not finish. It ran into a 60-second statement timeout after the outer side had produced 9,787 of its 20,000 rows, and the inner node reports loops=9787 with roughly 50,000 rows scanned per loop. The global counter for rows read by scanning stood at 497,855,511 when it stopped. The estimate that warned about this was rows=94.8e+6, which is the planner saying it expected 94.8 million joined rows.

Give the optimizer the choice, or give the inner table the index, and the same answer arrives quickly:

SET SESSION optimizer_switch = 'block_nested_loop=on,hash_join=on';
-> Aggregate: count(0)  (cost=117e+6 rows=1) (actual time=21.1..21.1 rows=1 loops=1)
    -> Inner hash join (o.customer_id = c.id)  (cost=94.8e+6 rows=94.8e+6) (actual time=5.09..20 rows=50000 loops=1)
        -> Table scan on o  (cost=0.0279 rows=48900) (actual time=0.275..5.18 rows=50000 loops=1)
        -> Hash
            -> Covering index scan on c using PRIMARY  (cost=1963 rows=19391) (actual time=0.0932..1.87 rows=20000 loops=1)

CREATE INDEX idx_customer ON big_orders_noidx (customer_id);
-> Nested loop inner join  (cost=517252 rows=4.89e+6) (actual time=0.114..22.5 rows=50000 loops=1)
    -> Covering index scan on c using PRIMARY  (cost=1963 rows=19391) (actual time=0.0969..2.16 rows=20000 loops=1)
    -> Covering index lookup on o using idx_customer (customer_id = c.id) (cost=1.37 rows=252) (actual time=730e-6..855e-6 rows=2.5 loops=20000)

Twenty-one milliseconds for the hash join, twenty-two for the nested loop with the index in place. Note that the hash join still scans 50,000 rows; it does it once and builds a hash table, which is the trade that makes it immune to the outer row count. Note too that the correct nested loop here has an estimated cost of 517,252 against the hash join's 94.8 million, so the planner would have chosen it without help once the index existed.

Reading a plan is therefore three questions, in order. Which node does the most work, measured in rows per loop times loops? Does the estimated row count at that node match the measured one? And if it does not, is that a missing index, a stale statistic, or a query shape that no index can help?

Show the solution and the reasoning

The table scan on status appears because no index exists on it, and the estimate should be close enough to be plausible while the measured count is close to 10,000, one quarter of the table. The useful comparison is not the absolute error but the direction: a scan that reads 40,000 rows to return 10,000 is doing four times the work it needs, and an index on status would not help much because the predicate matches a quarter of the table. That is the selectivity point from lesson ten, and the honest conclusion is that this query should scan. The estimate error is worth recording because it is the same ten-percent guess as the amount_cents example, and it is the reason to run ANALYZE TABLE after loading.

For the sort, the query is something like SELECT id, customer_id, amount_cents FROM lab_plan ORDER BY amount_cents, and the index that removes the sort is one on (amount_cents) or on (amount_cents, id). The plan changes from a table scan with Using filesort to an index scan in order, and Sort_merge_passes drops to zero only if the sort is gone; if the plan still sorts, the passes stay non-zero and the index did not match the ordering. The lesson is that the plan itself is ambiguous about the spill, so the counter is the only measurement.

The last question has one answer that matters. rows=100 with loops=5000 is 500,000 rows processed through that node, and the plan will not sum it for you; the number in actual rows is per loop. Before deciding it is a problem, check the node's position in the tree and what feeds it. A 500,000-row inner node under a 20,000-row outer node is the nested loop that timed out in this lesson, and it is a problem. The same 500,000 rows read once by a hash join's build side is normal. Position and loop count together decide, and the row count alone never does.