Databases · Cluster
Query Optimization
Cost models, cardinality, and plan choice.
- 01 How a Query Cost Model Reduces I/O and CPU to One Number Why planner costs are weighted units rather than milliseconds, how sequential and random page reads enter the sum, and which effects the arithmetic deliberately ignores.
- 02 Selectivity Estimation from Histograms and Most-Common-Value Lists How a single-column statistic turns a predicate into a row count, why equidepth buckets are chosen over equiwidth, and where the estimate fails for correlated columns.
- 03 The Crossover Between an Index Scan and a Sequential Scan Why fetching a few percent of a table by index can cost more than reading all of it, how physical correlation moves the crossover point, and what bitmap scans change.
- 04 Join Order Search and the Explosion of the Plan Space Left-deep and bushy trees, why the search is NP-hard, and the pruning rules that keep a twenty-table join tractable.
- 05 Join Cardinality and Where the Containment Assumption Fails How equality joins are estimated from distinct-value counts, why the uniform-frequency assumption collapses on skewed keys, and how the error compounds across a chain of joins.
- 06 Plan Caching, Generic Plans, and the Parameter Sensitivity Problem Why a reused plan is compiled for a parameter it will never see again, how the choice between custom and generic plans is made, and why parameter sniffing produces plans that fit one value.
- 07 Sort, Hash, and Materialize: Memory Limits and Spill to Disk How a hash join decides it must partition, what a sort does when the work memory runs out, and why an underestimated row count becomes a disk-bound plan.
- 08 Adaptive Plans: Choosing the Join Algorithm After Execution Starts How a statistics collector at a plan node defers the choice between nested loop and hash join, what the switch costs mid-flight, and why re-planning after a blocking operator is a different mechanism.
- 09 Costing a Parallel Plan: Workers, Gather, and the Serial Fraction Why dividing a plan cost by the number of workers overestimates the gain, how the planner prices worker startup and row transfer, and when a parallel plan loses to a serial one.
- 10 Learning Optimizers: Learned Cardinality Models and Plan Regression What a learned selectivity model replaces in the catalog, why a plan that is cheaper on average can be a regression for one query, and how plan management contains the damage.