📊 Execution Plans = Query Optimization
Slow queries need optimization. Execution plans show you exactly how your query runs.
📝 Reading Execution Plans
# Get Execution Plan EXPLAIN ANALYZE SELECT u.name, o.total, p.name FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id WHERE u.active = 1 AND o.order_date > '2024-01-01' ORDER BY o.total DESC; # PostgreSQL Plan EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE user_id = 123; # MySQL Plan EXPLAIN SELECT * FROM orders WHERE user_id = 123; # SQL Server Plan SET STATISTICS PROFILE ON; SET STATISTICS IO ON; SET STATISTICS TIME ON; # Common Operations - Seq Scan: Table scan (slow) - Index Scan: Using index (fast) - Index Only Scan: Data from index only (fastest) - Nested Loop: For small datasets - Hash Join: For medium datasets - Merge Join: For large sorted datasets
🎯 Optimization Strategies
# Find Slow Queries
SELECT
query,
total_time,
calls,
mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
# Missing Indexes
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000;
# Query Statistics
SELECT
query,
calls,
total_time,
mean_time,
rows
FROM pg_stat_statements
WHERE query LIKE '%users%'
ORDER BY mean_time DESC;
# Buffer Usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table WHERE id = 123;
# Analyze Tables
ANALYZE users;
ANALYZE orders;
ANALYZE products;
💡 Query Optimization Tips
- Use EXPLAIN ANALYZE regularly
- Monitor slow query logs
- Create appropriate indexes
- Update statistics regularly
- Consider query rewriting
Execution plans are the key to query optimization. They reveal performance bottlenecks and guide improvements.
