Dynamic parallelization in distributed join optimization

Distributed query processing

Figure 1 illustrates the distributed query processing workflow in a modern data processing engine. The process begins with the submission of a high-level SQL query by the user. Let us assume that we have students table with columns id, name and age and the following query is executed:

"Select name from students where age > 23"

The query is first parsed by the coordinator node, resulting in a raw logical plan. At this stage, the plan only reflects the syntactic structure of the query; no semantic resolution has yet been performed. In particular, the system does not yet know whether the table students exists or whether the referenced columns name and age are valid. The column types and table metadata are also not yet available. A sample raw plan may look like:

UnresolvedProject[name]

   UnresolvedFilter[age > 23]

     UnresolvedRelation[students]

Once the raw plan is generated, the analyzer component of the query engine is responsible for performing semantic resolution. During this phase, the system verifies the existence of the referenced table and columns by consulting the catalog. It also resolves attribute names to their fully qualified forms, determines data types, and ensures that all expressions are valid. The previously unresolved operators are replaced with their resolved counterparts.

For the running example, the analyzer will resolve the plan into the following initial logical representation:

Project[students.name]

    Filter[students.age > 23]

      Scan[students]

At this stage, all table and column references are fully qualified and valid. The plan is now semantically meaningful but still abstract in the sense that no concrete execution strategy has been chosen yet. It serves as the input for the logical optimizer, which will apply transformation rules to improve efficiency.

Once the logical plan is constructed, it is passed to the optimizer, which applies rule-based and cost-based transformations to improve execution efficiency. These may include predicate pushdown, projection pruning, and join reordering.

In distributed systems, the optimizer can use runtime statistics like output sizes or data skew to improve its decisions. Adaptive query engines may even change the plan during execution to match the actual data.

For our example, the optimizer might push down the filter and choose an efficient scan operator, producing a physical plan such as:

ProjectExec[name]

    FileScanExec students

     [PushedFilters: [IsNotNull(age), GreaterThan(age,23)]]

     [DataFilters: [age > 23]]

This plan serves as the basis for generating parallel execution tasks, which are dispatched across the cluster. At this stage, the system decides whether to use broadcast or shuffle for data exchange. It also selects concrete execution operators. For example, it chooses the join algorithm and scan method based on data size and format. These decisions complete the physical plan before execution begins.

Once execution starts, the plan is split into multiple tasks. Each task is assigned to a worker node. The coordinator node communicates with each worker to send task instructions and monitor their progress. For every task, there is a control flow between the coordinator and the corresponding worker.

As tasks run, they process their input partitions and produce partial results. These results may be shuffled, written to disk, or held in memory, depending on the execution plan. After all tasks complete, their outputs are collected by the coordinator. If further aggregation or sorting is required, final-stage tasks are executed. The final result is either written to storage or returned to the user.

Physical implementations in distributed join optimization

In distributed join optimization, a physical implementation specifies how a logical join executes within a distributed environment. It describes the mechanisms of data movement and the procedures for combining rows on each worker node. Each plan pairs a data exchange pattern such as broadcast (replicating a small input) or shuffle (repartitioning both inputs by the join key) with a local join algorithm such as hash, sort-merge, or nested loop. The optimizer selects among these options based on input sizes, data distribution, and available resources.

Broadcast replicates a small dataset to all workers, enabling local joins without shuffling the large side and thus reducing network cost when the small input fits in memory. In Fig. 2, L1, L2, and L3 are the large-input partitions that remain on their original workers, while S1, S2, and S3 (e.g., keys 1–3, 4–6, 7–9) are replicated to every worker for local processing.

Shuffle repartitions both inputs by the join key so that matching records colocate on the same partition. In Fig. 3, the exchange uses hash mode 3: each row is routed to partition $p=h(\mathrm{k}\mathrm{e}\mathrm{y})\mathrm{m}\mathrm{o}\mathrm{d}3$. Thus keys with the same remainder (e.g., $3,6,9,\dots$; $1,4,7,\dots$; $2,5,8,\dots$) are sent to the same worker, enabling local processing after the exchange. This scales to large inputs but incurs two-sided network and coordination costs and may degrade under key skew.

Hash, sort, and nested loop joins are the common local algorithms: hash join builds a hash table and probes (fast for equi-joins with sufficient memory), sort-merge join sorts both sides then merges (robust at very large scale or with pre-sorted data), and nested loop compares all pairs under a predicate (useful for small inputs or complex/non-equi predicates).

Cartesian product returns all row pairs when no join condition exists; it is typically impractical for large inputs due to quadratic output.

Broadcast Hash Join (BHJ). Broadcast the small input; each worker builds a hash table on it and probes with its local partition of the large input. The cost model for BHJ is given in Eq. (1).

(1) $$C_{\mathrm{B}\mathrm{H}\mathrm{J}}=(P\cdot S+L+R)+w\cdot S\cdot (P-1).$$where $P$ is the number of partitions, $S$ is the size of the small table, $L$ and $R$ are the sizes of the left and right tables, representing local scan/probe and result materialization costs, and $w$ weights communication relative to computation.

Shuffle Hash Join (SHJ). Hash-partition both inputs on the join key and shuffle so matching keys meet on the same partition; each worker builds and probes a per-partition hash table. The cost model for SHJ is given in Eq. (2).

(2) $$C_{\mathrm{S}\mathrm{H}\mathrm{J}}=(L+R+S)+w\cdot \frac{(L+R)(P-1)}{P}.$$where $P$ is the number of partitions, $L$ and $R$ are the sizes of the left and right tables, representing local scan/probe costs, $S$ is the smaller table size, and $w$ weights communication relative to computation. It is suitable for large equi joins when neither side is broadcastable; limited by two-sided shuffle and key skew.

Sort-Merge Join (SMJ). Shuffle both inputs by the join key, sort each partition locally, and merge the sorted streams to match equal keys. The cost model for SMJ is given in Eq. (3).

(3) $$C_{\mathrm{S}\mathrm{M}\mathrm{J}}=(L\cdot {\log }_2\frac{LR}{P}+R\cdot {\log }_2\frac{RR}{P}+L+R)+w\cdot \frac{(L+R)(P-1)}{P}.$$where $P$ is the number of partitions, $L$ and $R$ are the sizes of the left and right tables, representing local scan/probe costs, $LR$ and $RR$ are row counts, and $w$ weights communication relative to computation.

Broadcast Nested Loop Join (BNLJ). Broadcast a small input; each worker scans its local partition of the other input and evaluates the (possibly non-equi) predicate against all broadcast rows. The cost model for BNLJ is given in Eq. (4).

(4) $$C_{\mathrm{B}\mathrm{N}\mathrm{L}\mathrm{J}}=(B+R_{max}\cdot S)+w\cdot S\cdot (P-1).$$where $P$ is the number of partitions, $B$ is the size of the bigger input, $S$ is the size of the smaller table, $R_{max}$ is the row count of the bigger table, and $w$ weights communication relative to computation. It is suitable for small build sides and non-equi predicates; worst-case compute and memory growth with $S\cdot R_{max}$.

Cartesian Product Join (CPJ). Without a join condition, every row of one input pairs with every row of the other; engines may broadcast the small side or shuffle both. The cost model for CPJ is given in Eq. (5).

(5) $$C_{\mathrm{C}\mathrm{P}\mathrm{J}}=(B+\frac{R_{max}\cdot S}{P})+w\cdot \frac{(L+R)(P-1)}{P}.$$where $P$ is the number of partitions, $B$ is the size of the bigger table, $S$ is the size of the smaller table, $R_{max}$ is the row count of the bigger table, $L$ and $R$ denote size of the left and right tables, representing local scan/probe costs, and $w$ weights communication relative to computation. It is reasonable only for small inputs or explicit cross joins; the output grows with the product of input cardinalities.

Environmental setup

The experiments were conducted on a dedicated cluster consisting of one master node and four worker nodes. Each node was equipped with 16 CPU cores and 32 GB of main memory, providing sufficient resources to simulate realistic distributed query execution scenarios.

The software environment used Apache Spark 3.3.2 (Apache Software Foundation, 2023), compiled from source to integrate DPJoin into the Catalyst optimizer. The cluster ran on Hadoop 3.3.2 (Apache Software Foundation, 2022a) with Hadoop Distributed File System (HDFS) as the underlying shared-nothing storage layer.

We varied the replication factor across four levels (1–4) in HDFS, which are the only meaningful settings because the NameNode (master) does not allow a DataNode (worker node) to store more than one replica of the same block and placing duplicates on the same node violates HDFS block placement policies; thus, the effective maximum replication factor cannot exceed the number of worker nodes (Apache Software Foundation, 2022b).

The experimental study executes all TPC-DS queries end-to-end without excluding any operators; their full operator pipelines run as specified. However, our performance analysis focuses on join-related behavior, and we do not isolate or report the individual costs of non-join operators. This clarification ensures that the scope of our evaluation is consistent with the characteristics of TPC-DS and the objectives of our join-optimization study.

Preliminary analysis

Before implementing the full cost-based optimization model of DPJoin, we conducted a preliminary analysis to explore how replication level and degree of parallelism, affect query performance in a distributed environment. The aim of this analysis was twofold: first, to better understand how data locality and task coordination influence execution time, and second, to clarify how to integrate replication level and parallelism into cost model.

Effect of replication factor on query performance: Replication in distributed systems is traditionally used to improve availability and fault tolerance. However, it also has a substantial effect on query performance by influencing data locality, especially in distributed execution engines like Apache Spark. In Spark, the runtime metrics that quantify read locality are summarized in Table 1.

To investigate this, we conducted a series of experiments across 99 scan-and join-intensive queries on a 5-node Spark cluster (1 master, 4 workers), using scale factor 1 data (approximately 0.4 GB). We systematically varied the replication factor from 1 to 4. As illustrated in Fig. 4, increasing the replication factor led to a clear rise in LocalRead (MB) and a corresponding decrease in RemoteRead (MB). This behavior reflects the scheduler’s increased ability to place tasks on nodes where data is already replicated, thereby enhancing I/O locality and reducing costly inter-node data transfers.

To statistically validate the strength of this relationship, we conducted a Pearson correlation analysis between the replication factor and four key execution metrics: Local Processing, Remote Processing, LocalRead, and RemoteRead. The results, summarized in Fig. 5, reveal strong correlations: Local Processing ( $\rho =0.81$) and LocalRead ( $\rho =0.77$) both increase with replication, while Remote Processing ( $\rho =-0.81$) and RemoteRead ( $\rho =-0.75$) show negative correlations. These results reinforce the interpretation that increased replication enables better scheduling decisions, improving data locality and reducing network bottlenecks.

Collectively, these findings demonstrate that replication not only enhances availability but also serves as a powerful lever for query performance optimization. While excessive replication may introduce write and coordination overhead, its ability to improve read-locality makes it a valuable parameter to incorporate into cost-based join optimization models. Our empirical study thus highlights a critical trade-off that is often overlooked in current distributed query planners, particularly those like RelJoin (Liang et al., 2024) or ElasticJoin (Zhang, Zhang & Meng, 2025), which do not quantify replication-level effects in their cost estimations.

From these observations, we derive our first two preliminary findings:

Preliminary Finding 1 (PF1): Increasing the replication factor improves data locality and reduces the volume of inter-node transfers, suggesting that replication should be explicitly modeled to reduce network cost.

Preliminary Finding 2 (PF2): The statistically strong correlation between replication and execution metrics supports the integration of replication-awareness into analytical cost models. These findings provide a quantitative basis for developing cost estimators that are sensitive to the effects of data replication, especially in dynamic environments.

Effect of parallelism on average execution time: Parallelism is a key determinant of performance in distributed query engines. By increasing the number of concurrent tasks, systems like Apache Spark aim to reduce end-to-end query latency by exploiting intra-query parallel execution. However, the relationship between degree of parallelism (DOP) and execution time is not strictly linear, due to overheads associated with task scheduling, coordination, and data shuffling.

To empirically analyze this relationship, we executed 99 TPC-DS benchmark queries on a five-node cluster using scale factor 1 data (approximately 0.4 GB in total). For each configuration, we measured the average execution time across all queries. As shown in Fig. 6, increasing the DOP initially results in substantial reductions in query latency, demonstrating effective utilization of available computational resources. However, beyond a certain point, further increases in parallelism yield diminishing returns—and in some cases, slight regressions in performance. This observation aligns with prior findings that excessive parallelism should be curtailed in favor of locality and fairness, as demonstrated by the delay scheduling algorithm (Zaharia et al., 2010).

Excessive task parallelism can lead to overheads from increased scheduling effort, resource contention, and inefficient utilization of I/O and memory bandwidth. Therefore, while parallelism is a powerful lever for improving performance, it must be carefully tuned to match the system’s physical constraints (e.g., CPU cores, memory, disk throughput) and workload characteristics. This finding further motivates the need for dynamic and cost-based parallelism control, which we later address through the DPJoin optimizer’s runtime elasticity component.

We summarize the remaining two insights as follows:

Preliminary Finding 3 (PF3): Increasing the degree of parallelism does not always yield performance gains due to growing coordination and scheduling overheads. This observation is consistent with earlier analytical models of Spark partitioning, as demonstrated in Gounaris et al. (2017), where the authors formalized the existence of saturation points by analyzing how increasing the number of shuffle partitions initially improves parallelism but eventually leads to diminishing returns due to coordination and scheduling overhead.

Preliminary Finding 4 (PF4): A significant increase in task fragmentation and coordination messages was observed at higher parallelism levels, highlighting the need to incorporate coordination cost as an explicit factor in cost modeling. We see that this forms the foundation for designing parallelism-aware optimizers that dynamically adjust execution plans based on system capacity and workload intensity.

DPJoin algorithm

DPJoin is introduced as a planning-time optimizer that balances computation, communication, and coordination costs when selecting distributed join strategies. Unlike static approaches that fix the degree of parallelism or treat replication externally, DPJoin explicitly explores a grid of candidate parallelism levels for each feasible join method and detects the saturation point where additional parallelism no longer provides meaningful benefit. At this saturation point, the algorithm records the total cost of the method. Finally, it compares all methods at their own optimized saturation levels and selects the join strategy $m^{\ast }$ together with its parallelism $p^{\ast }$ that minimize the overall execution cost. This design ensures that DPJoin accounts for the trade-offs introduced by parallelism and replication, avoids over-parallelization, and produces robust plan choices tailored to distributed execution environments.

Algorithm inputs: cost models. For each join method $m\in \mathcal{M}$ and parallelism level $p$, the total cost is the sum of three components: computation, communication, and coordination as represented in Eq. (6).

(6) $$C_{\mathrm{t}\mathrm{o}\mathrm{t}\mathrm{a}\mathrm{l}}(p)=C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)+C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)+C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p).$$

These three components are instantiated differently depending on the join strategy, as described below.

Broadcast Hash Join (BHJ)

(7) $$C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)=\frac{S}{p},C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)=S\cdot (N-1)\cdot (1-\frac{r}{N}),C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)=O\cdot c.$$

The computation term( $C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}$) divides the build+probe work by $p$ to capture parallel speedup. The network term( $C_{\mathrm{n}\mathrm{e}\mathrm{t}}$) reflects broadcasting the small side $S$ to the other $N-1$ nodes, with replication reducing the fraction of remote transfers. The coordination term( $C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}$) grows linearly with the number of operators $O$, since each operator incurs an average operator cost $c$.

Shuffle Hash Join (SHJ)

(8) $$C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)=\frac{S+L}{p},C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)=\frac{S+L}{N}\cdot (N-1)\cdot (1-\frac{r}{N}),C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)=O\cdot c.$$

The computation cost scales with the total input size because both sides are partitioned and probed. The network term models the expected $(N-1)/N$ fraction of each partition that must be shuffled to remote nodes, with replication reducing this amount. The coordination term( $C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}$) grows proportionally with $O$, reflecting scheduling and driver–executor overheads.

Sort–Merge Join (SMJ)

(9) $$C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)=L\cdot {\log }_2(\frac{LR}{p})+R\cdot {\log }_2(\frac{RR}{p}),C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)=(S+L)\cdot (1-\frac{r}{N}),C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)=O\cdot c.$$

The computation term accounts for parallel sorting, where each partition processes $\frac{LR}{p}$ and $\frac{RR}{p}$ rows respectively, making sorting logarithmic in partition size. The network term captures the shuffle of both sides to colocate join keys, with replication reducing transfers. The coordination term again scales with the operator count.

Broadcast Nested Loop Join (BNLJ)

(10) $$C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)=B+R_B\cdot S,C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)=S\cdot (N-1)\cdot (1-\frac{r}{N}),C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)=O\cdot c.$$

The computation cost $C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)$ reflects the sum of the larger table size $B$ and the product of its row count $R_B$ with the smaller table size $S$. The communication cost $C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)$ models broadcasting the small side $S$ to the other $N-1$ nodes, adjusted by the replication factor $r$ over four nodes. The coordination cost $C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)$ is proportional to the operator count $O$, with $c$ denoting the per-operator overhead (e.g., 1.45 KB).

Cartesian Product Join (CPJ)

(11) $$C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)=N\cdot M,C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)=(L+R)\cdot (N-1)\cdot (1-\frac{r}{N}),C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)=O\cdot c.$$

The computation cost grows multiplicatively with the effective input size $M$ across $N$ nodes. The network term( $C_{\mathrm{n}\mathrm{e}\mathrm{t}}$) represents colocating both sides across nodes, with replication reducing the remote fraction. The coordination term( $C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}$) reflects per-operator overhead, scaling linearly with $O$ and $c$.

Algorithm output: selected strategy and parallelism. At the end of the optimization procedure, DPJoin produces two outputs: the best join method $m^{\ast }$ and its associated parallelism $p^{\ast }$ as illustrated in Eq. (12).

(12) $$m^{\ast }=\arg \underset{m\in \mathcal{M}}{min}{C}_m^{\ast },p^{\ast }=p_{m^{\ast }}^{\ast }$$

Here $C_m^{\ast }$ is the total cost of method $m$ measured at its saturation point $p_m^{\ast }$. The algorithm first scans all candidate parallelism levels $p\in P$ for each method $m$, identifies the saturation point where further increases yield negligible improvements, and records the corresponding total cost $C_m^{\ast }$. Among all methods, the one with the smallest $C_m^{\ast }$ is chosen as $m^{\ast }$, and the parallelism level fixed at $p^{\ast }$.

Algorithm description. The algorithm takes as input a set of feasible join methods and a monotone grid of parallelism levels. Its task is to determine, for each method, the parallelism level at which adding more tasks no longer yields meaningful improvements, and then to select the globally best method at saturation points.

The first step is to compute the adaptive convergence threshold ( $\epsilon$). We set $\epsilon =1/O$, where $O$ is the total number of operators in the logical plan. This ensures that complex queries require stronger evidence of improvement before allowing additional parallelism (see line 3 in Algorithm 1).

(13) $$\epsilon =\frac{1}{O}.$$

Here, $O$ denotes the total number of operators in the plan; it is computed by recursively summing all operators (scan, filter, project, join, etc.) contained in the subplan that includes the join.

The algorithm then begins its outer loop, iterating once for each candidate join strategy $m\in \mathcal{M}$ (line 4). For every strategy, it scans the parallelism grid $P=\{p_1,\dots ,p_K\}$ in increasing order (line 7). At each step, the three cost components are computation, communication, and coordination, and aggregated into the total cost, together with a scale-normalized mean that is easier to compare across different scales (lines 9–10).

(14) $$C_{\mathrm{t}\mathrm{o}\mathrm{t}\mathrm{a}\mathrm{l}}(p)=C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)+C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)+C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p),C_{\mathrm{m}\mathrm{e}\mathrm{a}\mathrm{n}}(p)=\frac{C_{\mathrm{t}\mathrm{o}\mathrm{t}\mathrm{a}\mathrm{l}}(p)}{3}.$$

Here, the three cost components are computed using the cost formulas defined for each join strategy: $C_{\mathrm{c}\mathrm{o}\mathrm{m}\mathrm{p}}(p)$ models per-partition computation based on input sizes and operator complexity; $C_{\mathrm{n}\mathrm{e}\mathrm{t}}(p)$ captures shuffle or broadcast communication as a function of data volume, replication level, and node count; and $C_{\mathrm{c}\mathrm{o}\mathrm{o}\mathrm{r}\mathrm{d}}(p)$ reflects the coordination overhead, computed from the total operator count and a fixed per-operator message cost. These strategy-specific cost definitions are provided in the corresponding cost model descriptions.

The key stopping rule is the saturation guard. Whenever the improvement between consecutive parallelism levels becomes negligible when the mean cost difference falls below the adaptive threshold, the algorithm halts the scan for that method and records its current parallelism and cost (lines 11–12).

(15) $$|C_{\mathrm{m}\mathrm{e}\mathrm{a}\mathrm{n}}(p)-C_{\mathrm{m}\mathrm{e}\mathrm{a}\mathrm{n}}(p^{-})|<\epsilon \Rightarrow (p_m^{\ast },C_m^{\ast })\mathrm{f}\mathrm{i}\mathrm{x}\mathrm{e}\mathrm{d}\mathrm{a}\mathrm{t}p.$$

Here $p^{-}$ denotes the previous grid point. This condition prevents unnecessary over-parallelization once the efficiency gains have saturated.

At the same time, a robustness guard tracks the best mean value encountered so far (lines 13–14). If the saturation guard never triggers, for instance when the cost curve decreases very smoothly, the algorithm falls back to the parallelism level that minimized the mean cost (line 16).

(16) $$p_m^{\ast }=\arg \underset{p\in P}{min}{C}_{\mathrm{m}\mathrm{e}\mathrm{a}\mathrm{n}}(p),C_m^{\ast }\mathrm{t}\mathrm{a}\mathrm{k}\mathrm{e}\mathrm{n}\mathrm{a}\mathrm{t}{p}_m^{\ast }.$$

Once all strategies have been processed independently, the algorithm proceeds to global selection. It compares the total costs recorded at each method’s saturation point and returns the method–parallelism pair with the overall lowest cost (line 18):

(17) $$m^{\ast }=\arg \underset{m\in \mathcal{M}}{min}{C}_m^{\ast },p^{\ast }=p_{m^{\ast }}^{\ast }.$$

In effect, lines 3–18 describe a two-stage process that governs the optimization logic of DPJoin. First, each candidate method is optimized locally. In this stage, the algorithm systematically scans the available parallelism grid, starting from the lowest level of parallelism and gradually increasing the degree until a saturation point is reached or a predefined fallback condition triggers termination. The idea is to explore the efficiency frontier of each method individually, so that DPJoin can observe how costs evolve with higher degrees of parallelism. This local search is not arbitrary: it explicitly monitors the interaction between computation cost, communication overhead, and coordination complexity. Rather than relying on static assumptions or default partition counts, the algorithm evaluates how performance behaves in practice as the parallelism level changes.

Second, once local optimization has been completed for all candidate strategies, DPJoin performs a global comparison across the outcomes. Here, the optimizer identifies the best method among the locally optimized candidates, ensuring that the final plan reflects not only the intrinsic behavior of each strategy but also the relative advantages across the entire set. This global decision step is critical: it prevents the optimizer from being biased towards a method that looks efficient under narrow conditions but is suboptimal when compared more broadly. For instance, a shuffle-based join might appear strong when considered in isolation, but when its high coordination cost is contrasted with the communication efficiency of a broadcast join, the balance may shift. By incorporating this second decision layer, DPJoin integrates both depth (detailed within-strategy exploration) and breadth (comparative across-strategy evaluation) into its optimization logic.

Together, these two stages, local exploration followed by global selection, give DPJoin the ability to handle the multidimensional trade-offs of distributed query processing. Unlike traditional optimizers that rely heavily on static cardinality estimates or fixed cost coefficients, DPJoin adapts its choices to the actual structure and dynamics of the workload. It balances computation, communication, and coordination factors in a principled way. At the same time, it avoids the common pitfall of over-parallelization, which can waste resources, create scheduling bottlenecks, and increase latency. By explicitly controlling parallelism levels, DPJoin ensures that additional tasks are created only when they improve throughput, not when they merely inflate scheduling overhead. This makes the approach resilient against inefficiencies that often arise in large clusters. Issues such as task granularity, shuffle imbalances, or stragglers can quickly offset any theoretical gains from fine-grained partitioning, but DPJoin is able to mitigate these risks.

Moreover, the design acknowledges heterogeneity: in real distributed systems, resources are often uneven, workloads fluctuate, and runtime conditions deviate from initial estimates. By accounting for such variability, DPJoin produces plan choices that are not only theoretically optimal but also practically robust. This robustness is particularly important for modern engines such as Spark or Presto, where adaptive query execution is essential to sustain performance under diverse workloads. The two-stage process also provides a natural foundation for future extensions. For example, additional cost factors such as memory pressure, I/O contention, or skew-handling mechanisms can be incorporated into the local optimization phase without changing the overall structure.

Integration of DPJoin into apache spark

To operationalize the DPJoin strategy and its underlying cost model, we integrated it into the physical planning stage of Apache Spark’s Catalyst optimizer. Specifically, we developed a new custom Strategy class named DPJoin, which extends Spark’s native join planning logic by introducing adaptive, cost-aware decision making at runtime.

The DPJoin strategy is invoked during the physical planning phase and intercepts logical Join nodes. When such a node is detected, the system first checks for equi-join keys and evaluates whether existing strategies (e.g., BroadcastHashJoin, ShuffleHashJoin, SortMergeJoin, Broadcast Nested Loop Join, CartesianProduct) are applicable. For each feasible strategy, the integrated CostModel is queried to compute execution costs using the four proposed findings (PF1, PF2, PF3, PF4), which stem from our preliminary analysis. These findings capture different aspects of query complexity namely input size, data movement, compute, coordination, and saturation overhead and serve as guiding signals for the optimizer.

Based on the computed costs, the planner identifies the strategy with the minimum estimated execution time. The selected strategy is then instantiated via Spark’s native SparkPlan constructs (e.g., BroadcastHashJoinExec, SortMergeJoinExec, etc.), and the optimal number of shuffle partitions is assigned dynamically by modifying the spark.sql.shuffle.partitions configuration entry within the same session (Ninan, 2023).

To enable fine-grained elasticity control, the implementation further includes a saturation estimation mechanism based on the number of join operators in the logical plan. A query complexity factor is computed based on the number of join operators (JoinCount) in the plan. This value is used to adaptively compute the convergence threshold $\epsilon =\frac{1}{O}$, enabling more fine-grained cost comparisons for complex queries. This value controls the granularity of performance estimation and acts as a sensitivity factor during the cost comparisons. More complex queries (i.e., those with more joins) trigger finer-grained comparisons, helping avoid suboptimal decisions in heavily parallel plans.

Additionally, the planner logs detailed runtime metadata in JSON format within Spark’s SparkConf object. These logs contain per-query metrics, such as data sizes, row counts, selected strategies, estimated costs, and partition counts. This feature facilitates traceable experimentation and debugging across multiple runs and queries.

Overall, the integration of DPJoin into Catalyst is modular and non-invasive. It maintains full compatibility with Spark’s optimizer API while offering advanced, dynamic decision-making capabilities that account for both system-level constraints and query-specific complexity.

Experimental design

To systematically evaluate the effectiveness and robustness of the proposed DPJoin strategy, we designed three complementary experiments that target different aspects of distributed join optimization. All experiments were conducted on a 5-node Spark cluster under varying data scales and replication levels. For each configuration, every query was executed three times consecutively to observe how effectively each strategy leveraged data locality, and the average execution time was used for comparison. To ensure comparable block distributions across scales, the HDFS block size was set to 16 MB for Scale 1, 64 MB for Scale 10, and 128 MB for Scale 50. The experiments are:

• 1.

  Robustness of join strategies under varying replication levels.

  This experiment analyzes how five different join strategies; Shuffle Hash Join (SHJ), Shuffle Sort Join (SSJ), Spark’s Adaptive Query Execution (AQE), RelJoin, and DPJoin respond to decreasing replication factors. For scale factor 1, 10 and 50 datasets, we varied the replication level from 1 to 4 and executed all benchmark queries 3 consecutive times per setting. For each query, the strategy with the lowest average execution time was marked as the winner. We then counted the number of queries where each strategy outperformed the others, allowing us to evaluate the tolerance of each strategy to replication loss. This analysis helps quantify how robust each approach is when data locality deteriorates due to limited data replication.

• 2.

  Resource efficiency: task counts and shuffle traffic

  To assess resource efficiency independently of query complexity, we evaluate two orthogonal proxies: (i) task-level parallelism (the total number of tasks produced by a plan) and (ii) shuffle traffic (sum of shuffle read and write, in MB). We fix the replication factor to $RF=4$ and compute these metrics per strategy over the 99 TPC-DS queries (averaging multiple runs when applicable). For task-level parallelism, we report per-query series to reveal fragmentation and coordination costs; for shuffle, we report the average volume across queries to capture network I/O pressure. Together, these measurements provide an apples-to-apples view of how each strategy uses resources—distinguishing cost-aware planning from shuffle-heavy execution.

• 3.

  Join strategy execution time analysis across data scales

  In this section, we analyze how the execution-time performance of five join strategies—Shuffle Hash Join, Shuffle Sort Join, Adaptive Query Execution (AQE), RelJoin, and DPJoin—changes as data replication decreases. The evaluation is based on TPC-DS benchmark results executed over datasets of scale factor 10 and 50, with replication levels ranging from 4 to 1. Each query was run three times per setting to obtain stable average runtimes.

  We analyzed the results using two key metrics: (1) Winner count: For each query, the strategy with the lowest average execution time across repetitions was marked as the winner. We then counted how many times each strategy outperformed the others. (2) Average execution time: We computed the mean runtime for each strategy across all queries and replication settings.

  These two metrics jointly capture both the consistency and overall efficiency of each join strategy under changing replication levels. Winner Count reflects how often a strategy is the fastest across diverse queries, while Average Execution Time summarizes its general performance. Together, they provide a clear view of how replication reductions influence the relative behavior of the join algorithms.

Experimental results

Robustness of join strategies under varying replication levels. The first analysis investigates how different join strategies perform under decreasing replication levels. The replication factor (RF) was varied from 4 to 1, while the experiments were repeated across three data scales ( $SF=1,10,50$, where $SF$ denotes the scale factor). The aim is to evaluate the robustness of each strategy in the face of limited data locality. In each configuration ( $RF=4,3,2,1$), all TPC-DS benchmark queries were executed three times consecutively. The average execution time was computed per query, and the strategy with the lowest average was considered the winner for that query. This evaluation was conducted for five strategies: ShuffleHashJoin, ShuffleSortJoin, Adaptive Query Execution (AQE), RelJoin, and the proposed DPJoin. The reported metric is the number of queries in which a given strategy outperformed all others. Additional comparisons across different scale factors allow us to observe how strategies react not only to reduced replication but also to growing dataset sizes. These observations provide early insights into which strategies are more resilient to both limited data locality and increased workload demands.

Small scale (Scale 1). In Fig. 7, DPJoin delivers the lowest overall execution times across all replication settings, with AQE following closely. RelJoin shows stable behavior and remains more consistent than the shuffle-based strategies. ShuffleHashJoin and ShuffleSortJoin exhibit higher variability when replication decreases, reflecting their sensitivity to data locality and shuffle overhead. By contrast, DPJoin, AQE, and RelJoin maintain relatively stable trends, benefiting from their lower shuffle intensity and more locality-aware planning.

Medium scale (Scale 10). In Fig. 8, DPJoin achieves the best overall performance, maintaining the lowest execution times across replication settings, though it still incurs some overhead as replication increases.

AQE follows closely, showing stable behavior, while RelJoin provides moderate but reliable performance. ShuffleHashJoin and ShuffleSortJoin degrade sharply, particularly at higher replication levels. This is because additional replicas introduce extra coordination and shuffle traffic, which amplify network and memory overhead.

Large scale (Scale 50). At the largest scale in Fig. 9, AQE achieves the best results, consistently delivering the lowest execution times across all replication factors. DPJoin follows closely, maintaining lower execution times than RelJoin and shuffle-based methods, but it does not surpass AQE at this scale. RelJoin shows moderate performance, remaining more stable than shuffle-heavy strategies. ShuffleHashJoin and ShuffleSortJoin show sharp changes in execution time as replication levels decrease, indicating that they are more sensitive to limited data locality and coordination overhead.

The results across all three data scales reveal how replication levels influence different join strategies, although the magnitude and direction of the effect vary by scale. Shuffle-based strategies, ShuffleHashJoin and ShuffleSortJoin, tend to exhibit higher sensitivity under reduced replication, especially at larger scales, where lower data locality can amplify shuffle and coordination overhead. At smaller scales, the trend is less pronounced, and execution times may fluctuate rather than consistently increase as replication decreases. RelJoin, DPJoin, and AQE show greater resilience across replication settings, benefiting from either replication-aware costing or adaptive planning mechanisms that help mitigate data movement overhead. RelJoin offers moderate stability but lacks adaptability beyond a certain threshold, while DPJoin and AQE maintain robust performance patterns across scales. While DPJoin achieves strong results at smaller scales ( $SF=1,10$), AQE delivers the best performance at the largest scale ( $SF=50$), reflecting its ability to incorporate runtime adaptivity and maintain efficiency as data size grows.

Resource efficiency: task counts and shuffle traffic.

Parallelism view. Figure 10 shows that DPJoin uses the fewest tasks across nearly all queries, i.e., it chooses the lowest effective degree of parallelism.

At this small scale (SF = 1, RF = 4), limiting task count prevents using more tasks than necessary and reduces scheduler/coordination overhead, shuffle overhead, and memory pressure, which are the costs that dominate when data volumes are modest. AQE and RelJoin employ moderate parallelism, while ShuffleHashJoin and ShuffleSortJoin create many more tasks, which can inflate coordination and shuffle costs without proportional speedups. Overall, DPJoin’s replication-aware costing steers it toward a conservative yet sufficient parallelism level, yielding a more resource-efficient execution plan.

Shuffle overhead by strategy.

Figure 11 shows a stark separation between shuffle-based and cost-aware approaches. Shuffle Sort and Shuffle Hash incur the largest data movement ( $\sim$1,548 and $\sim$1,544 MB on average), which is about 6–8 $\times$ higher than the others. Among the cost-aware methods, AQE yields the lowest shuffle volume ( $\sim$193 MB), followed by RelJoin ( $\sim$242 MB) and DPJoin ( $\sim$253 MB). Lower shuffle read+write correlates with fewer coordination stalls and more stable runtimes, whereas the shuffle-heavy strategies pay substantial network and scheduling overhead without commensurate gains.

Join strategy execution time analysis across data scales.

The Fig. 12 above shows execution time per query under Scale 10 with replication factor 4. Join strategies were evaluated under varying data scales and replication factors using two metrics: the number of fastest executions (i.e., wins) and the average execution time. We first analyze the per-query distribution (Fig. 12), and then summarize the outcomes in Table 2.

The results clearly indicate that DPJoin outperforms all competing strategies across all replications and both scales. In Scale 1, DPJoin wins 80 out of 99 queries at replication factor 4. Similarly, in Scale 10, it maintains dominance with 81 wins under the same replication level. While AQEJoin shows moderate performance, it falls significantly behind DPJoin. Traditional strategies such as RelJoin, ShuffleHashJoin, and ShuffleSort consistently exhibit low win rates, often failing to adapt to the increasing replication and data scale.

Table 3 above shows that DPJoin wins more frequently and achieves the lowest average execution times.

Figure 13 above illustrates execution time per query for each join strategy at Scale 50 with replication factor 4. Compared to the earlier analysis at Scale 10, a general upward shift in execution times is clearly visible across all strategies, as expected due to the larger data volume. This increase confirms the sensitivity of join performance to input size and highlights the importance of scalable join strategies. As summarized in Table 4, AQE achieves the highest number of fastest executions across all replication levels, consistently outperforming other strategies at this scale. While DPJoin remains competitive and secures a substantial number of wins, its relative advantage diminishes as data size grows, indicating that AQE adapts more effectively under large-scale conditions.

At Scale 50, AQE achieves the highest number of fastest executions across all replication levels, clearly outperforming DPJoin. RelJoin shows moderate success, while ShuffleHashJoin and ShuffleSortJoin remain marginal with only a few wins.

When we examine the average execution times, a clear trend emerges, as shown in Table 5. AQE consistently achieves lower runtimes than DPJoin across all replication factors. For instance, at replication factor 3, AQE records an average of 8,715.0 ms, compared to DPJoin’s 10,246.5 ms. This indicates that at large scale, AQEJoin not only wins more queries but also executes them more efficiently on average, whereas DPJoin’s relative advantage diminishes as data size increases.

Table 6 reports the standard deviation of execution times across repeated runs for each strategy. DPJoin shows consistently lower variability at both scales, indicating more stable and predictable performance. In contrast, ShuffleHashJoin and ShuffleSortJoin exhibit substantially higher variance, especially at Scale 10, suggesting reduced stability and greater sensitivity to increasing data size.

As shown in Table 7, execution-time variability increases substantially at Scale 50, particularly for the ShuffleHashJoin and ShuffleSortJoin strategies. DPJoin maintains comparatively lower variability across all repetitions, supporting the stability trend previously observed in Table 6. The extremely high deviation values in Rep. 3 for both shuffle-based strategies indicate significant performance instability under large-scale data processing.

In general, the variability patterns observed across the experiments show that different join strategies respond differently to increasing data scale. While some methods maintain relatively steady behavior, others exhibit higher dispersion as the workload grows. These results suggest that performance stability is influenced by both the underlying join mechanism and the scale of the input, highlighting the importance of considering variability—not only mean execution time—when evaluating distributed query processing approaches.

As shown in Fig. 14, DPJoin performs well at scale 1 and scale 10, where its relatively low level of parallelism does not place significant pressure on executor memory. At these smaller data sizes, the amount of intermediate data remains manageable and GC time stays low. The situation changes at scale 50. Because DPJoin continues to operate with the same low parallelism while the input volume increases substantially, more intermediate results accumulate in executor memory. This accumulation increases memory pressure and leads to a much higher garbage collection time. In other words, DPJoin becomes affected by large data sizes because it does not take system resources, particularly executor memory, into account when determining how much parallelism to use. A more resource aware design would increase parallelism when the data size grows and prevent the rise in memory usage that causes the GC overhead observed at scale 50.