Query optimization

A query optimizer is the component of a database engine that transforms a declarative SQL statement into an efficient execution plan. It is, in effect, an artificial intelligence that makes decisions about how to access reality — which indexes to probe, which joins to execute first, whether to parallelize, and how to pipeline intermediate results.

The optimizer operates on statistical models of the data distribution, and its failures are often invisible: a query that runs in milliseconds with accurate statistics may run for hours when statistics are stale. The black-box nature of query optimization has made it one of the most sophisticated yet least understood components of modern software infrastructure. It is also a locus of power: the optimizer decides who waits and who proceeds, and its decisions are not auditable by the programmer who wrote the query.

See also: SQL, Database, Join (SQL), Relational model