Principles and Common Optimization Techniques of the Spark SQL Optimizer

Introduction : Building on previous talks about Spark SQL parsing, analysis, and expression optimization, this article delves into the Spark SQL optimizer itself, describing its architecture and how its rule‑based engine transforms logical plans into more efficient execution plans.

Main Content Overview :

Spark SQL Optimizer Principles

Push‑down Optimization Ideas and Implementation

Operator Elimination and Merging

Expression Elimination and Replacement

Summary

1. Spark SQL Optimizer Principles : The optimizer is a rule engine that receives the analyzed logical plan and applies batches of optimization rules, producing an optimized logical plan. Since Spark 3.0, Adaptive Query Execution (AQE) extends the optimizer with additional rules for small‑partition merging, large‑partition splitting, and data‑skew handling.

2. Push‑down Optimization : Rules such as PushProjectionThroughUnion , PushProjectionThroughLimit , and PushDownPredicates move projection or filter operations before expensive operators (e.g., Union) to reduce shuffle volume. The article shows how PushProjectionThroughUnion rewrites the plan by inserting a Project node upstream of the Union, thereby cutting unnecessary data movement.

3. Operator Elimination and Merging : Examples include eliminating redundant Casts ( SimplifyCasts ), removing offsets, limits, outer joins, and collapsing redundant projects, repartitions, or windows. The article demonstrates offset elimination in three scenarios—zero offset, offset larger than the child output, and consecutive offsets that can be merged—showing the resulting simplified logical plans.

4. Expression Elimination and Replacement : Beyond Cast simplification, rules like EliminateWindowPartitions , EliminateAggregateFilter , OptimizeRepartition , BooleanSimplification , and ConstantPropagation replace costly expressions with cheaper equivalents or remove them entirely. The article explains how filter‑always‑true/false expressions can be pruned, and how hints (e.g., /*+Repartition*/ ) guide the optimizer to adjust partitioning.

5. Summary : The Spark SQL optimizer improves query performance by (1) eliminating or merging operators and expressions to lower computational overhead, and (2) applying rule‑based transformations on logical plans. Effective use of these patterns requires understanding both the optimizer’s rule set and the underlying data characteristics.