Database Optimization

EXPLAIN Analysis

Always run EXPLAIN ANALYZE before optimizing. Read the output bottom-up.

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;

Key metrics to watch:

• Seq Scan on large tables = missing index
• Nested Loop with high row count = consider hash/merge join
• Sort without index = add index on sort column
• Rows estimated vs actual divergence = stale statistics, run ANALYZE