Designing a Cost Model - Automated Physical Database Design and Tuning - page 77

Chapter 5
Designing a Cost Model
As we discussed in previous chapters, physical database design can be seen as
a complex search problem over a potentially very large space of feasible con-
figurations. Although there are different approaches to conducting this search
(we discuss different alternatives in Chapter 6), a common requirement of any
solution is the ability to evaluate the expected cost of a query under a given
candidate configuration in the search space. In Section 3.2.2 we discussed why
early designs, which were based on a cost model outside of the query opti-
mizer, cannot be trusted and in general produce undesirable results. At the
same time, it is fairly obvious that materializing each candidate configura-
tion in the database management system (DBMS), optimizing the workload
queries, and obtaining their costs are not feasible in practice.
Therefore, an important challenge for designing scalable physical design al-
gorithms is to eciently and accurately estimate query costs under arbitrary
configurations. To address this problem, we need a new component that is
able to simulate a hypothetical configuration in the DBMS and to optimize
queries without actually materializing new indexes (we call this process what-
if optimization ). In this chapter we explain different challenges that appear in
a robust implementation of this component. We then introduce additional im-
provements that further increase the performance of this what-if optimization
component without significantly compromising the quality of results.
5.1 What-If Optimization
The key observation that enables what-if optimization is that the query opti-
mizer does not actually leverage the content of the indexes (i.e., internal and
leaf nodes in the B + -trees ) during query optimization but instead exploits only
metadata information . Specifically, to determine whether an index is useful in
an execution plan, the optimizer leverages information about the table over
which the index is defined, the collections of key and included columns, the
size of the index, and existing integrity constraints (e.g., uniqueness).
77