Reading EXPLAIN ANALYZE
Overview
EXPLAIN ANALYZE executes your query and returns a detailed execution plan showing exactly how the database retrieved the rows. It reveals whether it used an 'Index Scan' (using a B-Tree index to find rows instantly), a 'Seq Scan' (scanning the entire table row-by-row), or a specific JOIN algorithm.
It is the primary diagnostic tool for SQL. Without it, tuning a query is just guesswork.
Where used: troubleshooting production database latency, verifying index usage
Why learn this
- Stop guessing why a query is slow and see the exact bottleneck.
- Prove that a new index is actually being used by the database optimizer.
Query walkthrough
EXPLAIN ANALYZE SELECT name FROM customers WHERE country = 'US' ORDER BY id;Focus: The EXPLAIN output is returned as a set of text rows describing the execution plan, showing the Seq Scan or Index Scan.
Common mistakes
- Forgetting the ANALYZE keyword: Using just EXPLAIN shows the database's *estimated* plan. Adding ANALYZE actually runs the query and shows *actual* execution times and row counts.
Glossary
- execution
- The actual process of running a query or command within the database system.
- diagnostic
- Relating to tools or processes used to identify the cause of a problem, like finding why a query is slow.
- optimizer
- The part of the database system that figures out the fastest and most efficient way to execute your query.
Recall questions
- What does a 'Seq Scan' node in an EXPLAIN plan indicate?
Understanding checks
If `id` is a primary key, what scan type will likely appear in the EXPLAIN output?
Index Scan
Because `id` has a primary key index, the database can perform an Index Scan instead of scanning the whole table.
If there is no index on `status`, what scan type will appear?
Seq Scan
Without an index on the `status` column, the database must scan the table row-by-row to find matching rows.
Practice tasks
Check the Execution Plan
Given this query, change it so that it returns the execution plan with actual runtimes instead of the query results.
Challenge
Explain a Join
Write a query that returns the execution plan and actual run times for joining customers and orders where the customer_id is 1. (Select customer name and order status).
Continue learning
Previous: How Databases Use Indexes