Understanding the query execution plan (via EXPLAIN) is key to becoming a query performance master. Let’s break it down step by step.
🧠 What Is a Query Execution Plan?
A query execution plan is a step-by-step blueprint of how the database will execute (or has executed) your SQL query.
You can ask the database to show you its plan using the EXPLAIN command.
🔍 Why Use EXPLAIN?
- To understand how your query behaves
- To find performance problems
- To see if indexes are used
- To check row estimates vs actual results
✅ Basic Example
EXPLAIN SELECT * FROM users WHERE email = 'stanley@example.com';
This will show:
- Whether the query does a sequential scan (bad for big tables)
- Or an index scan (faster!)
- How many rows are expected
- The cost of the operation (a unitless estimate)
🧠 Key Terms You’ll See in EXPLAIN
| Term | Meaning |
|---|---|
| Seq Scan | Full table scan (slow for large tables) |
| Index Scan | Uses an index to locate matching rows |
| Index Only Scan | Uses only index — doesn’t touch table (very fast) |
| Nested Loop | Joins one row at a time (fast for small sets) |
| Hash Join | Joins using a hash table (good for large joins) |
| Cost | Estimated cost to run the step (planner’s metric) |
| Rows | Estimated number of rows at that step |
| Width | Average size of each row in bytes |
✅ More Powerful: EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'stanley@example.com';
- Executes the query
- Shows actual time taken
- Shows actual row counts
- Helps you see misestimates or performance issues
🧠 Example Output (Simplified)
Seq Scan on users (cost=0.00..35.50 rows=1 width=48)
Filter: (email = 'stanley@example.com')
🟥 This means:
- The database is scanning the whole table to find the email — it’s not using an index, which is slow for big tables.
✅ Better:
Index Scan using idx_users_email on users
Index Cond: (email = 'stanley@example.com')
This means:
- The database used your index — much faster!
💡 When to Use EXPLAIN
| Situation | Use EXPLAIN? |
|---|---|
| Query is slow | ✅ Yes |
| You want to tune a query | ✅ Yes |
| Adding or testing an index | ✅ Yes |
| Optimizing joins | ✅ Yes |