An Index Is A Bet
Adding an index bets that its read savings outweigh its write and storage cost. Sometimes the bet loses. Knowing when not to index is as valuable as knowing when to.
Low Selectivity
Selectivity is the fraction of rows a filter eliminates. An index helps when a query returns a small slice of the table. If a column has few distinct values, like a boolean where half the rows match, an index seek that visits half the table plus extra fetches is often slower than a sequential scan. The planner may ignore such an index entirely.
Small Tables
For a table of a few hundred rows, a full scan is already a single page read or two. An index adds maintenance cost and complexity for no real read gain, since scanning everything is trivially cheap.
Write Heavy Or Rarely Queried
- On a write heavy path like a high volume log table, each index drags down ingest throughput, and the column may rarely be queried anyway.
- An index for a query that runs once a month rarely justifies taxing every write in between.
Redundant Indexes
A composite index on a b already serves queries on a, so a separate index on a alone is often redundant. Overlapping indexes multiply write cost without adding read coverage.
Key idea
Skip an index when selectivity is low, the table is tiny, the path is write heavy, the query is rare, or it duplicates an existing prefix, because in those cases the write and storage cost outweighs any read savings.