Constrained Physical Database Design - Automated Physical Database Design and Tuning - page 213

11.3 Examples of Tuning Sessions with Constraints
In this section we illustrate the constrained physical design problem with some
examples based on a 1 GB TPC-H database and a 22-query workload. Suppose
that we want to find a good configuration under 2 GB for which no query exe-
cutes slower than 70% of the time under the currently deployed configuration
(we denote that constraint, S70 , next). The specification looks as follows:

FOR I IN C ASSERT sum(size(I)) ≤ 2G

≤

0.7 * cost(Q, COrig)
After running a tool for 5 minutes, we obtained no feasible solution for
this specification. Instead, the search procedure returned the nondominated
configurations in Figure 11.7. We then infer that constraints are too strict.
Specifically, the tight storage constraint is preventing simultaneously satisfy-
ing the S70 constraint.
To relax the problem and obtain a solution, we can replace the hard storage
constraint by the following one:

FOR Q IN W ASSERT cost(Q, C)

FOR I IN C SOFT ASSERT sum(size(I)) ≤ 2G

Essentially, we transform the problem into a multiobjective problem (reducing
both execution time and storage) with a single S70 constraint. As there are
multiple soft constraints , the search strategy is not guaranteed to return a
single solution. Instead, it returns the set of nondominated configurations
shown in Figure 11.8. These are the configurations satisfying S70 with the
best trade-offs between size and execution cost. The figure also shows that

1300

460

2000

0%

6000 6%

FIGURE 11.7 Nondominated configurations for S70 .