How do database indexes work and when can they hurt?

A B-tree keeps keys sorted, so the database can seek to a range in logarithmic time instead of scanning every row. The query planner chooses an index based on statistics and estimated cost. An index only helps when the query predicate matches its columns and ordering.

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

EXPLAIN ANALYZE
SELECT id, total_cents
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Rules and gotchas

• A composite index follows the leftmost-prefix rule
• Ordering matters: an index on user_id, created_at serves queries filtering by user_id and sorting by created_at
• A covering index includes all selected columns and can avoid a table lookup
• Low-selectivity columns, such as a boolean, are often poor single-column indexes
• Wrapping a column in a function can prevent index use unless a matching expression index exists
• Every index adds write cost, storage and vacuum or maintenance work

Index the access patterns you actually run, then verify with EXPLAIN and real data. A query that looks indexed can still scan because types differ, statistics are stale or the planner estimates a sequential scan as cheaper. Adding an index to every column is a common overcorrection.