Query Plan & EXPLAIN Analysis

Also Known As

TL;DR

Explanation

Every SQL query is compiled into an execution plan by the query optimiser. EXPLAIN (MySQL/PostgreSQL) shows this plan: table access type (const, ref, range, index, ALL — ALL is a full table scan and nearly always a problem), estimated row counts, indexes used, join strategy, and temp table/filesort usage. EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL 8) shows actual execution statistics. Key red flags: type=ALL on large tables, Using filesort, Using temporary, and high rows_examined vs rows_returned ratios. Run EXPLAIN before adding indexes — the plan tells you exactly which index to create.

Common Misconception

Why It Matters

Common Mistakes

• Not using EXPLAIN before adding indexes — the plan shows whether an index would actually be used.
• Reading EXPLAIN output without checking actual row counts — estimated vs actual rows reveals stale statistics.
• Not using EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) for full detail.
• Optimising based on EXPLAIN on a small dev dataset — plans change dramatically with production data volumes.

Code Examples

-- Running slow query without checking the plan:
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending';
-- Just add EXPLAIN first:
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending';
-- Look for: Seq Scan (bad on large tables), Nested Loop with high rows

-- MySQL EXPLAIN
EXPLAIN SELECT * FROM orders
    WHERE user_id = 42 AND status = 'paid'
    ORDER BY created_at DESC LIMIT 10;
-- Look for: type (ALL=bad, ref/range=ok, const=best), key (index used), rows estimate

-- PostgreSQL EXPLAIN ANALYZE (actually executes)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
    SELECT * FROM orders
    WHERE user_id = 42 AND status = 'paid'
    ORDER BY created_at DESC LIMIT 10;
-- Look for: Seq Scan on large tables, high rows estimate vs actual, cache hits

-- Paste output into explain.dalibo.com for visual analysis

References

• ↗ https://use-the-index-luke.com/sql/explain-plan
• ↗ https://dev.mysql.com/doc/refman/8.0/en/explain.html

Tags

Related Terms