Investigate the work behind the result
Performance starts with a correct result and a measurable symptom. This chapter moves from plans and access paths to reproducible experiments, then contrasts transactional engines with analytical warehouses.
Suggested route: Follow workflow → plans → indexes → statistics → joins and predicates. Run the index and partition labs before jumping to engine-specific guidance.
By the end: Form a hypothesis from a plan, verify unchanged results, and measure a change without claiming universal speedups.
Decision reference
| Symptom | First lesson |
|---|---|
| Unclear source of slowness | When and where to optimize |
| Much input, tiny output | Plans and access paths |
| Estimates differ from reality | Statistics and skew |
| Large joins or spills | Joins, sorting, and memory |
| Broad time scans | Predicates and partition pruning |
| Warehouse scan or shuffle cost | Warehouse performance |
In this chapter
- When and where to optimizeTurn a performance complaint into a measurable question before changing SQL or infrastructure.
- Reading EXPLAIN plansFollow data flow, compare estimates to reality, and find expensive work.
- Indexes and access pathsChoose indexes from predicates, ordering, and workload rather than a checklist.
- Statistics, cardinality, and skewExplain why an optimizer can choose a poor plan even when a useful index exists.
- Joins, sorting, and memoryRelate physical operators to input size, repeated work, and intermediate results.
- Predicates and query rewritesMake restrictions usable while preserving dates, null behavior, and the intended population.
- Correctness & optimization trapsCheck semantics before comparing query performance.
- Partitioning & pruningSeparate physical data layout from window partitions and logical grouping.
- Pagination: OFFSET and keysetsReturn stable pages without repeatedly skipping an ever-growing prefix.
- Lab: investigate an order lookupCapture a baseline, add a candidate index, and compare results and actual plans on PostgreSQL.
- Lab: prove partition pruningCompare a time-bounded request with an asset-only request and inspect the partitions actually accessed.
- Warehouse performance: BigQuery, Redshift, AthenaInvestigate pruning, data movement, and file layout using the warehouse's own evidence.
- SQL Server performance investigationConnect actual plans, logical reads, waits, and Query Store history to a specific regression.
How to study
Use the suggested route above. For each lesson, explain the decision in your own words, test its example, and identify a situation where a different approach would be needed.