Query Optimization & EXPLAIN Plans
How to find out why a query is actually slow, instead of guessing from how the SQL looks.
Overview
EXPLAIN (and EXPLAIN ANALYZE) shows a query planner's actual execution plan for a query — the real, authoritative way to find out why a query is slow, instead of guessing based on how the SQL text looks.
Why It Exists
A query planner makes cost-based decisions — which index to use, which join algorithm, which order to join tables — that aren't visible from the SQL text alone. Two queries that look nearly identical can end up with wildly different plans depending on data distribution and statistics. EXPLAIN exists to make that invisible decision-making visible, so a developer can diagnose the actual bottleneck instead of guessing based on what the query happens to look like.
Real World Example
A query that 'should' be fast because it filters on an indexed column is actually slow. Running EXPLAIN ANALYZE reveals the planner chose a sequential scan instead of the index — perhaps because the column's statistics are stale, or a function wraps the column in a way that prevents index use. Seeing the actual plan is what turns 'this query is slow' into a specific, fixable reason why.
Example Data
The same query, two different plans the planner might choose
| Plan | Estimated Cost | Actual Rows | Time |
|---|---|---|---|
| Seq Scan on orders | 12,450 | 1,000,000 | 820 ms |
| Index Scan using orders_customer_id_idx | 8 | 3 | 0.04 ms |
How to Actually Read a Plan
Plans Execute Inside-Out
A query plan is a tree of nodes. Execution actually starts at the innermost (deepest) nodes and works outward — the outermost node shown is the last thing that runs, even though it's listed first.
Seq Scan vs Index Scan
A Seq(uential) Scan reads every row in a table. An Index Scan uses an index to jump directly to matching rows. Neither is automatically better — for a small table or a query returning most of its rows, a sequential scan is often genuinely the fastest option.
Estimated vs Actual Rows (the ANALYZE difference)
Plain EXPLAIN shows the planner's estimated cost and row counts, based on stored statistics — it doesn't run the query. EXPLAIN ANALYZE actually executes it and shows real timing and real row counts alongside the estimates, letting you compare the two directly.
Common Plan Nodes to Recognize
Nested Loop (good for small inputs, checks one side against the other repeatedly), Hash Join (builds a hash table from one side, good for larger unsorted inputs), and Sort (often appears before a merge join or an ORDER BY) are the nodes you'll see most often — recognizing them speeds up reading any plan.
Diagram
EXPLAIN shows the plan the planner picked; EXPLAIN ANALYZE actually runs it too
Query submitted
Planner considers multiple possible plans
based on stored statistics
Picks the plan with the lowest estimated cost
EXPLAIN
shows the chosen plan, estimates only
EXPLAIN ANALYZE
actually runs it — shows real timing and rows
Common Mistakes
Reading plain EXPLAIN and trusting the row estimates as if they were real numbers
Why: Plain EXPLAIN shows the planner's estimated cost and rows based on stored statistics, not what actually happened — if those statistics are stale, the estimates can be significantly wrong.
Fix: Use EXPLAIN ANALYZE when you need real numbers, keeping in mind it actually executes the query — be careful running it on a statement with side effects.
Treating any 'Seq Scan' in a plan as automatically a problem
Why: For a small table, or a query that legitimately needs most of a table's rows, a sequential scan is often genuinely the fastest available plan — seeing one isn't automatic evidence of a missing index.
Fix: Judge a plan by its actual cost and time relative to the table size and query, not by the presence of a specific node type.
Not comparing estimated vs actual row counts in an ANALYZE output
Why: A large gap between a plan node's estimated and actual row count is one of the most reliable signs that the planner's statistics are stale, which can cause it to pick a genuinely bad plan.
Fix: Run ANALYZE (the statistics-collection command) periodically, or rely on autoanalyze, and treat a big estimate/actual gap as a signal to investigate stats freshness.
Interview Questions
What's the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the planner's chosen execution plan with estimated cost and row counts, without running the query. EXPLAIN ANALYZE actually executes the query and shows real timing and real row counts alongside those estimates.
You see a Sequential Scan in a query's plan. Is that automatically a problem?
Not automatically. For a small table, or a query that needs to return most of a table's rows, a sequential scan can genuinely be the fastest plan available — the planner may correctly determine that an index scan would cost more due to the overhead of jumping between many scattered pages. The presence of a Seq Scan is only worth investigating in the context of the table's actual size and the query's expected selectivity.
EXPLAIN ANALYZE shows a massive gap between estimated and actual row counts for one plan node. What does that suggest, and what would you do?
It suggests the planner's statistics for the underlying table(s) are stale or don't reflect the real data distribution, which can cause it to choose a suboptimal plan elsewhere in the query too — a bad row estimate at one node can cascade into bad decisions at the nodes above it. I'd run ANALYZE on the affected tables to refresh statistics, check whether autoanalyze/autovacuum is keeping up with the actual write rate, and re-check the plan afterward.
Production Best Practices
Do
✓Use EXPLAIN ANALYZE when you need real timing and row counts, not just estimates.
✓Judge scan types by actual cost relative to table size, not by name alone.
✓Watch for large estimated-vs-actual gaps as a signal to refresh statistics.
Don't
✗Don't trust plain EXPLAIN's row estimates as ground truth without verifying with ANALYZE.
✗Don't assume a Seq Scan is always the wrong choice.
✗Don't ignore stale statistics as a root cause of a bad plan.
Comparison
| Executes the Query? | Shows Real Timing? | Risk | |
|---|---|---|---|
| EXPLAIN | No | No — estimates only | None |
| EXPLAIN ANALYZE | Yes | Yes | Actually runs the statement — caution with side effects |