What Does EXPLAIN ANALYZE Do?
Learn what EXPLAIN ANALYZE does, how it differs from plain EXPLAIN, and how to use it to diagnose slow SQL queries.
Expected Interview Answer
EXPLAIN ANALYZE actually runs the query and returns the real execution plan annotated with true measured timings and row counts for every operator, unlike plain EXPLAIN which only shows the optimizer's estimates without executing anything.
Because EXPLAIN ANALYZE executes the query for real, side effects from INSERT, UPDATE, or DELETE statements do happen, so engines wrap it in a transaction rollback when testing writes. The output shows, per operator, the estimated versus actual row counts and the actual time spent, which is exactly what reveals optimizer misestimates — for example a nested loop expected to touch 10 rows that actually touches 100,000 because statistics are stale. Comparing estimated to actual rows at each node is the single most useful diagnostic technique for fixing a slow query.
- Shows real timings, not just cost estimates
- Exposes estimate-vs-actual row mismatches caused by stale statistics
- Pinpoints exactly which operator in the tree consumes the most time
- Validates whether an index change actually helped, with real numbers
AI Mentor Explanation
Think of a pre-match team-sheet prediction of how many runs each batter will score, versus the actual scorecard after the innings is played. Plain EXPLAIN is the prediction sheet; EXPLAIN ANALYZE is the innings actually being played out, with real runs, real dismissals, and real over-by-over timings recorded against each prediction. Comparing the two tells the analyst exactly where the pre-match plan was wrong, the same way comparing estimated to actual rows tells a developer where the optimizer misjudged the query.
Step-by-Step Explanation
Step 1
Run EXPLAIN ANALYZE on the query
The engine executes the query for real while instrumenting each operator.
Step 2
Read the operator tree with actual numbers
Each node shows estimated vs actual rows and actual elapsed time.
Step 3
Find the largest estimate-vs-actual gap
A large mismatch usually points to stale statistics or a missing index.
Step 4
Apply a fix and re-run
Add an index, update statistics, or rewrite the query, then re-run EXPLAIN ANALYZE to confirm improvement.
What Interviewer Expects
- Clear distinction from plain EXPLAIN: this one actually executes the query
- Awareness that write statements have real side effects, so care or a transaction wrap is needed
- Ability to explain estimate-vs-actual row comparison as the core diagnostic
- Mention of measured timing per operator, not just totals
Common Mistakes
- Confusing EXPLAIN ANALYZE with plain EXPLAIN, thinking neither executes the query
- Running EXPLAIN ANALYZE on a destructive write without wrapping it in a rollback
- Only looking at total time and ignoring per-operator breakdown
- Not connecting an actual-vs-estimated row mismatch to stale statistics
Best Answer (HR Friendly)
“EXPLAIN ANALYZE actually runs the query and shows me the real timings and real row counts at each step, not just the optimizer's guesses. I use it to compare what the optimizer expected against what actually happened, and that mismatch almost always points me straight to the fix, whether it's a missing index or outdated statistics.”
Code Example
-- Estimate only, does not execute the query
EXPLAIN
SELECT * FROM Orders WHERE status = 'pending';
-- Actually executes the query and reports real timings/rows
EXPLAIN ANALYZE
SELECT * FROM Orders WHERE status = 'pending';
-- Sample annotated output line:
-- Seq Scan on Orders (cost=0.00..1834.00 rows=50 width=40)
-- (actual time=0.02..12.41 rows=48210 loops=1)
-- Here estimated rows (50) vastly undercounted actual rows (48210),
-- signalling stale statistics or a missing index.Follow-up Questions
- Why can EXPLAIN ANALYZE be risky to run on a production write query?
- What causes the optimizer's row estimates to be wrong?
- How do you keep table statistics fresh in a busy database?
- What other EXPLAIN options add extra diagnostic detail, like buffer usage?
MCQ Practice
1. What is the key difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN ANALYZE runs the query for real and annotates the plan with actual measured rows and timing, unlike plain EXPLAIN.
2. Why should EXPLAIN ANALYZE be used cautiously on an UPDATE statement in production?
Because EXPLAIN ANALYZE truly executes the query, a write statement has real side effects unless wrapped in a rolled-back transaction.
3. A large gap between estimated and actual row counts for an operator most often signals?
Large estimate-vs-actual mismatches typically mean the optimizer's statistics are outdated, leading it to choose a suboptimal plan.
Flash Cards
What does EXPLAIN ANALYZE do? — Executes the query for real and reports the actual execution plan with measured timings and row counts.
EXPLAIN vs EXPLAIN ANALYZE? — EXPLAIN only estimates without running the query; EXPLAIN ANALYZE actually runs it.
What is the key diagnostic signal in EXPLAIN ANALYZE output? — A large gap between estimated and actual row counts at an operator.
Why be careful with EXPLAIN ANALYZE on writes? — It executes the statement for real, so INSERT/UPDATE/DELETE side effects actually happen.