How a Query Executes and Reading EXPLAIN

Concept

You describe what you want; the optimiser decides how to get it. EXPLAIN shows you that decision without running the query, which makes it the first tool to reach for whenever something is slow.

Syntax

EXPLAIN SELECT ...;                     -- the estimated plan
EXPLAIN ANALYZE SELECT ...;             -- MySQL 8.0.18+, PostgreSQL: runs it and shows actuals
EXPLAIN FORMAT=JSON SELECT ...;         -- MySQL: full cost detail
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;  -- PostgreSQL
SET SHOWPLAN_ALL ON;                    -- SQL Server

Example

EXPLAIN
SELECT c.name, o.order_date, o.total
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
WHERE  o.status = 'shipped'
ORDER BY o.order_date DESC;

Reading the output

Access types, best to worst

The Extra column

Estimates versus reality

-- EXPLAIN estimates. EXPLAIN ANALYZE actually runs the query and reports what happened.
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) AS orders
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Output includes: actual time=..., rows=..., loops=...

When the estimated rows is wildly different from the actual, the statistics are stale. Run ANALYZE TABLE before concluding anything else is wrong.

Finding the queries to look at

-- MySQL: log statements slower than a threshold
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- seconds

-- MySQL 8: the worst statements by total time
SELECT query, exec_count, total_latency, rows_sent_avg, rows_examined_avg
FROM   sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;

-- PostgreSQL, with pg_stat_statements enabled
SELECT query, calls, total_exec_time FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

Optimise by total time, not worst single time. A query taking 50ms and running 100,000 times a minute matters far more than a 5 second report run nightly.

Important rules

• EXPLAIN does not execute the query (except EXPLAIN ANALYZE, which does).
• Plans depend on statistics and on the actual data volume - a plan from a 100 row test database means nothing.
• The optimiser picks the plan it estimates cheapest; a full scan can genuinely be the right choice.
• rows is an estimate. Compare it with rows actually returned.
• Plan output formats differ completely between products; the concepts transfer, the columns do not.

Common mistakes

• Guessing at the cause instead of reading the plan.
• Testing on an empty or tiny table.
• Chasing the slowest single query while ignoring the one running a million times an hour.
• Adding index hints to force a plan instead of fixing the statistics or the query.
• Ignoring Using temporary and Using filesort on large result sets.

Best practices

• Read the plan before and after every change, and keep both.
• Refresh statistics with ANALYZE TABLE when estimates look wrong.
• Measure on production sized data.
• Drive the work from the slow query log, ordered by total time.
• Aim to make rows examined approach rows returned; that ratio is the single best health metric for a query.

Practice

1. Run EXPLAIN on a join in the sample schema and identify the type and key for each table.
2. Force a Using filesort with an ORDER BY, then remove it with an index.
3. Explain what a large gap between rows and rows returned tells you.