5.2Execution plans & the optimizer

For an application developer or DBA, when a SQL statement is correct but slow to execute, the cause lies in which procedure (execution plan) the DBMS uses to process the query. This section covers the thinking behind the optimizer, the mechanism by which a DBMS estimates "which execution plan will be fastest," and the judgment needed to read an execution plan and improve a slow query.

5.2.1Cost-based optimization and statistics

• Cost-based optimization (CBO) estimates the I/O and CPU cost of each of several candidate execution plans that would yield the same result (which index to use, which join method, etc.) and chooses the plan with the lowest estimated cost. This is the approach adopted by all major modern RDBMSs.
• The accuracy of the cost estimate depends heavily on the freshness of statistics (metadata the DBMS collects, such as the distribution of values per column, row counts, NULL ratios, and index selectivity). If statistics go stale—for example, right after a large bulk update or delete—there is a risk of estimating a row count wildly different from reality and choosing an inappropriate execution plan (e.g., a full table scan where an index should have been used). Building periodic statistics recollection (e.g., ANALYZE) into operations is important.