Understanding EXPLAIN Plans
EXPLAIN Statement
Read aloud is unavailable in this browser.
The Query Doctor's Stethoscope
Imagine you're a doctor treating a patient with mysterious symptoms. You can observe external signs—fever, fatigue, elevated heart rate—but these symptoms alone don't reveal the underlying cause. To truly diagnose the problem, you need diagnostic imaging: X-rays, MRIs, CT scans that let you see inside the patient's body and understand what's actually happening.
Database query optimization follows the same principle. A slow query is a symptom. You can observe it taking seconds instead of milliseconds. But the symptom doesn't tell you why it's slow. Is it scanning millions of rows unnecessarily? Missing an index? Performing an expensive sort? Creating temporary tables in memory (or worse, on disk)? Executing a terrible join strategy?
The EXPLAIN statement is your diagnostic imaging tool—your X-ray into the database engine's decision-making process. It reveals not what the query does semantically, but how the database engine plans to execute it physically.
By the end of this page, you will understand what EXPLAIN does at a fundamental level, why it's essential for query optimization, the basic syntax across major database systems, the difference between estimated and actual execution, and how to invoke EXPLAIN in various forms to get the diagnostic information you need.
What Is EXPLAIN?
Every time you execute a SQL query, the database engine doesn't just blindly execute your instructions. Instead, it goes through a sophisticated multi-step process:
- Parsing — Validate syntax and create an abstract syntax tree (AST)
- Semantic Analysis — Verify that tables, columns, and permissions exist
- Query Rewriting — Apply transformations (view expansion, subquery flattening)
- Optimization — Generate candidate execution plans and choose the best one
- Execution — Actually run the chosen plan against the data
The query optimizer in step 4 is where the magic (and sometimes the tragedy) happens. The optimizer considers potentially thousands of different ways to execute your query and selects what it believes is the most efficient approach based on statistics, indexes, and heuristics.
EXPLAIN intercepts this process after step 4 but before step 5. It says: "Show me what you would do, without actually doing it."
This is profoundly powerful because:
- It reveals the optimizer's decision-making without waiting for query completion
- It costs almost nothing in terms of time and resources
- It exposes inefficiencies before they impact production systems
EXPLAIN shows the plan the optimizer chose, not necessarily what would happen in real execution. The actual execution might differ due to caching, concurrent queries, or data changes. This is why some databases offer EXPLAIN ANALYZE (or equivalents) that actually execute the query and compare planned vs. actual behavior.
The Fundamental Mental Model:
Think of EXPLAIN as asking the database: "If I asked you to run this query, what steps would you take, and why do you think that approach is best?"
The response is an execution plan—a structured description of the physical operations the database would perform, in what order, using which indexes or access methods, and with what estimated costs.
This execution plan is your window into the optimizer's mind. Master reading it, and you gain the power to:
- Diagnose why specific queries are slow
- Verify that indexes are being used (or discover they're not)
- Identify opportunities for query restructuring
- Predict how queries will behave as data grows
- Make informed decisions about index creation and schema design
Basic Syntax Across Database Systems
While the concept of EXPLAIN is universal across relational databases, the exact syntax and output format varies between database management systems. Understanding these variations is crucial for working across different platforms.
The Core Pattern:
At its simplest, EXPLAIN is a prefix to any SELECT, INSERT, UPDATE, or DELETE statement. You place it before the statement you want to analyze, and instead of executing the query, the database returns the execution plan.
1234567891011121314151617
-- Basic EXPLAIN: Shows estimated execution planEXPLAIN SELECT * FROM employees WHERE department_id = 5; -- EXPLAIN ANALYZE: Actually executes and shows real timingsEXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 5; -- EXPLAIN with OPTIONS for detailed outputEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT * FROM employees WHERE department_id = 5; -- JSON format for programmatic analysisEXPLAIN (ANALYZE, FORMAT JSON)SELECT * FROM employees WHERE department_id = 5; -- VERBOSE adds output column informationEXPLAIN (ANALYZE, VERBOSE)SELECT * FROM employees WHERE department_id = 5;While the fundamental concepts transfer across databases, the specific syntax, available options, and output format vary significantly. Always consult your specific database's documentation for the complete set of EXPLAIN options and how to interpret the output format unique to that system.
Estimated vs. Actual Execution
A critical distinction in understanding EXPLAIN is the difference between estimated and actual execution plans. This distinction matters enormously for effective query diagnosis.
Estimated Execution Plan (EXPLAIN):
- Shows what the optimizer thinks will happen
- Based on table statistics, index information, and cost models
- Does NOT execute the query
- Very fast (milliseconds)
- Safe to run on production systems
- May be inaccurate if statistics are stale
Actual Execution Plan (EXPLAIN ANALYZE):
- Shows what actually happened during execution
- Runs the full query and measures real timings
- Compares estimated row counts vs. actual row counts
- Can be slow (runs the entire query)
- May impact production if query is resource-intensive
- Reveals discrepancies between optimizer assumptions and reality
- •Initial query development and debugging
- •Checking if indexes are being considered
- •Quick validation of query structure
- •Production systems where you can't risk performance impact
- •Understanding optimizer's interpretation of complex queries
- •Comparing multiple query approaches quickly
- •Diagnosing mysterious performance issues
- •Verifying optimizer row estimates are accurate
- •Measuring actual I/O and memory usage
- •Finding discrepancies between plan and reality
- •Performance testing in development/staging environments
- •After schema or statistics changes to validate improvements
Why the Difference Matters:
The optimizer makes decisions based on statistics—histograms about data distribution, row counts, index selectivity, etc. But statistics can be:
- Stale — Data has changed, but statistics haven't been updated
- Sampled — Statistics are based on samples, not full scans
- Inaccurate for skewed data — Histograms may not capture unusual distributions
- Missing for complex expressions — The optimizer may guess about function results
When statistics are wrong, the optimizer's plan may be locally optimal but globally terrible. It might estimate a filter returning 100 rows when it actually returns 100,000, choosing a nested loop join that becomes catastrophic.
EXPLAIN ANALYZE reveals these misests. It shows you: "The optimizer expected 100 rows here, but we got 100,000." That single insight often diagnoses an entire class of performance problems.
Unlike basic EXPLAIN, EXPLAIN ANALYZE runs the full query. For UPDATE or DELETE statements, this means changes are applied! Always wrap destructive statements in a transaction that you roll back: BEGIN; EXPLAIN ANALYZE DELETE FROM...; ROLLBACK;
Anatomy of EXPLAIN Output
Regardless of database system, EXPLAIN output contains common informational elements that describe the execution plan. Understanding these elements is essential before diving into specific plan operators.
Universal Elements Found in Execution Plans:
| Element | Description | Why It Matters |
|---|---|---|
| Operation/Operator | The type of action (Seq Scan, Index Scan, Hash Join, etc.) | Tells you HOW data is being accessed or processed |
| Object/Table | Which table or index is being accessed | Identifies which part of your schema is involved |
| Estimated Rows | How many rows the optimizer expects this step to produce | Gross overestimates or underestimates indicate statistics problems |
| Estimated Cost | A relative measure of resource consumption (not in real units) | Helps identify expensive operations within the plan |
| Actual Rows (with ANALYZE) | How many rows were actually processed | Compare to estimated to detect cardinality misestimates |
| Actual Time (with ANALYZE) | Real wall-clock time for this step | Identifies which specific operation consumed the time |
| Width/Bytes | Average size of each row returned | Large widths can indicate excessive columns or data types |
| Loops | How many times this operation was executed | Reveals nested loop behavior and iteration counts |
Understanding Plan Trees:
Execution plans are fundamentally tree structures. Each node in the tree represents an operation, and nodes have parent-child relationships:
- Child nodes execute first and produce rows
- Parent nodes consume rows from their children
- Leaf nodes are data access operations (scans, index lookups)
- Root node is the final operation that produces the query result
When reading a plan, you typically read from the innermost (deepest) operation outward, or from bottom to top in traditional text representations. The innermost operations run first.
1234567891011121314151617181920
QUERY PLAN ---------------------------------------------------------------------------------------------- Hash Join (cost=3.25..8.62 rows=100 width=40) (actual time=0.045..0.089 rows=97 loops=1) Hash Cond: (e.department_id = d.id) -> Seq Scan on employees e (cost=0.00..4.00 rows=200 width=32) (actual time=0.008..0.025 rows=200 loops=1) -> Hash (cost=2.00..2.00 rows=100 width=12) (actual time=0.024..0.024 rows=10 loops=1) -> Seq Scan on departments d (cost=0.00..2.00 rows=100 width=12) (actual time=0.004..0.010 rows=10 loops=1) Planning Time: 0.215 ms Execution Time: 0.127 ms -- Reading this plan:-- 1. First, "Seq Scan on departments d" runs (innermost, bottom operation)-- 2. Results are stored in a "Hash" structure-- 3. Simultaneously, "Seq Scan on employees e" runs-- 4. Finally, "Hash Join" combines the results-- 5. Notice: estimated rows for departments was 100, actual was 10 (statistics issue!)Cost values in execution plans are in arbitrary units relative to sequential page reads—they're NOT milliseconds or seconds. Use costs to compare relative expense of different operations within a plan or between alternative plans, not as absolute performance predictions.
Common EXPLAIN Options and Formats
Modern database systems offer numerous options to customize EXPLAIN output. Understanding these options helps you extract precisely the information you need for different diagnostic scenarios.
PostgreSQL EXPLAIN Options (most comprehensive):
| Option | Description | Use Case |
|---|---|---|
ANALYZE | Actually execute and show real timings | When you need to measure actual performance |
BUFFERS | Show buffer usage (shared/local hits, reads, writes) | Diagnosing I/O patterns and cache effectiveness |
COSTS | Show cost estimates (on by default) | Basic cost comparisons between plans |
TIMING | Show actual time per node (with ANALYZE) | Identifying which specific operation is slow |
VERBOSE | Show output column list for each node | Understanding exactly what data flows through the plan |
FORMAT TEXT/JSON/XML/YAML | Output format selection | TEXT for human reading, JSON/XML for tooling |
SETTINGS | Show modified configuration parameters | Debugging when non-default settings affect plans |
WAL (pg13+) | Show WAL (write-ahead log) usage | Understanding write-heavy query impact |
12345678910111213141516
-- Full diagnostic output for performance debuggingEXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)SELECT e.name, d.department_name, SUM(s.amount)FROM employees eJOIN departments d ON e.department_id = d.idJOIN sales s ON e.id = s.employee_idWHERE s.sale_date >= '2024-01-01'GROUP BY e.name, d.department_name; -- JSON format for programmatic analysis or visualization toolsEXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)SELECT * FROM large_table WHERE indexed_column = 'value'; -- Cost-only mode (no execution) for quick plan comparisonEXPLAIN (COSTS ON)SELECT * FROM orders WHERE status = 'pending';Don't use every option at once. Start with basic EXPLAIN to understand the plan structure, then add ANALYZE for actual timings, then BUFFERS if I/O seems suspect. Layer diagnostic information as needed to avoid overwhelming output.
Why EXPLAIN Is Non-Negotiable
Some developers treat EXPLAIN as an advanced tool for DBAs or experts. This is a mistake. EXPLAIN is as fundamental to database development as a debugger is to application development. Here's why:
1. You Cannot Optimize What You Cannot Measure
Tuning queries blindly—adding indexes randomly, restructuring WHERE clauses by gut feeling—is gambling. Sometimes you luck into improvements; often you make things worse or change nothing. EXPLAIN transforms optimization from guesswork into engineering.
2. Queries Behave Differently at Scale
A query that returns instantly on 1,000 rows may take minutes on 10,000,000 rows. The execution plan often changes as data volumes grow—different join strategies become optimal, different indexes become useful. EXPLAIN lets you see how the optimizer adapts (or fails to adapt) to scale.
3. Statistics Lie, But EXPLAIN Reveals It
Database statistics are estimates. When they're wrong, the optimizer makes bad decisions. EXPLAIN ANALYZE shows you the gap between expected and actual row counts—often the smoking gun for mysterious performance problems.
- •Missing index usage — You created an index but it's not being used due to function calls, type mismatches, or selectivity estimates
- •Suboptimal join order — The optimizer chose to join tables in an inefficient sequence
- •Full table scans — Reading entire tables when index access would suffice
- •Nested loop disasters — Quadratic behavior from nested loops on large inputs
- •Sort and hash spills — Operations running out of memory and hitting disk
- •Cardinality misestimates — Expected 100 rows, got 1,000,000 (optimizer's assumptions are wrong)
- •Implicit type conversions — Column comparisons requiring type casts that disable indexes
Running EXPLAIN takes roughly 10 seconds of your time. It can save hours of debugging and prevent production incidents. Make it a habit: before any query goes to production, EXPLAIN it. This single practice separates informed engineers from hopeful ones.
Common Mistakes When Using EXPLAIN
Even experienced developers make mistakes when using EXPLAIN. Avoid these common pitfalls:
- •Testing with unrepresentative data — Running EXPLAIN on a development database with 1,000 rows when production has 100 million. The optimizer chooses completely different plans based on data volume.
- •Ignoring stale statistics — Plans change dramatically when table statistics are updated. Always ensure statistics are current before concluding anything from EXPLAIN output.
- •Focusing only on cost numbers — Cost is relative and abstract. A plan with higher cost isn't necessarily slower; it depends on I/O patterns, parallelism, and hardware. Always verify with ANALYZE.
- •Forgetting parameter substitution — Prepared statements with bound parameters may have different plans than literal values. EXPLAIN the actual parameterized query the application sends.
- •Testing in isolation — Query performance in production may differ due to buffer cache state, concurrent workload, and lock contention. A plan that looks fast in isolation may struggle under load.
- •Reading top-down instead of bottom-up — Execution plans are trees; innermost (bottom) operations execute first. Always trace execution from leaf nodes upward.
- •Ignoring 'loops' in output — An operation that runs fast but loops 100,000 times becomes the bottleneck. Always multiply time by loop count for true cost.
The same query can have radically different plans in development vs. staging vs. production due to different data volumes, statistics freshness, configuration parameters, and available memory. When diagnosing production issues, try to replicate production conditions as closely as possible.
KEEP LEARNING
There’s more to understand.
You’re reading the introduction. Create a free account to read the complete lesson, save notes and keep track of your progress.