Database Optimization

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

Index Strategies

B-tree (default, most cases)

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

Use for: equality, range queries, sorting. Column order matters in composite indexes: put equality columns first, then range/sort columns.

Partial Index (PostgreSQL)

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

Use when queries always filter on a specific condition. Dramatically smaller than full indexes.

GIN (PostgreSQL - arrays, JSONB, full-text)

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));