EXPLAIN Statement - Understanding EXPLAIN Plans | OneNoughtOne

0/278

Database Management SystemsEXPLAIN Plans

Understanding EXPLAIN Plans

LevelIntermediate

Duration60 mins

TopicEXPLAIN Plans

1 / 5

EXPLAIN Statement

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.

What You Will Learn

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 vs. Execution

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.

PostgreSQL EXPLAIN Syntax
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Basic EXPLAIN: Shows estimated execution plan
EXPLAIN SELECT * FROM employees WHERE department_id = 5;
 
-- EXPLAIN ANALYZE: Actually executes and shows real timings
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 5;
 
-- EXPLAIN with OPTIONS for detailed output
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM employees WHERE department_id = 5;
 
-- JSON format for programmatic analysis
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM employees WHERE department_id = 5;
 
-- VERBOSE adds output column information
EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM employees WHERE department_id = 5;

Vendor Documentation Is Essential

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

When to Use Estimated

•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

When to Use Actual

•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.

ANALYZE Actually Executes The Query

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:

Core Elements of 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.

Sample PostgreSQL EXPLAIN ANALYZE Output

PostgreSQL

                                          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 Units Are Abstract

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):

PostgreSQL EXPLAIN Options
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

EXPLAIN with Various Options
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Full diagnostic output for performance debugging
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT e.name, d.department_name, SUM(s.amount)
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN sales s ON e.id = s.employee_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY e.name, d.department_name;
 
-- JSON format for programmatic analysis or visualization tools
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM large_table WHERE indexed_column = 'value';
 
-- Cost-only mode (no execution) for quick plan comparison
EXPLAIN (COSTS ON)
SELECT * FROM orders WHERE status = 'pending';

Start Simple, Then Add Detail

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.

Problems EXPLAIN Surfaces Immediately

•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

The 10-Second Investment

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:

EXPLAIN Anti-Patterns

•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.

Environment Matters

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.

Summary: The EXPLAIN Statement

We've established a comprehensive foundation for understanding the EXPLAIN statement. Let's consolidate the key insights:

Key Takeaways

•EXPLAIN is your diagnostic window — It reveals how the query optimizer plans to execute your query before actually running it.
•Estimated vs. Actual matters — Basic EXPLAIN shows predictions; EXPLAIN ANALYZE shows reality. Use both strategically.
•Syntax varies but concepts are universal — PostgreSQL, MySQL, SQL Server, and Oracle all have EXPLAIN capabilities with different syntax and output formats.
•Plans are trees, not sequences — Read from innermost operations outward; children execute before parents.
•Cost is relative, not absolute — Use cost for comparing operations within a plan, not predicting wall-clock time.
•Environment affects plans — Data volume, statistics freshness, and configuration all influence optimizer choices.

What's Next:

Now that you understand what EXPLAIN does and how to invoke it, the next page dives into reading execution plans—how to interpret the tree structure, understand the flow of data, and identify where queries spend their time. This is where you develop the diagnostic intuition that makes EXPLAIN truly powerful.

Page Complete

You now understand the fundamental purpose and mechanics of the EXPLAIN statement. You know why it matters, how to invoke it across different database systems, and the critical distinction between estimated and actual execution. Next, we'll learn to read and interpret the execution plans EXPLAIN produces.