Proactive re-optimization

Abstract

Traditional query optimizers rely on the accuracy of estimated statistics to choose good execution plans. This design often leads to suboptimal plan choices for complex queries, since errors in estimates for intermediate subexpressions grow exponentially in the presence of skewed and correlated data distributions. Reoptimization is a promising technique to cope with such mistakes. Current re-optimizers first use a traditional optimizer to pick a plan, and then react to estimation errors and resulting suboptimalities detected in the plan during execution. The effectiveness of this approach is limited because traditional optimizers choose plans unaware of issues affecting reoptimization. We address this problem using proactive reoptimization, a new approach that incorporates three techniques: i) the uncertainty in estimates of statistics is computed in the form of bounding boxes around these estimates, ii) these bounding boxes are used to pick plans that are robust to deviations of actual values from their estimates, and iii) accurate measurements of statistics are collected quickly and efficiently during query execution. We present an extensive evaluation of these techniques using a prototype proactive re-optimizer named Rio. In our experiments Rio outperforms current re-optimizers by up to a factor of three.

A Foundation for the Replacement of Pipelined Physical Join Operators in Adaptive Query Processing

“…POP [10] explores several approaches to adaptation that materialize the results of complete sub-plans, but when an operator is replaced during its evaluation, the replacement operator starts evaluating from scratch, thus repeating work that was done by its predecessor. Rio [3] tests the suitability of an operator at a place in a plan by sampling and caching its inputs, and, like POP, when an algorithm is replaced it is rerun from scratch over its input buffers. This paper complements existing work by investigating finer-grained operator replacement and by providing a formal characterization of the changes made.…”

Automation Everywhere: Autonomics and Data Management

“…For example, specific proposals have been made that reoptimize queries, reusing at least some of the results produced to date, when selectivity estimates are shown to be inaccurate (e.g. [18,33,3]), or to rebalance load in parallel query evaluation (e.g. [14,30,31]).…”

“…As such interventions commonly incur some cost, may block ongoing activities while changes are made, and may discard partially completed tasks when changing the state of a system, there is certainly the potential for more harm to be done than good. For example, [3] describes circumstances in which an adaptive query processor may thrash by repeatedly identifying alternative strategies during the evaluation of a query, sometimes resorting to a previously discarded plan. An earlier proposal contains a threshold on the number of adaptations it may carry out with a view to limiting the consequences of repeated adaptations in an uncertain setting [26].…”

“…An earlier proposal contains a threshold on the number of adaptations it may carry out with a view to limiting the consequences of repeated adaptations in an uncertain setting [26]. Both [3] and [26] make particularly well motivated decisions as to when to adapt, and thus may be felt to be less prone to unproductive adaptations than many proposals. A comparison of several adaptive load balancing strategies [28] revealed circumstances in which all of the adaptive strategies did more harm than good, as illustrated in Figure 1, although all the techniques compared sometimes improved on static oprimization.…”

“…In addition, as query processors are increasingly used in less controlled environments, there has been a growing interest in adaptive query processing, whereby queries can be revised during their evaluation to compensate for inappropriate assumptions about the data (e.g. [3,26]) or to react to changes in the environment (e.g. [28]).…”