Old Spark optimizers guess the best plan using fixed data collected before running. When those fixed data are missing, stale, or inaccurate a common reality with complex pipelines, selective filters, UDFs, or skewed data the optimizer can choose a suboptimal plan. The result? Slow joins, too many tiny tasks, or one task that runs forever because of data skew.
Adaptive Query Execution (AQE) solves this by re-optimizing the query plan during execution using accurate runtime statistics. Introduced in Apache Spark 3.0 and available (and enabled by default) in Databricks Runtime, AQE turns query planning from a one-time decision into a continuous, data-driven process.
Why AQE Matters
At the end of every shuffle or broadcast exchange (called a query stage), Spark has real statistics about partition sizes and row counts. AQE uses these fresh numbers to:
- Switch to a better physical strategy
- Adjust the number and size of partitions
- Handle skew automatically
- Propagate empty relations
This is especially powerful when compile-time estimates are wrong — which is frequent in real-world workloads.
Core Capabilities of AQE
AQE currently delivers four major dynamic optimizations (enabled by default in Databricks):
1. Dynamically convert Sort-Merge Join → Broadcast Hash Join
If runtime statistics show that one side of a join is small enough (default threshold 30 MB in Databricks), AQE switches from an expensive sort-merge join to a broadcast hash join. This can deliver dramatic speedups.
2. Dynamically coalesce shuffle partitions
Setting a high number of shuffle partitions (spark.sql.shuffle.partitions) is often necessary for large data, but it creates many tiny tasks that waste scheduler overhead and reduce I/O efficiency. AQE automatically merges adjacent small partitions into reasonably sized ones (target around 64 MB by default). Fewer, better-sized tasks improve throughput and reduce resource waste.
3. Dynamically handle data skew in joins
Skewed partitions (one partition much larger than the others) used to cause stragglers. AQE detects skewed partitions after the shuffle and splits them into roughly equal-sized tasks (replicating the matching side when needed). Both sort-merge and shuffle-hash joins are supported.
4. Dynamically detect and propagate empty relations
If a relation turns out to be empty at runtime, AQE can short-circuit large parts of the plan, avoiding unnecessary work.
These optimizations apply to non-streaming queries that contain at least one exchange (joins, aggregations, window functions) or a subquery. Support for Structured Streaming via foreachBatch was added later (Databricks Runtime 13.1+).
How AQE Works Under the Hood
- The query starts with the statically planned stages.
- Leaf stages execute and materialize their results.
- AQE collects accurate runtime statistics from completed stages.
- It re-runs selected optimizer and planner rules with the new statistics.
- New stages are launched based on the updated plan.
- The process repeats until the entire query finishes.
You can observe this live in the Spark UI, the plan evolves, and you will see AdaptiveSparkPlan nodes. After completion, isFinalPlan becomes true. Calling df.explain(true) also shows both the initial plan and the final adaptive plan, along with runtime statistics (isRuntime=true).
Enabling and Configuring AQE
In modern Databricks Runtime, AQE is on by default. The main Databricks-specific toggle is:
spark.conf.set("spark.databricks.optimizer.adaptive.enabled", "true")
Useful related configurations:
| Configuration | Default | Purpose |
| spark.sql.adaptive.coalescePartitions.enabled | true | Enable partition coalescing |
| spark.sql.adaptive.advisoryPartitionSizeInBytes | 64MB | Target size after coalescing |
| spark.sql.adaptive.skewJoin.enabled | true | Enable skew join handling |
| spark.sql.adaptive.skewJoin.skewedPartitionFactor | 5 | Factor × median size to detect skew |
| spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes | 256MB | Minimum size to consider a partition skewed |
| spark.databricks.adaptive.autoBroadcastJoinThreshold | 30MB | Threshold for runtime broadcast conversion |
| spark.sql.shuffle.partitions | 200 (or auto) | Initial shuffle partitions; set to auto for auto-optimized shuffle |
A partition is treated as skewed only when both conditions are met: size > factor × median and size > the byte threshold.
Best Practices
- Prefer AQE’s automatic skew handling over the older skew join hint — it is generally more effective and requires no manual intervention.
- You can still use broadcast join hints when you know a side is small. A static broadcast plan is often faster than waiting for AQE to discover the size after a shuffle.
- AQE does not currently perform dynamic join reordering.
- Ensure your queries have shuffle boundaries if you want AQE to act — pure filter/project pipelines give it nothing to work with.
- For foreachBatch streaming, configure AQE settings on the Spark session; support exists from DBR 13.1/13.2 onward.
- Monitor the Spark UI and explain output to confirm which adaptive rules fired (look for CustomShuffleReader with Coalesced, isSkew=true, or changed join types).
Real-World Impact
Early TPC-DS results showed up to 8× speedup on individual queries, with many queries gaining >10%. Production workloads that combine skew, varying data sizes, and complex joins often see even larger gains. When combined with Photon, the benefits compound further.
Conclusion
Adaptive Query Optimization removes much of the guesswork and manual tuning that used to be required for high-performance Spark SQL. By continuously refining the plan with real data statistics, AQE makes Databricks pipelines more resilient to data changes, skew, and imperfect statistics.
If you are still relying solely on static plans or heavy use of hints, enable AQE (it is already on in modern runtimes) and inspect your query plans. In many cases you will discover that Spark is already making smarter decisions than the original plan suggested automatically, at runtime.
That is the real power of Adaptive Query Optimization in Databricks.