How to Check Query Speed
Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows | Few (100) | Many (100,000) |
| Cost | Low (< 100) | High (> 1000) |
3 rows
Use EXPLAIN to see how database runs your query.
EXPLAIN SELECT * FROM students WHERE age > 20;What EXPLAIN Shows
Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows | Few (100) | Many (100,000) |
| Cost | Low (< 100) | High (> 1000) |
3 rows
Reading EXPLAIN Output
Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows | Few (100) | Many (100,000) |
| Cost | Low (< 100) | High (> 1000) |
3 rows
Good query:
Index Scan on students
Rows: 50
Cost: 8.29
Bad query:
Seq Scan on students
Rows: 100000
Cost: 1520.00
Fix Slow Queries
Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows | Few (100) | Many (100,000) |
| Cost | Low (< 100) | High (> 1000) |
3 rows
- Add index on columns in WHERE
- Reduce data with more filters
- Use selective projection
Summary
Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows | Few (100) | Many (100,000) |
| Cost | Low (< 100) | High (> 1000) |
3 rows
- EXPLAIN shows query execution plan
- Look for Index Scan (good) vs Seq Scan (bad)
- Low rows and cost = fast query