Reading a query plan with EXPLAIN without fear

SQL is a declarative language: you describe the result you want, not how to get it. Deciding how to read the tables, in which order to join them and which indexes to use is the job of a database component called the optimiser. The execution plan is its decision written down, and the EXPLAIN command lets you read it. It is the most useful tool for understanding why a query is slow, instead of guessing.

The basic command

Just put EXPLAIN in front of the query:

EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

The database does not run the query, but shows the expected plan. PostgreSQL also has EXPLAIN ANALYZE, which actually runs it and adds real timings and row counts next to the estimates. Be careful: with an UPDATE or DELETE, running it really changes the data, so it is wise to wrap it in a transaction and undo it with ROLLBACK.

The examples below use PostgreSQL syntax; MySQL, SQL Server and SQLite have equivalent commands with a different output format, but the concepts are the same.

How to read the output

A plan is a tree of operations. You read it from the inside out: the most indented lines run first and pass their result to the node that contains them.

Hash Join  (cost=35.50..410.20 rows=1200 width=64)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..340.00 rows=20000 width=40)
  ->  Hash  (cost=25.00..25.00 rows=840 width=24)
        ->  Seq Scan on customers c  (cost=0.00..25.00 rows=840 width=24)
              Filter: (city = 'Turin')

Here the database reads the customers in Turin, builds an in-memory hash table from them, then scans all orders and, for each one, checks whether the customer is in the hash table.

The numbers in brackets

• cost: an estimate of work in arbitrary units. The first value is the cost before the first row is returned, the second the total cost. It is for comparing alternative plans, not for predicting seconds.
• rows: how many rows the optimiser expects the node to produce.
• width: the estimated average size of a row, in bytes.

With EXPLAIN ANALYZE you also see actual time, the actual rows and the number of times the node was repeated (loops).

Operations to recognise

Ways of reading a table

A Seq Scan is not a mistake in itself. It becomes one when it runs on a large table to return a few rows.

Ways of joining two tables

• Nested Loop: for each row of the first table, it looks up matches in the second. Excellent when the first produces few rows and the second has an index on the join column.
• Hash Join: builds a hash table from the smaller input and probes it with the other. Suitable for large volumes without useful indexes.
• Merge Join: merges two streams already sorted on the join column, walking through them in parallel.

A diagnosis example

A query looking up a customer's orders takes too long:

Seq Scan on orders  (actual time=0.02..180.40 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 1999988

The message is clear: the database read two million rows to keep 12. An index on customer_id is missing. After creating it:

Index Scan using idx_orders_customer on orders
  (actual time=0.03..0.09 rows=12 loops=1)
  Index Cond: (customer_id = 42)

The time drops from about 180 milliseconds to under one, on the same machine.

Wrong estimates: the sneakiest problem

The optimiser chooses the plan based on statistics about the data: how many rows there are, how many distinct values, how they are distributed. If the statistics are stale or the data has unusual distributions, the estimates go wrong. The symptom, in EXPLAIN ANALYZE, is a large gap between estimated and actual rows, for example 10 expected and 500,000 real.

With an estimate that far off, the optimiser may choose a Nested Loop expecting to repeat it ten times and end up repeating it half a million times. The most common fixes are:

1. refresh the statistics (in PostgreSQL with ANALYZE, in MySQL with ANALYZE TABLE);
2. avoid applying functions to filtered columns, such as WHERE year(date) = 2025, which make estimation hard and prevent index use: a date range is better;
3. rewrite complex conditions in forms the optimiser understands better.

A working method

1. Run EXPLAIN ANALYZE on a realistic copy of the data, not on a nearly empty test database.
2. Look for the node that takes most of the time.
3. Check whether it reads far more rows than it returns.
4. Compare estimated and actual rows.
5. Change one thing at a time and read the plan again.

In short

• EXPLAIN shows how the database plans to run a query; EXPLAIN ANALYZE runs it and measures.
• A plan is read from the innermost nodes outwards.
• A Seq Scan on a large table returning few rows often points to a missing index.
• Large gaps between estimated and actual rows point to stale statistics or conditions to rewrite.
• Optimise one change at a time, checking the new plan each time.