Add a Database Index That Actually Helps
Updated: 4 days ago
Performance Engineering · Part 3
Corrected, 4 October 2026. Replaced an unsourced estimate of index size with how to measure it.
A missing database index is the difference between a query that scans a million rows and one that jumps straight to the ten it needs — often a 100x speedup from a single line. But indexes aren't free, and the wrong one does nothing. This walks through adding an index that actually gets used: reading the query plan, indexing the right column, and knowing what an index costs.
Indexes turn 5-second queries into 5-millisecond queries. But only when used right.
Step 1: Find the Slow Query (5 min)
From profiling (Part 1) or DB monitoring:
-- Postgres: enable slow query log
ALTER SYSTEM SET log_min_duration_statement = 1000; -- log queries > 1 sec
Or pg_stat_statements for aggregate stats:
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Top queries by average time.
Step 2: EXPLAIN ANALYZE (10 min)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;
Look for:
Seq Scan = sequential scan; reads every row. Bad on big tables.
Index Scan = uses index. Good.
Bitmap Heap Scan = uses index for many rows. Good.
Times in milliseconds at each step.
Step 3: Identify the Filter Column (5 min)
The WHERE clause column = candidate for index.
WHERE user_id = 42 → index on user_id
WHERE email = 'foo@x.com' → index on email
WHERE status = 'pending' → maybe (depends on cardinality)
Step 4: Add the Index (3 min)
CREATE INDEX idx_orders_user_id ON orders(user_id);
In Postgres, this is online (doesn't lock the table for reads). For very large tables:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
Doesn't block writes either.
Step 5: Verify Index Use (5 min)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;
Look for Index Scan using idx_orders_user_id. Query time should drop dramatically.
If still seq scan: Postgres decided index isn't faster for the query. Could mean:
Small table
Most rows match (low selectivity)
Stale statistics (ANALYZE orders;)
Step 6: Composite Indexes (10 min)
For multi-column WHERE:
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
Order matters. This index helps:
WHERE user_id = 42 ✓
WHERE user_id = 42 AND status = 'pending' ✓
WHERE status = 'pending' ✗ (without user_id first)
Put the more-selective column first.
Step 7: Partial Indexes (10 min)
Index only the rows you query:
CREATE INDEX idx_orders_pending
ON orders(user_id)
WHERE status = 'pending';
Smaller index. Faster. Useful when WHERE always filters by the same condition.
Step 8: Indexes for ORDER BY (5 min)
SELECT * FROM events ORDER BY created_at DESC LIMIT 100;
CREATE INDEX idx_events_created ON events(created_at DESC);
Index used for sorting; no explicit sort step needed.
Step 9: Functional Indexes (5 min)
Index expressions:
-- Case-insensitive lookup
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'foo@example.com';
Without the functional index, the LOWER() prevents normal index use.
Step 10: Costs of Indexes (5 min)
Indexes aren't free:
Storage: each index takes its own disk space, and it grows with the table and the columns it covers. Measure it rather than guessing: in PostgreSQL, pg_relation_size on the index
Write cost: every INSERT/UPDATE/DELETE updates all indexes
Maintenance: vacuum, bloat over time
Don't add indexes "just in case." Add for specific queries. Drop unused ones:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
Indexes never scanned = dead weight.
What You Just Did
You can find slow queries, add the right index, verify it's used, and balance the trade-offs.
Common Failure Modes
Index on every column. Storage explosion; slow writes.
Wrong column order in composite. Doesn't get used.
Functions on columns. Index ignored; needs functional index.
Forgetting ANALYZE. Postgres stats stale; uses wrong plan.
Index on low-cardinality column. All rows match; index doesn't help.
Continue the Performance Engineering path
Previous — Part 2: Find N+1 Queries
Next — Part 4: Cache Hot Reads
Part of the Performance Engineering learning path.
