Indexing Strategy Language
5 exercises — practise the English vocabulary for indexing strategy discussions: B-tree limitations, partial indexes, the leftmost prefix rule, covering indexes, and arguing about the trade-offs of excessive indexing.
Indexing vocabulary quick reference
- B-tree: equality, range, and prefix queries — cannot use leading wildcard LIKE '%x%'
- Partial index: WHERE clause limits indexed rows — smaller, faster, lower write cost
- Leftmost prefix rule: composite index (A, B) is usable only when A is in the WHERE clause
- Covering index: includes all projected columns — enables Index Only Scan, no heap fetch
- INCLUDE clause: PostgreSQL 11+ — adds non-key payload columns to index leaf pages
- Indexing cost: every index slows INSERTs, UPDATEs, DELETEs and increases autovacuum time
Question 1 of