RelJoin: Relative-cost-based Selection of Distributed Join Methods for Query Plan Optimization

F. Liang,F.C.M. Lau,H. Cui,Y. Li,B. Lin,C. Li,X. Hu

DOI: https://doi.org/10.1016/j.ins.2023.120022

2023-11-24

Abstract:Selecting appropriate distributed join methods for logical join operations in a query plan is crucial for the performance of data-intensive scalable computing (DISC). Different network communication patterns in the data exchange phase generate varying network communication workloads and significantly affect the distributed join performance. However, most cost-based query optimizers focus on the local computing cost and do not precisely model the network communication cost. We propose a cost model for various distributed join methods to optimize join queries in DISC platforms. Our method precisely measures the network and local computing workloads in different execution phases, using information on the size and cardinality statistics of datasets and cluster join parallelism. Our cost model reveals the importance of the relative size of the joining datasets. We implement an efficient distributed join selection strategy, known as RelJoin in SparkSQL, which is an industry-prevalent distributed data processing framework. RelJoin uses runtime adaptive statistics for accurate cost estimation and selects optimal distributed join methods for logical joins to optimize the physical query plan. The evaluation results on the TPC-DS benchmark show that RelJoin performs best in 62 of the 97 queries and can reduce the average query time by 21% compared with other strategies.

Databases,Distributed, Parallel, and Cluster Computing

What problem does this paper attempt to address?

### Problems the Paper Attempts to Solve

This paper aims to address the issue of selecting appropriate distributed join methods in distributed query plan optimization, particularly in performance optimization on large-scale data-intensive computing (DISC) platforms.

#### Specific Issues
1. **Network Communication Cost Issue**: Most cost-based query optimizers primarily focus on local computation costs and do not accurately model network communication costs. Different network communication patterns during the data exchange phase significantly impact the performance of distributed joins.
2. **Accurate Cost Model**: A cost model is proposed to optimize join queries in DISC platforms. This model accurately measures the network and local computation workloads at different execution stages, utilizing dataset size, cardinality statistics, and cluster join parallelism.
3. **Adaptive Statistics**: An adaptive runtime statistics method is introduced to accurately estimate costs and select the optimal distributed join method.

#### Main Contributions
1. **Cost Model**: A cost model is constructed that considers various distributed join methods with different communication patterns and memory operations, emphasizing the importance of the relative size of join datasets.
2. **RelJoin Strategy**: The RelJoin query optimization strategy is proposed and implemented, seamlessly integrated into SparkSQL. It selects the best distributed join method based on adaptive runtime statistics and efficiently re-optimizes the physical query plan.
3. **Evaluation Results**: Through TPC-DS benchmark tests, RelJoin can reduce query completion time by 21%, performs best in 62 out of 97 queries, and introduces a new metric, PSTS, to measure the optimization effectiveness of various join method selection strategies.

In summary, this paper aims to improve query performance on large-scale data processing platforms by optimizing the selection of distributed join methods through accurate cost models and adaptive statistics.