Always Read the Plan First
sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;
Two outcomes:
•Seq Scan on orders — reads EVERY row. Add an index.
•Index Scan using idx_orders_user_id — fast. Good.
Rule 1: Index Every Foreign Key
sql
-- You almost certainly run this:
SELECT * FROM orders WHERE user_id = $1;
-- Add this:
CREATE INDEX idx_orders_user_id ON orders(user_id);
Rule 2: Composite Index for WHERE + ORDER BY
sql
-- This query:
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;
-- Needs this (column order matters!):
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
Rule 3: Partial Indexes
sql
-- Only index active users:
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
-- Smaller, faster, only covers rows you actually query.
Rule 4: Find Your Slow Queries
sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls,
round(total_exec_time / calls) AS avg_ms
FROM pg_stat_statements
ORDER BY avg_ms DESC LIMIT 20;
-- Run EXPLAIN ANALYZE on the top results.
Rule 5: Delete Unused Indexes
sql
SELECT indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
-- These indexes slow writes but help nothing. Drop them.
DROP INDEX CONCURRENTLY idx_unused; -- CONCURRENTLY avoids table lock
Remember: Every index speeds up reads but slows INSERT/UPDATE/DELETE. Only index columns you actually filter or sort by.
