nurvexMost database performance advice starts and ends with "add an index," which is a little like telling someone to "get stronger" without mentioning which muscle. The strategies that actually move the needle are more specific than that.
Cardinality decides whether an index even helps
An index on a column with only three distinct values almost never helps a query planner — the planner will often ignore it and scan the table anyway, because filtering three-quarters of rows still leaves too much to bother. Indexes earn their keep on high-cardinality columns, and especially on the columns that show up in WHERE clauses together.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 4821 AND status = 'pending';
-- Before: Seq Scan on orders (cost=0.00..18420.00 rows=3)
-- After: Index Scan using orders_customer_status_idx (cost=0.42..8.60 rows=3)A composite index's column order matters — it's only useful for queries that filter on a matching prefix of those columns, left to right.
The index you added six months ago might be costing you now
Every index speeds up reads and slows down writes, and that tradeoff drifts as a table's read/write ratio changes. It's worth an annual pass through unused indexes — most production databases carry a handful nobody remembers adding, quietly taxing every insert.
The fastest win is usually deletion, not addition.