Module 14: Indexes and query plans

Make slow queries fast: how B+ tree indexes work, reading EXPLAIN, composite indexes and when an index can't help.

1. Why indexes, and the B+ tree idea: An index is a sorted B+ tree of values and row addresses: a lookup reads 3-4 pages instead of the whole table; PK and UNIQUE are indexed for you, foreign keys are not.
2. Reading EXPLAIN: scans and joins: Read plans inside-out; Index/Index Only/Bitmap scans use indexes, Seq Scan reads everything; nested loop for small outer sides, hash for big unsorted inputs, merge for sorted ones.
3. Composite and covering indexes: A composite index (a, b) is sorted by a then b: it serves a and a + b but not b alone; equality columns first, ranges last; INCLUDE makes it covering for Index Only Scans.
4. When an index can't help: Keep the indexed column bare (no functions, casts, maths or leading %); low-selectivity filters skip indexes; every index slows writes; use expression and partial indexes for the queries you really run.

Ends with a 13-question checkpoint (80% passes) and a project: Speed up the food app.