Indexes I Forget to Create

Partial index WHERE active = true—80% smaller when most rows inactive; suggest for status columns
Expression index ON lower(email)—must match query exactly; without it, WHERE lower(email) scans
Covering index INCLUDE (name, email)—enables index-only scan; check EXPLAIN for "Heap Fetches"
Foreign key columns—not auto-indexed in PG; JOINs and ON DELETE CASCADE need them
Composite index order matters—(a, b) helps WHERE a = ? but not WHERE b = ?

Index Traps

Unused indexes hurt every INSERT/UPDATE—query pg_stat_user_indexes for idx_scan = 0, drop them
Too many indexes on write-heavy tables—balance carefully
Index on low-cardinality column (boolean, status) often useless—PG prefers seq scan
LIKE '%suffix' can't use B-tree—need pg_trgm GIN index or reverse() expression index