Efficient and Provable Multi-Query Optimization

Abstract

Complex queries for massive data analysis jobs have become increasingly commonplace. Many such queries contain common subexpressions, either within a single query or among multiple queries submitted as a batch. Conventional query optimizers do not exploit these subexpressions and produce sub-optimal plans. The problem of multi-query optimization (MQO) is to generate an optimal combined evaluation plan by computing common subexpressions once and reusing them. Exhaustive algorithms for MQO explore an O(n n ) search space. Thus, this problem has primarily been tackled using various heuristic algorithms, without providing any theoretical guarantees on the quality of their solution.In this paper, instead of the conventional cost minimization problem, we treat the problem as maximizing a linear transformation of the cost function. We propose a greedy algorithm for this transformed formulation of the problem, which under weak, intuitive assumptions, provides an approximation factor guarantee for this formulation. We go on to show that this factor is optimal, unless P = NP. Another noteworthy point about our algorithm is that it can be easily incorporated into existing transformation-based optimizers. We finally propose optimizations which can be used to improve the efficiency of our algorithm.

On optimizing operator fusion plans for large-scale machine learning in systemML

“…Neumann pioneered the work on generating optimal DAG-structured query plans [63,66], while others heuristically share CSEs via materialized views [63,84,95] or common operators [6,17,32]. Recent work further introduced a greedy algorithm with guaranteed approximation factor [42]. Sideways information passing such as semi-join reductions [10], magic sets [8], bypass plans for disjunctive queries [86], or adaptive information passing [38,67] also deal with DAGs, but none of these techniques are integrated with query compilation.…”

Selecting subexpressions to materialize at datacenter scale

“…Heuristic approaches have incorporated MQO in a Volcano-style optimizer, using AND/OR DAGs, but without considering a space budget [42]. A recent work devised an approximation algorithm that runs in time quadratic to the number of common subexpressions and provides theoretical guarantees on the quality of the solution obtained [30]. Other approximate solutions have used genetic algorithms by treating a vector of query plans as chromosomes [6].…”

Query grouping–based multi‐query optimization framework for interactive SQL query engines on Hadoop

“…7,[20][21][22][23] The second one is to construct a global plan that can take full advantage of common tasks. In order to solve this problem, many algorithms have been proposed, [6][7][8][9][10][11] and most of these are heuristic based algorithms, eg, A* or Genetic Algorithm.…”

“…The most important problem of MQO is to take full advantage of common tasks by constructing a global plan. In order to solve this problem, many algorithms have been proposed, including heuristic algorithms– and some improved algorithms –…”

“…In order to solve this problem, many algorithms have been proposed, including heuristic algorithms 6-8 and some improved algorithms. [9][10][11] There are two alternative system architectures that can be used for integrating MQO. One is to take full advantage of the local optimizer, which can only optimize a single query at a time and merge the optimal plans of all queries together.…”