Making queries fast.
WHAT TO DO FIRST
Read the query plan.
WHAT TO LOOK FOR
Full scans where an index should be used Estimated versus actual row counts differing greatly Expensive sorts Joins producing far more rows than expected
WHY ESTIMATE ACCURACY MATTERS
The optimiser chooses a plan from estimates, and wrong estimates produce bad plans.
WHAT CAUSES BAD ESTIMATES
Stale statistics Correlated conditions the optimiser assumes independent
WHAT TO DO
Update statistics, and consider rewriting.
WHAT INDEXES DO
Allow finding rows without scanning everything.
WHAT PREVENTS THEIR USE
A function applied to the indexed column Implicit type conversion Leading wildcards in pattern matching
WHAT COLUMNAR STORAGE CHANGES
Reading only the columns needed, and compressing well.
WHAT THAT MEANS
Selecting fewer columns matters far more than in row-based systems.
WHAT PARTITIONING PROVIDES
Skipping entire sections of data.
WHAT TO PARTITION BY
What you filter on most, usually date.
WHAT TO AVOID
Very many small partitions Queries that cannot use the partition key
WHAT TO MEASURE
Data scanned, not only elapsed time, where billing depends on it.
