How to Read EXPLAIN

Lesson 10.4 · Reading time: ~12 min · Practice tasks ↓

EXPLAIN shows how the database is going to run your query: which tables it reads, in what order, whether it uses an index and how many rows it expects. In this lesson you will read real plans from MySQL and PostgreSQL line by line, compare the optimizer's estimates with the actual numbers from EXPLAIN ANALYZE and learn the signs that point to a slow query.

In the previous lesson you created indexes. But an index helps only if the database actually uses it, and the only way to know is to look at the execution plan. The plan replaces guessing with facts: instead of "the query is slow, maybe add an index" you see exactly which step reads too many rows.

What an execution plan is

Before running a query, the optimizer chooses how to do it: which table to read first, whether to use an index or scan the whole table, how to join tables and how to sort. The result of this choice is the execution plan. The optimizer picks the plan with the lowest estimated cost, and it estimates the cost from statistics: how many rows each table has and how values are distributed.

You get the plan by putting a keyword in front of the query:

EXPLAIN only builds the plan and does not run the query. EXPLAIN ANALYZE runs it and adds the real time and row counts.

MySQL: the table format

Let's find the payments of customers named Smith in the Sakila database:

EXPLAIN
SELECT c.first_name, c.last_name, p.amount, p.payment_date
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
WHERE c.last_name = 'SMITH';

Result (MySQL 8.0):

+----+-------------+-------+------+-----------------------+--------------------+----------------------+------+----------+-------+
| id | select_type | table | type | possible_keys         | key                | ref                  | rows | filtered | Extra |
+----+-------------+-------+------+-----------------------+--------------------+----------------------+------+----------+-------+
| 1  | SIMPLE      | c     | ref  | PRIMARY,idx_last_name | idx_last_name      | const                | 1    | 100.00   | NULL  |
| 1  | SIMPLE      | p     | ref  | idx_fk_customer_id    | idx_fk_customer_id | sakila.c.customer_id | 26   | 100.00   | NULL  |
+----+-------------+-------+------+-----------------------+--------------------+----------------------+------+----------+-------+

Each row is one table in the order MySQL reads them: first customer, then for every customer found, payment. Here is what the columns mean:

• table: the table (or its alias) read at this step.
• type: how the rows are accessed. This is the most important column.
• possible_keys: indexes that could be used; key: the index actually chosen. NULL in key means no index.
• ref: what the index is compared with: a constant (const) or a column of the previous table (sakila.c.customer_id).
• rows: how many rows MySQL expects to read at this step. It is an estimate, not a fact.
• filtered: the percentage of those rows expected to pass the remaining conditions.
• Extra: extra details, see below.

Reading the plan: MySQL finds the customer by the index on last_name (1 row expected), then for that customer looks up the payments by the index on customer_id (26 rows expected). Both steps use indexes, so the query reads a few dozen rows out of more than 16,000 payments.

Access types in the type column

From the best to the worst:

ALL on a small reference table is fine. ALL on a large table in a query that needs a few rows is the first thing to fix.

The Extra column

• Using index: all needed columns are in the index, the table itself is not read (a covering index). Good.
• Using where: rows are filtered after reading. Normal by itself, a problem together with ALL on a big table.
• Using index condition: the condition is checked inside the index before reading the row (index condition pushdown).
• Using temporary: an intermediate temporary table is built, usually for GROUP BY or DISTINCT.
• Using filesort: rows are sorted separately, not read in index order.

For example, the five customers who paid the most:

EXPLAIN
SELECT customer_id, SUM(amount) AS total
FROM payment
GROUP BY customer_id
ORDER BY total DESC
LIMIT 5;

Result: type = index, rows = 15577, Extra = Using temporary; Using filesort. MySQL reads the whole index, groups the payments in a temporary table and sorts the sums. There is no way to avoid this here: the order by a computed sum is unknown until all rows are grouped. On 16,000 rows it is fast; on 100 million rows such a report would be precomputed (for example, in a summary table).

How a function hides an index

The rental table has an index on rental_date. Compare two queries that return the same 504 rentals of 6 July 2005:

EXPLAIN
SELECT *
FROM rental
WHERE rental_date >= '2005-07-06' AND rental_date < '2005-07-07';

Result: type = range, key = rental_date, rows = 504.

EXPLAIN
SELECT *
FROM rental
WHERE DATE(rental_date) = '2005-07-06';

Result: type = ALL, key = NULL, rows = 15831.

The index is sorted by rental_date, not by DATE(rental_date). When the column is wrapped in a function, the database can't search the index and checks every row. The fix is to keep the column bare and move the computation to the other side of the comparison, as in the first query. Conditions that can use an index this way are called SARGable.

Plan trees: MySQL FORMAT=TREE and EXPLAIN ANALYZE

The table format hides how the steps are nested. MySQL 8 can show the plan as a tree:

EXPLAIN FORMAT=TREE
SELECT c.first_name, c.last_name, p.amount, p.payment_date
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
WHERE c.last_name = 'SMITH';

-> Nested loop inner join  (cost=9.45 rows=26)
    -> Index lookup on c using idx_last_name (last_name='SMITH')  (cost=0.35 rows=1)
    -> Index lookup on p using idx_fk_customer_id (customer_id=c.customer_id)  (cost=9.1 rows=26)

Read a tree from the inside out: the most indented lines run first, and every line passes its rows to the line above it. Here the first child finds the customer, the second child finds the payments for each customer, and the Nested loop inner join above them combines the rows.

EXPLAIN ANALYZE runs the query and adds the actual numbers to every step:

-> Nested loop inner join  (cost=9.45 rows=26) (actual time=0.139..0.157 rows=32 loops=1)
    -> Index lookup on c using idx_last_name (last_name='SMITH')  (cost=0.35 rows=1) (actual time=0.0572..0.0586 rows=1 loops=1)
    -> Index lookup on p using idx_fk_customer_id (customer_id=c.customer_id)  (cost=9.1 rows=26) (actual time=0.0555..0.0688 rows=32 loops=1)

• rows=26 is the estimate, actual ... rows=32 is the fact. Mr Smith made 32 payments, while the statistics suggested 26 on average per customer.
• actual time=0.0555..0.0688: milliseconds until the first row and until the last row of this step.
• loops: how many times the step ran. The inner side of a nested loop runs once for every row of the outer side.

An estimate off by 20% is normal. An estimate off by 100 times means the optimizer chose the plan on wrong data, and the plan may be wrong too.

PostgreSQL: reading the plan tree

PostgreSQL always shows a tree. A query by primary key in the Bookings database:

EXPLAIN
SELECT *
FROM bookings.flights
WHERE flight_id = 1001;

Index Scan using flights_pkey on flights  (cost=0.29..8.31 rows=1 width=63)
  Index Cond: (flight_id = 1001)

Each node shows:

• the operation: Seq Scan (the whole table), Index Scan (index plus table rows), Index Only Scan (the index alone), Bitmap Heap Scan (first collects the matching rows from the index, then reads them from the table in storage order);
• cost=0.29..8.31: the estimated cost until the first row and for all rows, in abstract units (not milliseconds);
• rows: the estimated number of output rows; width: the average row size in bytes;
• the lines below: the index condition (Index Cond) or the filter (Filter).

There is no index on departure_airport, so a search by airport reads the whole table:

EXPLAIN
SELECT flight_id, scheduled_departure
FROM bookings.flights
WHERE departure_airport = 'DME';

Seq Scan on flights  (cost=0.00..806.01 rows=3217 width=12)
  Filter: (departure_airport = 'DME'::bpchar)

Here a full scan is reasonable: the query returns about 10% of the table, and reading 33,000 rows in order is cheap.

EXPLAIN ANALYZE in PostgreSQL

The tickets of one booking:

EXPLAIN ANALYZE
SELECT t.passenger_name, tf.fare_conditions, tf.amount
FROM bookings.tickets t
JOIN bookings.ticket_flights tf ON tf.ticket_no = t.ticket_no
WHERE t.book_ref = '00000F';

Gather  (cost=1000.42..9103.17 rows=6 width=30) (actual time=409.864..415.535 rows=2.00 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop  (cost=0.42..8102.57 rows=2 width=30) (actual time=388.369..389.800 rows=0.67 loops=3)
        ->  Parallel Seq Scan on tickets t  (cost=0.00..8086.07 rows=1 width=30) (actual time=386.664..387.206 rows=0.33 loops=3)
              Filter: (book_ref = '00000F'::bpchar)
              Rows Removed by Filter: 122244
        ->  Index Scan using ticket_flights_pkey on ticket_flights tf  (cost=0.42..16.47 rows=3 width=28) (actual time=5.061..7.713 rows=2.00 loops=1)
              Index Cond: (ticket_no = t.ticket_no)
Planning Time: 45.638 ms
Execution Time: 415.774 ms

Reading from the inside out:

1. Parallel Seq Scan on tickets: three processes (loops=3, the leader and two workers) scan the whole tickets table. Each discards about 122,000 rows (Rows Removed by Filter) to find a single ticket.
2. Index Scan on ticket_flights: for the ticket found, its flight segments are taken by the primary key index. Fast: under 8 ms.
3. Nested Loop joins them, Gather collects the results of the parallel workers.

The whole query takes 415 ms, and almost all of it is the first step. The bottleneck is the node with the largest actual time and the most removed rows. The fix is an index on tickets (book_ref).

In PostgreSQL, rows and loops work together: a node reports rows per loop, so the total is rows × loops. rows=0.33 loops=3 means one row in total.

Before and after an index

The same diagnosis on a test table orders with 200,000 rows (10,000 customers, 20 orders each):

EXPLAIN ANALYZE
SELECT id, amount, created_at
FROM orders
WHERE customer_id = 42;

Without an index:

Seq Scan on orders  (cost=0.00..3774.00 rows=20 width=14) (actual time=0.030..19.330 rows=20.00 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 199980
Execution Time: 19.379 ms

After CREATE INDEX orders_customer_id_idx ON orders (customer_id);:

Bitmap Heap Scan on orders  (cost=4.45..77.18 rows=20 width=14) (actual time=0.142..0.188 rows=20.00 loops=1)
  Recheck Cond: (customer_id = 42)
  ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.45 rows=20 width=0) (actual time=0.120..0.120 rows=20.00 loops=1)
        Index Cond: (customer_id = 42)
Execution Time: 0.293 ms

199,980 discarded rows became zero, and the query became about 65 times faster. Now write the condition as customer_id + 0 = 42: the plan goes back to Parallel Seq Scan, and the estimate becomes rows=1000 instead of 20, because the optimizer has no statistics for the expression.

Join methods

A plan with several tables shows how they are joined:

• Nested Loop: for every row of the outer input, look up matching rows in the inner input. Fast when the outer input is small and the inner one has an index on the join column, as in all the examples above.
• Hash Join: build a hash table from the smaller input, then pass the larger one through it. Good for large inputs without a useful index.
• Merge Join: both inputs are sorted by the join key and merged. Good when the data is already sorted, for example read from indexes.

MySQL mostly uses nested loops and, since 8.0.18, hash joins; PostgreSQL and SQL Server use all three.

Red flags in a plan

Look at the plan as a whole. The goal is not to remove every full scan but to find the step where most of the time goes.

Using EXPLAIN ANALYZE safely

EXPLAIN ANALYZE really executes the query. For SELECT this only costs time, but EXPLAIN ANALYZE UPDATE ... or DELETE ... changes the data. In PostgreSQL, wrap such a check in a transaction:

BEGIN;
EXPLAIN ANALYZE
UPDATE orders SET amount = amount * 1.1 WHERE customer_id = 42;
ROLLBACK;

Also remember that the first run may read data from disk and later runs from memory. Compare plans and times after several runs, and on data close to production in size: on a table of 100 rows almost any plan is fast.

Key takeaways from this lesson:

• EXPLAIN shows the plan the optimizer chose; EXPLAIN ANALYZE runs the query and adds the actual time and rows.
• In MySQL start with type, key, rows and Extra; ALL and key = NULL on a big table are the first suspects.
• Read plan trees from the inside out: nested steps run first and pass rows up.
• The bottleneck is the step with the largest actual time and the most discarded rows.
• A big difference between estimated and actual rows points to outdated statistics.
• A function or expression on an indexed column usually turns an index search into a full scan.
• EXPLAIN ANALYZE of UPDATE or DELETE changes data: run it in a transaction and roll back.

Frequently Asked Questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN only shows the plan with the optimizer's estimates and does not run the query. EXPLAIN ANALYZE executes the query and shows the actual time, row counts and loops for every step, so you can compare estimates with reality.

Is a full table scan always bad?

No. A full scan is the best choice for small tables and for queries that return a large part of the table: reading rows in order is cheaper than jumping between the index and the table. It is a problem when a large table is scanned to find a few rows.

Why does the database ignore my index?

Common reasons: the column is wrapped in a function or an expression, LIKE starts with %, the types of the compared values differ, the condition matches a large share of rows, or the statistics are outdated. The plan shows which of these is the case.

What does cost mean in a plan?

Cost is the optimizer's estimate of the work in abstract units (page reads and CPU operations), not milliseconds. It is useful for comparing plans of the same query; for real time use EXPLAIN ANALYZE.

Interview Questions

How do you find out why a query is slow?

I run EXPLAIN ANALYZE (or look at the actual execution plan) and find the step with the largest time and the largest number of rows read and discarded. Then I check whether it uses an index, whether the condition is SARGable and whether the estimated rows match the actual ones.

What do the access types ALL, index, range and ref mean in MySQL?

ALL reads the whole table; index reads the whole index; range reads a range of an index for BETWEEN, > or IN; ref finds rows by an index value. const and eq_ref find at most one row by a unique key.

The optimizer expected 10 rows but got 100,000. What will you do?

The plan was built on wrong estimates, so the join method or the join order may be wrong. I update the statistics (ANALYZE), check that the condition does not hide the column in an expression, and compare the plan again. If the data is skewed, extended statistics or a rewritten query can help.