Missing trigram index for ILIKE search

Missing trigram index for ILIKE search
Type: incident simulation · Topic: Query Performance · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-01/06-missing-trigram-index

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: the search filters email with a leading wildcard (ILIKE
'%rare.io%'). A B-tree index can only satisfy left-anchored patterns, so it's
useless here — the planner does a full Seq Scan of customers on every search.

How it was found: EXPLAIN showed a Seq Scan with a Filter on the ILIKE pattern;
the slow query stood out in pg_stat_statements.

The fix:
  CREATE EXTENSION IF NOT EXISTS pg_trgm;
  CREATE INDEX idx_customers_email_trgm
  ON customers USING gin (email gin_trgm_ops);
pg_trgm breaks text into 3-character trigrams and a GIN index over them lets
Postgres satisfy substring/ILIKE searches with a Bitmap Index Scan.

Lesson: leading-wildcard text search needs a trigram (pg_trgm) GIN/GiST index,
not a plain B-tree. Creating a B-tree on the column looks like a fix but the
planner will keep doing a Seq Scan.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. A leading-wildcard search like email ILIKE '%rare.io%' can't use a normal B-tree index — B-trees only help when the pattern is anchored at the start. EXPLAIN shows a Seq Scan.
2. PostgreSQL can index substring/ILIKE searches with trigrams. Enable the extension: CREATE EXTENSION pg_trgm.
3. Create a GIN trigram index: CREATE INDEX ... ON customers USING gin (email gin_trgm_ops). Then EXPLAIN shows a Bitmap Index Scan instead of a Seq Scan.