Efficiently approximating query optimizer plan diagrams

Abstract

Given a parametrized n-dimensional SQL query template and a choice of query optimizer, a plan diagram is a color-coded pictorial enumeration of the execution plan choices of the optimizer over the query parameter space. These diagrams have proved to be a powerful metaphor for the analysis and redesign of modern optimizers, and are gaining currency in diverse industrial and academic institutions. However, their utility is adversely impacted by the impractically large computational overheads incurred when standard bruteforce exhaustive approaches are used for producing fine-grained diagrams on high-dimensional query templates. In this paper, we investigate strategies for efficiently producing close approximations to complex plan diagrams. Our techniques are customized to the features available in the optimizer's API, ranging from the generic optimizers that provide only the optimal plan for a query, to those that also support costing of sub-optimal plans and enumerating rank-ordered lists of plans. The techniques collectively feature both random and grid sampling, as well as inference techniques based on nearest-neighbor classifiers, parametric query optimization and plan cost monotonicity. Extensive experimentation with a representative set of TPC-H and TPC-DS-based query templates on industrial-strength optimizers indicates that our techniques are capable of delivering 90% accurate diagrams while incurring less than 15% of the computational overheads of the exhaustive approach. In fact, for full-featured optimizers, we can guarantee zero error with less than 10% overheads. These approximation techniques have been implemented in the publicly available Picasso optimizer visualization tool.

Multi-objective parametric query optimization

“…Most work on PQ assumes that cost functions are PWL [13,17,12]. We make the same assumption in the following.…”

“…Our comparison between PQ and MPQ focuses on three problem properties that have been shown to hold for PQ. Those three problem properties were already called the guiding principles of PQ [12] since many PQ algorithms exploit them in one way or another [13,17,12], assuming that they hold either over the whole parameter space [13,17] or at least locally [12]. We will see that the guiding principles do not hold anymore for MPQ which makes many successful approaches to PQ inapplicable to MPQ.…”

“…It may at first seem possible to model cost metrics as parameters; if all but one cost metric could be represented as parameters then PQ algorithms could be applied. Trying to model for instance monetary fees as a parameter in Scenario 1 (such that execution time becomes a function of predicate selectivities and monetary budget) leads however to the following problems: First, existing PQ algorithms [19,13,18,5,12,9] usually assume that the value domain of each parameter is known in advance. This is realistic for predicate selectivity or the available amount of buffer space but not for monetary fees, as finding the minimal execution fees for a given query is a hard optimization problem all by itself.…”

“…The goal in PQ is usually to generate a plan set that contains one optimal plan for each possible parameter value combination [13,17,18,7]. Many approaches to PQ are based on parameter space decomposition [13,17,18,12,7]. They repeatedly invoke a standard optimizer to generate optimal plans for fixed parameter values (if the parameter values are fixed then the cost of a query plan can be modeled as a constant value again) in order to decompose the parameter space into regions in which a single plan is optimal.…”

Identifying robust plans through plan diagram reduction

“…Therefore, it is possible that future better implementations of the FPC feature may bring SEER's running time closer to CostGreedy. (In fact, our own implementation of FPC in a public-domain optimizer indicates that its cost can be further brought down by an order of magnitude [7].) When we consider the 3D query templates, however, the running times of SEER can be quite large.…”