📘 EXPLAIN — “Tell me the plan”
EXPLAIN shows the estimated execution plan without actually running the query.
🧠 What it gives you:
- Planned steps: scans, joins, filters
- Estimated cost, rows, and row widths
- Which indexes (if any) it plans to use
✅ Pros:
- Safe: doesn’t run the query
- Fast and risk-free
- Good for previewing what the DB thinks it will do
❌ Cons:
- Might not reflect reality — it’s just a guess
- Estimates could be wrong (e.g., outdated statistics)
🔬 EXPLAIN ANALYZE — “Run it and show what actually happened”
EXPLAIN ANALYZE executes the query and shows the actual plan used, along with real timings and row counts.
🧠 What it gives you:
- Everything
EXPLAIN gives - ✅ Actual execution time
- ✅ Actual row counts
- ✅ Timing for each operation
- ✅ Can highlight performance issues or bad estimates
✅ Pros:
- Shows what really happened
- Helps find mismatches between estimated vs actual
- Critical for diagnosing slow queries
❌ Cons:
- Runs the query (may affect data or take time)
- Be careful with
UPDATE/DELETE — it executes them!
📊 Side-by-Side Comparison
| Feature | EXPLAIN | EXPLAIN ANALYZE |
|---|
| Executes the query | ❌ No | ✅ Yes |
| Shows actual time | ❌ No | ✅ Yes |
| Shows actual row count | ❌ No | ✅ Yes |
| Risk of side effects | ❌ None | ⚠️ Yes (it runs the query!) |
| Use case | Preview the plan | Analyze real performance |
