Counter Strike: Generic Top-Down Join Enumeration for Hypergraphs

  title={Counter Strike: Generic Top-Down Join Enumeration for Hypergraphs},
  author={Pit Fender and Guido Moerkotte},
  journal={Proc. VLDB Endow.},
Finding the optimal execution order of join operations is a crucial task of today's cost-based query optimizers. There are two approaches to identify the best plan: bottom-up and top-down join enumeration. But only the top-down approach allows for branch-and-bound pruning, which can improve compile time by several orders of magnitude while still preserving optimality. For both optimization strategies, efficient enumeration algorithms have been published. However, there are two severe… 

Figures and Tables from this paper

Improving Join Reorderability with Compensation Operators
This work is able to support complete join reorderability for this class of queries which supersedes the state-of-the-art approaches.
How Good Are Query Optimizers, Really?
This paper introduces the Join Order Benchmark (JOB) and experimentally revisit the main components in the classic query optimizer architecture using a complex, real-world data set and realistic multi-join queries.
Optimizing Join Enumeration in Transformation-based Query Optimizers
This paper proposes an efficient new ruleset for generating cross-product free trees, based on techniques proposed recently for top-down join order enumeration, but unlike earlier work it is cleanly integrated into the Volcano/Cascades framework, and can be used in conjunction with other transformation rules.
Query optimization through the looking glass, and what we found running the Join Order Benchmark
This paper introduces the Join Order Benchmark that works on real-life data riddled with correlations and introduces 113 complex join queries and investigates plan enumeration techniques comparing exhaustive dynamic programming with heuristic algorithms and finds that exhaustive enumeration improves performance despite the suboptimal cardinality estimates.
Adaptive Optimization of Very Large Join Queries
An adaptive optimization framework is introduced that is able to solve most common join queries exactly, while simultaneously scaling to queries with thousands of joins, and produces optimal or near-optimal solutions for most common queries.
Algorithms for Efficient Top-Down Join Enumeration
This thesis focuses on the top-down join enumeration and proposes a more generic partitioning framework that enables every graph-partitioning algorithm to handle join predicates involving more joins.
Cardinality Estimation Done Right: Index-Based Join Sampling
Indexbased join sampling is proposed, a novel cardinality estimation technique for main-memory databases that relies on sampling and existing index structures to obtain accurate estimates and significantly improves estimation as well as overall plan quality.
A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration
This paper reviews the techniques used to improve the quality of the three key components in the cost-based optimizer, cardinality estimation, cost model, and plan enumeration, and provides insights on the future directions.
Bi-objective Optimization for Approximate Query Evaluation
The proposed optimization and execution model provides for interactive trade of quality for speed as a special case of multi-objective optimization with 2 criteria: the computational resources and the quality of result.
On the Calculation of Optimality Ranges for Relational Query Execution Plans
This paper analyzes the deviation from the estimate, and denotes the cardinality range of an intermediate result, where the optimal plan remains optimal as the optimality range, and derives theoretical worst case bounds for the number of enumerated plans required to compute the precise optimalityrange.


Top down plan generation: From theory to practice
  • P. FenderG. Moerkotte
  • Computer Science
    2013 IEEE 29th International Conference on Data Engineering (ICDE)
  • 2013
A novel top-down join enumeration algorithm that overcomes two severe limitations and is competitive when compared with the state of the art in bottom-up processing even without playing out its advantage by making use of its branch-and-bound pruning capabilities.
Dynamic programming strikes back
The most efficient known join-ordering algorithm, DPccp, is used as a starting point, and a new algorithm is developed, DPhyp, which is capable to handle complex join predicates efficiently, which gives dynamic programming a distinct advantage over current memoization techniques.
Reassessing Top-Down Join Enumeration
This paper presents an algorithm that has an according O(1) complexity in the case of a clique-shaped query graph and shows experimentally that this more theoretical result has indeed a high impact on the performance in other nonclique settings.
A new, highly efficient, and easy to implement top-down join enumeration algorithm
This paper presents an algorithm that has an according O(1) complexity in the case of a clique-shaped query graph and shows experimentally that this more theoretical result has indeed a high impact on the performance in other non-clique settings.
Optimal top-down join enumeration
This work describes a top-down join enumeration algorithm that is optimal with respect to the join graph and presents performance results demonstrating that a combination of optimal enumeration with search strategies such as branch-and-bound yields an algorithm significantly faster than those previously described in the literature.
Measuring the Complexity of Join Enumeration in Query Optimization
This paper describes and measures the performance of the Starburst join enumerator, which can parameterically adjust for each query the space of join sequences that arc evaluated by the optimizer to allow or disallow composite tables as the inner operand of a join.
Hypergraph based reorderings of outer join queries with complex predicates
This work presents the needed hypergraph abstraction and algorithms for reordering such queries with joins and outer joins, and shows how these algorithms are easily incorporated into well known and widely used enumeration methods such as dynamic programming.
Exploiting upper and lower bounds in top-down query optimization
The paper compares the performance of top-down and bottom-up optimizers, using the measure of the number of plans generated during optimization, to show that the use of upper bounds for the costs of the plans it generates is quite effective, while preserving the optimality of the resulting plans.
A Combined Framework for Grouping and Order Optimization
Outerjoin simplification and reordering for query optimization
This paper presents the theory and algorithms needed to generate alternative evaluation orders for the optimization of queries containing outerjoins, and presents both a complete set of transformation rules, suitable for new-generation, transformation-based optimizers, and a bottom-up join enumeration algorithm compatible with those used by traditional optimizers.