Cost-Function Complexity Matters: When Does Parallel Dynamic Programming Pay Off for Join-Order Optimization
The execution time of queries can vary by several orders of magnitude depending on the join order. Hence, an efficient query execution can be ensured by determining optimal join orders. Dynamic programming determines optimal join orders efficiently. Unfortunately, the runtime of dynamic programming depends on the characteristics of the query, limiting the applicability to simple optimization problems. To extend the applicability, different parallelization strategies were proposed. Although existing parallelization strategies showed benefits for complex cost functions, the effects of the cost-function complexity was not evaluated.
Therefore, in this paper, we compare different sequential and parallel dynamic programming variants with respect to different query characteristics and cost-function complexities. We show that the parallelization of a parallel dynamic programming variant is most often only useful for complex cost functions. For simple cost functions, we show that most often sequential variants are superior to their parallel counterparts.
This work was partially funded by the DFG (grant no.: SA 465/50-1).
- 1.Fong, Z.: The design and implementation of the POSTGRES query optimizer. Technical report, University of California, Berkeley, August 1986Google Scholar
- 2.Han, W.S., Kwak, W., Lee, J., Lohman, G.M., Markl, V.: Parallelizing query optimization. PVLDB 1(1), 188–200 (2008)Google Scholar
- 3.Han, W.S., Lee, J.: Dependency-aware reordering for parallelizing query optimization in multi-core CPUs. In: SIGMOD, pp. 45–58. ACM (2009)Google Scholar
- 4.Leis, V., Gubichev, A., Mirchev, A., Boncz, P., Kemper, A., Neumann, T.: How good are query optimizers, really? PVLDB 9(3), 204–215 (2015)Google Scholar
- 5.Moerkotte, G., Fender, P., Eich, M.: On the correct and complete enumeration of the core search space. In: SIGMOD, pp. 493–504. ACM (2013)Google Scholar
- 6.Moerkotte, G., Neumann, T.: Analysis of two existing and one new dynamic programming algorithm for the generation of optimal bushy join trees without cross products. In: VLDB, pp. 930–941. VLDB Endowment (2006)Google Scholar
- 7.Moerkotte, G., Scheufele, W.: Constructing optimal bushy processing trees for join queries is NP-hard. Technical report Informatik-11/1996 (1996)Google Scholar
- 8.Neumann, T.: Engineering high-performance database engines. PVLDB 7(13), 1734–1741 (2014)Google Scholar
- 9.Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., Price, T.G.: Access path selection in a relational database management system. In: SIGMOD, pp. 23–34. ACM (1979)Google Scholar
- 11.Vance, B., Maier, D.: Rapid bushy join-order optimization with cartesian products. In: SIGMOD, pp. 35–46. ACM (1996)Google Scholar