Sharing-aware horizontal partitioning for exploiting correlations during query processing

Abstract

Optimization of join queries based on average selectivities is suboptimal in highly correlated databases. In such databases, relations are naturally divided into partitions, each partition having substantially different statistical characteristics. It is very compelling to discover such data partitions during query optimization and create multiple plans for a given query, one plan being optimal for a particular combination of data partitions. This scenario calls for the sharing of state among plans, so that common intermediate results are not recomputed. We study this problem in a setting with a routing-based query execution engine based on eddies [1]. Eddies naturally encapsulate horizontal partitioning and maximal state sharing across multiple plans. We define the notion of a conditional join plan, a novel representation of the search space that enables us to address the problem in a principled way. We present a lowoverhead greedy algorithm that uses statistical summaries based on graphical models. Experimental results suggest an order of magnitude faster execution time over traditional optimization for high correlations, while maintaining the same performance for low correlations.

High performance stream query processing with correlation-aware partitioning

“…By ignoring strong inter-stream correlations existing optimizers miss the critical join pair pruning opportunity illustrated above. To avoid enumerating the entire search space, they identify partitions either by employing off-the-shelf randomized partitioning algorithms [13] or by utilizing greedy search algorithms without backtracking [20]. These simplistic search heuristics tend to result in ineffective optimization decisions as confirmed by our experiments (Sec.…”

“…In [20] Tzoumas et al introduces the notion of conditional join plans, a restricted search space resulting from horizontal partitioning that captures both the partitioning and join ordering aspects. However [20] makes partitions only based on one single attribute, while our work can partition on any set of attributes.…”

“…However [20] makes partitions only based on one single attribute, while our work can partition on any set of attributes. They estimate the join selectivity with the assumption that conditional independencies exist in the data.…”

“…We compare the performance of our CMR against the three key alternative approaches in the literature, namely (1) the traditional "single plan for all data" system [18] (SP), (2) the "query mesh" system [13] (QM), and (3) the "Sharing-Aware Horizontal Partitioning" [20] (HP). For SP, we implement a multi-way join (MJoin) [18] operator.…”

“…While initial work on multi-route processing has recently emerged [13,20], the existing techniques do not address the above described challenges. To reduce the search space these multi-route optimizers construct partitions by either only considering the local statistics of each individual stream [13] in isolation or by going with the unrealistic simplification of assuming conditional independence across attributes [20]. By ignoring strong inter-stream correlations existing optimizers miss the critical join pair pruning opportunity illustrated above.…”