9.8 When does an index not help, or even hurt?

In a book: An index entry for "the" would list every page: useless. An index can't help you find "every word ending in -ing", because it's sorted by how words start. And a tiny 4-page pamphlet doesn't need an index at all.

In the system: Common cases:

1. Low selectivity. An index on status with 3 values matches about a third of the table for each value. Fetching a third of the rows one by one through an index can be slower than one scan, so the planner often ignores it. Indexes shine when a condition matches a small fraction of rows.
2. Functions on the column. WHERE lower(email) = 'asha@example.com' can't use an index on email, because the index is sorted by email, not lower(email) (the Level 4 bug). Fix: an expression index on lower(email), or store emails already lowercased.
3. Leading wildcards. WHERE title LIKE '%systems%' can't use a B-tree, because it's sorted by how titles start. That's a job for a search index (Chapter 19).
4. Type mismatches. Comparing a text column to a number may force a conversion on every row and skip the index.
5. Small tables. A scan of a few hundred rows is already fast.
6. Write-heavy tables with indexes nobody uses. All cost, no benefit (9.7).
7. Wrong column order in a composite index (9.4).

Memory code: "Indexes help when you look up a few, by how the column starts."

---