The query optimizer chooses the execution plan with lowest estimated cost.
What it considers:
Statistics: Table sizes, column distributions, index selectivity
Join ordering: Which tables to join first
Access methods: Seq scan vs index scan vs bitmap scan
Join algorithms: Nested loop vs hash join vs merge join
When it goes wrong:
- Stale statistics (run ANALYZE)
- Correlated columns (optimizer assumes independence)
- Parameter sniffing issues