Adding an index feels like a universal speed button until the database ignores it. That is often correct: an index has a traversal cost, consumes memory and disk, and makes every write more expensive.
Short answer: start from the slow query and its EXPLAIN plan, then index the columns and order the query actually uses. A composite index helps from its leftmost columns onward; an index that does not match the filter or selectivity may be worse than a scan.
SELECT id, created_at FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;
CREATE INDEX orders_account_created_idx
ON orders (account_id, created_at DESC);
| Plan symptom | Likely next check |
|---|---|
| Sequential scan on a selective filter | Missing or unusable index |
| Huge rows examined | Predicate or join order is broad |
| Sort after filtering | Index may not support the ordering |
| Index scan still slow | Fetching too many rows or random I/O |
Do not index every column. Inserts, updates, deletes, vacuuming, and storage all pay for each index; unused indexes are permanent write tax.
Use EXPLAIN ANALYZE in a safe environment with representative data, measure the before and after plan, and keep only indexes tied to a known query pattern. Query plans end arguments faster than index folklore.
Cover photo by panumas nikhomkhai on Pexels.