Dynamic programming-based join enumeration is a common and well-accepted ap- proach for plan generation. However, this approach is not suitable for every type of query, i.e., if the query is to complex because it references too many relations or it con- sists of too many join predicates. For those type of queries, plan generation often relies on computationally less intensive greedy join heuristics or modifies the query graph, as proposed in [24]. As a consequence, the produced plans are often not optimal, and, thus, the compiled query takes longer to execute. The first who analyzed the search space for dynamic programming-based/memoization-based plan generation were Ono and Lohman [26].
This work motivated Moerkotte and Neumann [22] to analyze different dynamic programming-based plan generation algorithms. As it turned out, depending on the shape of the query, all dynamic programming plan generation variants known to the literature were far from optimal when compared with the lower bounds given by Ono and Lohman [26]. As a consequence, Moerkotte and Neumann proposed DPCCP[22] as the first dynamic programming-based plan generator meeting those bounds.
For top-down join enumeration, no equivalent efficient solution was known. On the other hand, due to its demand-driven processing nature, top-down join enumeration is amenable to branch-and-bound pruning techniques. The beauty of branch-and-bound pruning is that it can speed up plan generation by several orders of magnitude while guaranteeing the optimality of the produced plan.
This gave rise to a rivalry between bottom-up plan generation via dynamic program- ming and top-down plan generation via memoization. DeHaan and Tompa followed with a memoization-based plan generator called TDMINCUTLAZY. Although dis- tinctly more efficient than the naive generate-and-test based memoization algorithm, TDMINCUTLAZYis still outperformed by DPCCP when not relying on its branch- and-bound pruning capabilities.
Both DPCCPand TDMINCUTLAZYcould only handle graphs and thus only a sub-
set of real-world queries. Therefore, Moerkotte and Neumann [21] developed DPHYP, which was the first efficient plan generator that could deal with hypergraphs. Thus, top-down join enumeration had a couple of disadvantages:
• There was a distinctive performance gap towards bottom-up plan generation. • Only a subset of queries could be compiled, i.e., those that contained inner joins
and simple join predicates only.
• The proposed branch-and-bound pruning techniques were not safe. In particular, there are scenarios with a worst-case runtime behavior that is by several orders of magnitude higher than if no branch-and-bound pruning is applied.
By making a series of contributions, this thesis dealt with these disadvantages. We summarize the contributions in the following.
5.1. Graph-Aware Join Enumeration Algorithms
We motivated our work with a complexity analysis of TDBASIC. The analysis showed that the naive generate-and-test partitioning strategy used by TDBASIC has a com-
plexity in O(2|V |) per emitted ccp where |V | is the number of vertices in the query graph. In Appendix A, we analyzed MINCUTLAZY and showed that it has a worst case complexity in O(|V |2) per emitted ccp. Further, we improved upon TDMIN-
CUTLAZY and presented TDMINCUTLAZYIMP. Most importantly, we proposed three new graph-partitioning algorithms: MINCUTAGAT [9], MINCUTCONSERVA-
TIVE [12] and MINCUTBRANCH [8]. We showed that the complexity of MINCUT-
BRANCH is in O(1) for standard and acyclic graphs per emitted ccp. We presented
two efficient implementations in C++ for MINCUTCONSERVATIVE (Appendix B.1) and MINCUTBRANCH(Appendix B.2). In our evaluation, MINCUTCONSERVATIVE
and MINCUTBRANCH proved to be competitive with DPCCP. For random acyclic queries they outperformed DPCCP, for random cyclic queries they were dominated by DPCCP. But altogether, the performance differences were marginal. In summary, we
closed the performance gap between top-down versus bottom-up plan generation by presenting MINCUTCONSERVATIVEand MINCUTBRANCH.
5.2. Hypergraph-Aware Join Enumeration Algorithms
Previous work emphasized that a plan generator has to deal with hypergraphs [2, 21, 27, 20, 35], otherwise it can compile only a subset of real-world queries. There- fore, we presented MINCUTCONSERVATIVEHYP[11] as an extension of MINCUT-
CONSERVATIVE as the first efficient partitioning algorithm for hypergraphs. Further-
more, we proposed a generic graph-partitioning framework that enables any graph- aware partitioning algorithm to handle hypergraphs [10]. The beauty of this approach is that we could reuse our efficient and competitive graph-partitioning algorithms MIN-
CUTCONSERVATIVEand MINCUTBRANCH.
In our performance evaluation, we compared TDMCBHYPas our generic top-down join enumeration algorithm instantiated with our generic graph-partitioning framework and MINCUTBRANCHas partitioning algorithm with DPHYP. As our results proved, TDMCBHYPis indeed competitive with DPHYP, even without exploiting its branch- and-bound pruning capabilities. We were able to support this claim by the performance results of the TPC-H, TPC-DS and the SQLite test suite benchmarks.
5.3. Branch and Bound Pruning
The main advantage of top-down join enumeration over bottom-up join enumeration is that it allows for branch-and-bound pruning. Two branch-and-bound pruning methods are known to the literature: accumulated cost bounding and predicted cost bounding. It has been shown how both methods can be combined. Through seven advancements we achieved an improvement to the combined pruning method (a) in terms of effective- ness, (b) in terms of robustness and (c), most importantly, by avoiding the worst-case behavior otherwise observed. For the workload considered in our evaluation, we im- proved the average pruning performance by a factor of 10 and decreased the worst-case
5.4. Graceful Degradation
behavior by a factor of 25 − 225. By considering the TPC-H, TPC-DS and SQLite queries, we have shown that our improvements speed up plan generation by 100% when compared with DPHYPas the state-of-the-art in bottom-up plan generation.
5.4. Graceful Degradation
As has been mentioned, for complex queries, i.e., queries that reference many rela- tions or queries that consist of many join predicates, plan generation relies often on computationally less intensive greedy join heuristics. With the contributions of this thesis, we showed how compile time can be decreased by orders of magnitude while still preserving plan optimality. Thus, in order to produce an optimal plan, we are able to consider much more complex queries than before.
This leads to an important question: When should plan generation rely on dynamic programming/memoization and when should it use a greedy join heuristic? The prob- lem with dynamic programming is that it does not exhibit graceful degradation. This means that a complete query execution plan is produced very late in the plan generation process. One solution to this is to limit the query by a certain number of relations/join edges or a combination of both. Queries with a higher number of relations/join edges are not considered for dynamic programming. Another solution gives the dynamic programming-based search process a time constraint. If the search takes longer, it is aborted and a greedy join heuristic is used. A more elegant solution modifies the query graph beforehand such that the search space for dynamic programming is decreased [24].
For top-down join enumeration, the latter solution can still be applied, but the whole problem is less immanent. For our improved accumulated predicted cost bounding method, we produce an initial plan by utilizing a greedy join heuristic. Thus, the search process can be stopped at any time, and we already have a usually relatively good plan. Moreover, top-down processing produces a complete plan, i.e. a plan that contains all relations of the query, much faster. Thus, it exhibits graceful degradation. Due to the demand-driven nature of top-down join enumeration, we have the means of prioritizing the optimization of certain (sub)plans. In such a way, we can consider the (sub)plans of the initial plan at a faster rate. This is achieved by the sixth advancement of our pruning method. Hence, if the search process needs to be stopped, we do have a plan that has the same or even and most likely smaller cost than our initial plan.
5.5. Summary
With TDMCBHYPAP CBI and TDMCCFWHYPAP CBI, we presented two top-down
join enumeration algorithms that are based on our generic partitioning frame- work and use the improved accumulated predicted cost-bounding method. Where- as TDMCBHYPAP CBI relies on MINCUTBRANCH as graph-partitioning method,
TDMCCFWHYPAP CBIutilizes MINCUTCONSERVATIVE.
Our experiments with the TPC-H, TPC-DS and SQLite queries showed that both
TDMCBHYPAP CBI and TDMCCFWHYPAP CBI dominate DPHYP, which is the
state-of-the-art in bottom-up processing. Thereby, TDMCBHYPAP CBIperforms best.
Moreover, for our generated workloads we have shown that TDMCBHYPAP CBI and
TDMCCFWHYPAP CBI have a better worst-case performance than DPHYP.
Because of the distinctive performance advantage, a better worst-case behavior and graceful degradation, we can conclude: When building a query optimizer, top-down processing, and, in particular, TDMCBHYPAP CBIshould be the alternative of choice.