Add a Database Index That Actually Helps

Performance Engineering · Part 3

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 = ~25% of table size, varies

• 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.