Indexes And Performance

Indexes and Performance

How indexes work, when to add them, and how to read query plans. The module that turns "this query is slow" into "I know exactly why."

Suggest an edit

Indexes and Performance

Every other module so far has assumed your queries return correctly. This one is about returning correctly fast. Indexes are the single biggest lever — the difference between a query that takes 5 ms and the same query taking 5 minutes.

Place in the curriculum

  • Prerequisites: comfort with JOIN, WHERE, schema design.
  • Followed by: Transactions and Concurrency. Indexes affect concurrency too — they reduce lock contention and enable better isolation.

Chapters

  1. B-Tree Indexes — how a B-tree works, when an index helps, sargability, covering indexes.
  2. Other Index Types — Hash, GIN, GiST, BRIN, partial indexes, expression indexes.
  3. EXPLAIN and Query Plans — reading the planner's output, scan types, join algorithms, cost vs actual.
  4. Query Anti-Patterns — function-on-column, leading wildcards, OR vs UNION, the patterns that block index use.
Mark as read