Introduction
An execution plan (or query plan) is the step-by-step strategy the database's optimizer chooses to run a given SQL statement — which tables to access first, whether to use an index or scan sequentially, which join algorithm to use, and in what order to join tables. The EXPLAIN command reveals this plan without necessarily running the query, letting you see how the engine intends to satisfy a statement and, in most systems, the estimated cost and row counts for each step.
Cricket analogy: An execution plan is like a captain's pre-match strategy sheet deciding bowling order, field placements, and when to use DRS; EXPLAIN is like reading the team's game plan before the toss to see the intended approach and its projected outcome.
Syntax
-- Show the estimated plan without executing
EXPLAIN SELECT * FROM orders WHERE customer_id = 4821;
-- Actually run the query and show real timing/row counts alongside the plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4821;Explanation
Plans are typically shown as a tree of operations (nodes), read from the innermost/bottom node outward, since inner nodes feed rows to the operations above them. Two common leaf-level access methods are a sequential scan (Seq Scan), which reads every row in a table and is efficient when a large fraction of rows match or the table is small, and an index scan, which uses an index's B-tree to jump directly to matching rows and is efficient when few rows match. Each node typically reports an estimated cost (a unitless number reflecting relative I/O and CPU effort, not actual seconds) and an estimated row count, both derived from table statistics the optimizer maintains. Join nodes indicate the algorithm chosen, such as nested loop (good for small outer inputs), hash join (good for large unsorted inputs), or merge join (good when both inputs are already sorted on the join key).
Cricket analogy: Reading a plan bottom-up is like reading a scorecard from the first ball onward; a 'seq scan' checking every ball for a boundary is fine on a short innings, while an 'index scan' jumping straight to a specific over via a bookmarked index suits a long Test match; joining batting and bowling stats uses a nested loop for a small XI, a hash join for a full tournament, or a merge join if both lists are already sorted by player.
Example
EXPLAIN SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';
-- Example conceptual plan output:
-- Hash Join (cost=45.00..210.50 rows=1200 width=48)
-- Hash Cond: (o.customer_id = c.customer_id)
-- -> Seq Scan on orders o (cost=0.00..150.00 rows=1200 width=24)
-- Filter: (status = 'shipped')
-- -> Hash (cost=30.00..30.00 rows=2000 width=28)
-- -> Seq Scan on customers c (cost=0.00..30.00 rows=2000 width=28)Analysis
Reading this plan from the bottom up: the engine performs a sequential scan on customers (no filter, so all rows are needed) and builds an in-memory hash table from it; it separately does a sequential scan on orders, applying the status = 'shipped' filter as it reads; then it joins the two using a hash join, probing the hash table with each qualifying order row. The Seq Scan on orders is a warning sign — if orders is large and few rows have status = 'shipped', an index on (status) or a composite index on (status, customer_id) would let the optimizer switch to an index scan, likely lowering the estimated cost and making the query faster. This is exactly the kind of insight execution plans are meant to surface: they turn 'the query is slow' into a specific, actionable diagnosis.
Cricket analogy: Reading bottom-up: the engine scans every player once (no filter needed) building a lookup table, then scans every ball bowled filtering for 'six', then joins the two; scanning every ball is a warning sign on a huge archive, and an index on shot_type would speed it up, turning 'the query is slow' into 'add this index.'
Key Takeaways
- EXPLAIN shows the optimizer's chosen plan; EXPLAIN ANALYZE actually runs the query and adds real timing and row counts.
- Plans are trees read from the innermost/bottom node outward, since inner results feed outer operations.
- A sequential scan reads every row; an index scan jumps to matching rows via an index — the optimizer picks based on estimated selectivity.
- Cost values are relative estimates for comparing plans, not actual elapsed seconds.
- A sequential scan on a large table with a highly selective filter is a common signal that a missing index may help.
Practice what you learned
1. What is the key difference between EXPLAIN and EXPLAIN ANALYZE?
2. In an execution plan tree, in what order should the nodes generally be read?
3. When would the optimizer typically prefer a sequential scan over an index scan?
4. What does the 'cost' value shown in an execution plan represent?
Was this page helpful?
You May Also Like
Indexes and How They Work
How database indexes use B-tree structures to speed up lookups, and why they add overhead to writes.
Query Optimization Basics
Foundational techniques for writing queries the optimizer can execute efficiently.
Indexing Strategies and Tradeoffs
How to choose composite index column order, use covering indexes, and know when not to index.