Module 2 · Indexes

Episode 18 Lab: The Index That Wasn't Used

7 min · SQL · Postgres

Title card of Episode 18: Lab: The Index That Wasn't Used

▶ This video is coming to YouTube soon.

Four queries on a real Postgres store ignore their indexes. This lab reproduces each with EXPLAIN (ANALYZE, BUFFERS), diagnoses why (a composite index's leading column, a function on the column, a numeric cast, and a predicate matching 29.9% of rows), fixes three of them, and measures why forcing the fourth is slower.

Chapters

  1. 0:00Intro
  2. 0:13The symptom
  3. 0:31Reproduce: four queries, four Seq Scans
  4. 1:09Diagnose A: the index leads with the wrong column
  5. 2:01Diagnose B and C: the index holds the column, not your expression
  6. 2:53Diagnose D: 30% of the rows is every page
  7. 3:42Fix A, B and C
  8. 4:17Measure D: forcing the index is slower
  9. 5:12Before and after
  10. 5:45Why isn't my index used? A checklist
  11. 6:29Recap
  12. 6:55Next: Processing Models