How does a cost-based optimizer choose a plan? Why do stale statistics cause bad plans?

Answer preview

A cost-based optimiser estimates how many rows each operator will produce, assigns a cost per I/O and CPU, and picks the cheapest plan. Those row estimates come from table statistics collected by ANALYZE — when stats are stale, plans go wrong.…