Parallelizing query optimization

Abstract

Many commercial RDBMSs employ cost-based query optimization exploiting dynamic programming (DP) to efficiently generate the optimal query execution plan. However, optimization time increases rapidly for queries joining more than 10 tables. Randomized or heuristic search algorithms reduce query optimization time for large join queries by considering fewer plans, sacrificing plan optimality. Though commercial systems executing query plans in parallel have existed for over a decade, the optimization of such plans still occurs serially. While modern microprocessors employ multiple cores to accelerate computations, parallelizing query optimization to exploit multi-core parallelism is not as straightforward as it may seem. The DP used in join enumeration belongs to the challenging nonserial polyadic DP class because of its non-uniform data dependencies. In this paper, we propose a comprehensive and practical solution for parallelizing query optimization in the multi-core processor architecture, including a parallel join enumeration algorithm and several alternative ways to allocate work to threads to balance their load. We also introduce a novel data structure called skip vector array to significantly reduce the generation of join partitions that are infeasible. This solution has been prototyped in PostgreSQL. Extensive experiments using various query graph topologies confirm that our algorithms allocate the work evenly, thereby achieving almost linear speed-up. Our parallel join enumeration algorithm enhanced with our skip vector array outperforms the conventional generate-and-filter DP algorithm by up to two orders of magnitude for star queries-linear speedup due to parallelism and an order of magnitude performance improvement due to the skip vector array.

Toward GPU-accelerated Database Optimization

“…Although in DBMSs several optimization approaches exist, which would benefit from the high parallelism of GPUs, so far, the application of GPUs in DBMSs is limited to selectivity estimation. As Han et al showed, other optimization approaches, such as DP for join-order optimization, also benefit from parallelization [17]. Hence, we expect that the higher parallelism of GPUs would also increase the quality of these optimization approaches in DBMSs.…”

“…Nevertheless, the increased parallelism of the DP approach for join-order optimization can be used on multi-core CPUs to provide a linear speedup [17]. This speed up improves the applicability of the DP approach for join-order optimization from 12 joined relations to 22-25 joined relations [17].…”

“…First, we discuss one already evaluated database optimization technique, the KDE for selectivity estimation [21]. Second, we discuss a parallelized Dynamic Programming (DP) approach for join-order optimization [17], which might benefit to a different extend from GPUs compared to KDE. Third, we discuss a GPU-accelerated Dijkstra algorithm for the Single-Source Shortest Path (SSSP) [19], usable in different optimization problems.…”

“…Although Han et al showed that the calculations within each step can be parallelized, the parallelization of different steps is not possible [17]. Nevertheless, the increased parallelism of the DP approach for join-order optimization can be used on multi-core CPUs to provide a linear speedup [17].…”

Parallel skyline computation on multicore architectures

“…In order to exploit multicore architectures to their fullest, we may have to devise new index structures or reimplement database operations accordingly. In fact, researches along this line are already producing highly promising (and even surprising) results in the database community [17,[11][12][13]29,6]. Certainly we do not want to find ourselves struggling to squeeze performance out of just a single core while all other 31 cores remain idle!…”