Cost-based optimizer
Query planner design where every alternative plan is assigned an estimated "cost" (typically IO + CPU estimate) and the cheapest plan is chosen. Standard in every modern RDBMS since ~1990. Depends on correct statistics about data distribution.
Cardinality estimation is the heart: how many rows does WHERE x > 5 return? The planner uses histograms + most-common-values + n-distinct counts. Wrong: 100 rows estimated, 100k actual → nested loop becomes catastrophically slow. Postgres tunable: random_page_cost (default 4.0, lower to 1.1 for SSD). effective_cache_size. Plan stability: when stats are 50% off, the planner may pick one plan today, another tomorrow — performance jitter. Cloud DBs (Aurora, Snowflake) do adaptive replanning at runtime.