Indexes are the cheapest performance work in a relational database, which is exactly why they get added in a hurry. A report runs slow, someone adds an index, the report speeds up, and the ticket closes. Six months later writes have crept up, the buffer cache is full of pages nobody reads, and the planner is choosing between four indexes that look almost identical.
The cure is rarely a bigger server or a cleverer query. It is usually a short, unglamorous audit of the indexes you already have: deleting the ones that never earn their keep, and rebuilding the few that matter so their shape matches the shape of your queries. Below are the seven mistakes that show up most often, roughly in the order they cost you money.
1. Indexing every column that appears in a WHERE clause
The reflex is understandable. A query filters on status, created_at, and customer_id, so you create three single-column indexes and assume the planner will stitch them together. Sometimes it does — PostgreSQL can combine them with a bitmap AND — but that is a repair, not a plan. It reads three structures, intersects row identifiers, and then still has to visit the heap.
A single composite index on (customer_id, created_at) frequently answers the same query with one range scan. Multiply the mistake across a dozen tables and you have thirty indexes where eight would do, all of them maintained on every insert.
2. Getting composite column order backwards
A composite index is not a set of independent indexes bolted together; it is a sorted list, and only a leftmost prefix can be searched efficiently. An index on (customer_id, created_at) supports a query filtering by customer_id alone, or by customer_id plus a range on created_at. An index on (created_at, customer_id) does not help a query that filters by customer_id alone.
The workable rule: put equality columns first, then range columns, then anything used for ordering. If a query says WHERE customer_id = 42 AND created_at > now() - interval '30 days' ORDER BY created_at DESC, the index (customer_id, created_at) serves the filter and the sort at once — no sort step, no temp file.
3. Hiding the column from the index
Functions and casts wrap a column in an expression the index cannot match. WHERE lower(email) = '[email protected]' cannot use a plain B-tree on email. WHERE date(created_at) = '2024-03-01' cannot use an index on created_at. An implicit cast — comparing a text column to an integer literal — can defeat the index just as thoroughly, and far more quietly.
The fixes are all unremarkable. Rewrite the predicate as a range: created_at >= '2024-03-01' AND created_at < '2024-03-02'. Or create an expression index that matches the exact expression, or store the derived value in a generated column and index that.
4. Duplicate and overlapping indexes
If you have indexes on (email), (email, tenant_id), and (email, tenant_id, created_at), the first two are dead weight — the third can serve every query they can. Migration scripts are the usual culprit: someone adds an index, someone else adds a "slightly better" one, and nobody removes the original.
Find them by querying the catalog for indexes with the same leading columns, and by checking usage counters such as pg_stat_user_indexes. One caution: an index with zero scans may still be load-bearing. Unique indexes enforce constraints, and some engines require an index to support a foreign key. Read before you drop.
5. Ignoring selectivity
An index on a column with three distinct values earns its keep only when the query targets a rare one. Index a boolean flag where 95% of rows are false, and the planner will correctly ignore your index and scan the table instead — you paid the write cost for nothing.
Partial and filtered indexes solve this neatly. If 99% of your queries only care about pending work, index only the pending rows: CREATE INDEX ... ON jobs (created_at) WHERE status = 'pending'. The index is a fraction of the size, stays hot in cache, and is updated only when a row enters or leaves that state.
6. Forgetting that every index taxes every write
Reads are not free, but writes are the bill that arrives later. Each insert must update every index on the table; each update must remove and reinsert entries. In PostgreSQL, indexing a column that changes frequently can also block the heap-only tuple optimization, turning cheap updates into full index maintenance.
An index is a promise to every write that you will keep it current. Make the query earn that promise.
Watch the write path as carefully as the read path. A table taking five thousand inserts a second with twenty indexes is doing a hundred thousand B-tree insertions a second, plus the WAL, plus the replication traffic, plus the bloat you will eventually have to rebuild away.
7. Assuming the index is being used
An index existing and an index being chosen are different facts. Run EXPLAIN ANALYZE, not just EXPLAIN, and compare estimated rows with actual rows. A tenfold misestimate is usually how a good index gets skipped: stale statistics, a skewed distribution, or a generic prepared-statement plan built for the wrong parameter.
Also be suspicious of "sometimes slow." Parameter-sensitive queries are the classic case — fast for the common value, disastrous for the rare one — and no single index fixes them. Sometimes the answer is a plan hint or a forced custom plan; often it is better statistics.
A Ten-Minute Index Audit
- List every index on your busiest five tables. Note the columns and their order.
- Cross off any index whose columns are a prefix of another index on the same table.
- Check usage statistics and flag indexes that have never been scanned — then confirm none of them back a constraint.
- Pull the top ten queries by total execution time and read their actual plans, with buffers.
- For each slow query, ask whether one composite or partial index could replace two or three existing ones.
What Good Indexing Looks Like
Good indexing is boring and small. A handful of composite indexes, ordered equality-then-range, each one traceable to a real query. Partial indexes where the data is lopsided. Expression indexes where the query insists on a function. Nothing left over from a migration three years ago.
Do the audit once and you will usually delete more indexes than you create — and the queries will still be fast, because the ones that remain finally match the work being asked of them.
Photo: kumar111aakashin / Pixabay
