The Index That Didn't Help
A production incident, replayed by hand: customer search by email fragment crawls, and the index someone added did nothing. Bring up the same database, catch the query in pg_stat_statements, watch a B-tree get ignored by a leading-wildcard ILIKE, and fix it for real with a trigram GIN index.
Support searches customers by a piece of their email — and every search crawls, getting slower as the table grows. Someone already "fixed" it by adding an index on the email column; nothing changed. This book replays the incident on a database you bring up yourself: schema, data and broken state are plain SQL files, nothing is hidden. You reproduce the pain with a stopwatch, catch the query in pg_stat_statements, read the plan, and then walk into the same trap production did — a plain B-tree that the planner refuses to touch. Understanding WHY it refuses (a leading wildcard defeats sorted order) leads to the real fix: the pg_trgm extension and a GIN index over trigrams, proven by watching the plan flip from a Seq Scan to a Bitmap Index Scan.
What you'll build
- Reproduce a slow-search incident on a local PostgreSQL stand
- Find the offending query with pg_stat_statements
- Understand why a leading-wildcard ILIKE cannot use a B-tree index
- Enable pg_trgm and build a GIN trigram index for substring search
- Verify the fix by watching the plan switch to a Bitmap Index Scan
Contents
- The stand
- Bring it up
- The schema
- Apply the schema
- The data
- Load it
- A clean slate for statistics
- Reproduce the pain
- Ask the database what hurts
- Read the plan
- The obvious fix
- Take it back out
- Design the real fix
- Apply it
- Watch the plan flip
- Close the ticket