Index Design and Query Execution
An index is a maintained physical shortcut for selected access patterns. Every shortcut consumes memory and adds work to writes.
Planned model
Run filters and joins through scans, ordered indexes, hash indexes, and composite indexes. Display candidate rows, random reads, selectivity, and maintenance cost.
Questions
- Which key order supports a query prefix?
- When is a scan cheaper than following an index?
- How do statistics errors lead to a poor execution plan?
Exercise
Choose the smallest index set for a concrete query workload and enumerate the write amplification it creates.