Adaptive Query Execution in Spark: What AQE Does and How to Tune It
AQE re-plans your query while it runs, using real numbers instead of estimates. Here are its three optimisations, every setting that controls them, and the default that quietly ignores your tuning.
Before Spark 3.0, the query plan was fixed before the job started. Spark estimated how much data it would handle, chose a plan, and stuck with it — even when those estimates turned out to be badly wrong.
Adaptive Query Execution changes that. It re-plans the query while it runs, using the actual data instead of guesses.
It is on by default and it fixes a great deal for free. This article covers what it actually does, every setting that controls it, and the one default that quietly cancels out your tuning.
Why runtime planning matters
Spark’s optimiser plans a query before any data has moved. To do that it estimates how big each intermediate result will be — and those estimates come from table statistics, which are often stale, missing, or impossible to compute.
Then a filter removes 99% of the rows, or a join key turns out to be badly distributed, and the plan that looked sensible is now wrong. Without AQE, Spark runs it anyway.
AQE works because a shuffle is a natural checkpoint. Every task must finish writing before the next stage can read, so at that moment Spark knows exactly how big everything really is. AQE uses that pause to re-plan what remains.
Break the query into stages, split at each shuffle.
Execute a stage and wait for it to complete.
Collect real statistics — actual partition sizes, actual row counts.
Re-optimise the remaining plan using those numbers.
Repeat at the next shuffle.
This is also why AQE only helps queries that have a shuffle. A job that reads, filters and writes gives it nothing to work with. See Spark Shuffle Explained for why stage boundaries land where they do.
Every shuffle is a checkpoint. AQE uses the pause to re-plan the rest of the query with real numbers.
Is it on?
spark.conf.get("spark.sql.adaptive.enabled")# 'true' on Spark 3.2 and laterspark.conf.set("spark.sql.adaptive.enabled", "true")
AQE arrived in Spark 3.0 and has been enabled by default since 3.2. On Databricks it is on. Everything below assumes it is.
The three things AQE does
Optimisation
Problem it solves
1. Coalescing post-shuffle partitions
Too many tiny partitions after a shuffle
2. Converting sort-merge to broadcast
A join side that turned out small at runtime
3. Skew join optimisation
One partition far larger than the rest
1. Coalescing post-shuffle partitions
The setting spark.sql.shuffle.partitions decides how many partitions a shuffle produces, and it defaults to 200 regardless of your data volume. That number is wrong at both extremes — enormous partitions on a big dataset, and 200 near-empty tasks on a small one.
AQE fixes the second case. After the shuffle it looks at the real partition sizes and merges small adjacent ones together into fewer, properly sized partitions.
Because of this, leaving spark.sql.shuffle.partitions high is now reasonable — it becomes a starting point that AQE coalesces down from, and more starting partitions give it finer control.
AQE merges small adjacent partitions after the shuffle, so 200 tiny tasks become a handful of right-sized ones.
Setting
Default
What it does
spark.sql.shuffle.partitions
200
Starting partition count. AQE coalesces down from it
This one catches people out, and it is worth reading twice.
You set advisoryPartitionSizeInBytes to 128 MB. You re-run the job. Nothing changes. The partitions are still small.
The reason is parallelismFirst, which defaults to true. While it is on, AQE ignores your advisory size entirely and instead calculates its own target from the cluster’s parallelism — usually a much smaller number. The setting you carefully tuned is not being read.
That default exists for a defensive reason: it maximises parallelism so that switching AQE on never makes an existing job slower. But it means the advisory size is inert until you turn it off.
# Make AQE actually respect your target partition sizespark.conf.set("spark.sql.adaptive.coalescePartitions.parallelismFirst", "false")spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "134217728") # 128 MB
Spark’s own documentation recommends setting this to false and respecting the configured target size. You will find plenty of guidance saying to leave it at true — that advice keeps the safety net but also keeps the advisory setting switched off.
The practical rule: if you are not tuning partition sizes, leave everything alone. If you are, set parallelismFirst to false first, or you are tuning a value nothing reads.
With parallelismFirst on, your advisory partition size is never read. That is why tuning it appears to do nothing.
One other consequence worth knowing: coalescing reduces the number of output partitions, and therefore the number of files written. That makes AQE a partial defence against the small files problem.
2. Converting a sort-merge join to a broadcast join
Spark decides whether to broadcast a table before running anything, based on estimated size. When the estimate is missing or stale it plans a sort-merge join — shuffling both sides — even if one side is tiny.
AQE gets a second look. Once the first stage completes, it knows the real size of each side. If one is small enough, it switches the join to a broadcast hash join mid-query and skips the remaining shuffle entirely.
This is the optimisation that catches filtered data. A 5 TB table filtered down to 8 MB still looks like 5 TB to the static planner. AQE sees 8 MB.
Setting
Default
What it does
spark.sql.autoBroadcastJoinThreshold
10 MB
Size limit for broadcasting a table
spark.sql.adaptive.autoBroadcastJoinThreshold
not set
AQE-specific limit. Falls back to the above when unset
The second setting exists so you can be more generous at runtime than at plan time — a runtime measurement is real, where a plan-time estimate is a guess, so you may reasonably trust a larger number.
# Let AQE broadcast up to 100 MB, while static planning stays at 10 MBspark.conf.set("spark.sql.adaptive.autoBroadcastJoinThreshold", str(100*1024*1024))
Raise it carefully. A broadcast table is assembled in driver memory before being copied to every executor, and the size Spark measures is not the size it occupies in memory — both covered in Broadcast Join vs Bucketing.
Planned as a sort-merge join, converted to a broadcast join once AQE sees the real size.
3. Skew join optimisation
AQE detects partitions that are far larger than the rest and splits them into smaller sub-partitions spread across more tasks, so one straggler no longer holds up the stage.
The critical detail is that a partition is only treated as skewed when it satisfies both conditions:
Larger than skewedPartitionThresholdInBytes (256 MB), and
Larger than skewedPartitionFactor × the median partition size.
Both, not either. So a 200 MB partition sitting among 2 MB partitions is 100 times the median and still ignored, because it never crosses 256 MB. This is why lowering just one setting often changes nothing.
AQE is often described as making tuning obsolete. It is not, and knowing the gaps saves real time.
It does not handle skew in aggregations. Skew join optimisation applies to joins. A skewed groupBy still needs salting or a reshaped key.
It does not engage below its thresholds. A partition under 256 MB is never treated as skewed, however lopsided the distribution.
It cannot help without a shuffle. No shuffle means no checkpoint, no statistics, and nothing to re-plan.
It does not reduce data volume. Filtering early and selecting fewer columns still matter.
It does not fix data layout. Too many small files or bad partitioning are write-time problems.
It does not prevent spill or OOM. It reduces both by sizing partitions better, but an oversized working set still fails — see Spark Spill to Disk and Fix Spark Executor OOM.
The honest recommendation for most workloads: change nothing. The defaults are good, and AQE’s value is that it adapts without being told. The two worth touching are parallelismFirst when you genuinely need larger partitions, and the skew thresholds when your partitions are naturally smaller than 256 MB.
Quick reference
Question
Answer
What is AQE?
Re-planning the query at runtime using real statistics instead of estimates
Is it on?
Yes, by default since Spark 3.2
What does it do?
Coalesces small partitions, converts joins to broadcasts, splits skewed partitions
When does it act?
At each shuffle boundary, where real sizes become known
Do I still tune shuffle partitions?
Not usually. AQE coalesces down from whatever you set
Why did my advisory size do nothing?
parallelismFirst is true and ignores it. Set it false
Why is my skew not being fixed?
The partition must exceed both 256 MB and 5× the median
Does it fix groupBy skew?
No. Joins only
Does it work without a shuffle?
No. It needs a stage boundary to measure at
Summary
AQE re-optimises the query while it runs, using real statistics collected at each shuffle boundary.
It does three things: coalesces small post-shuffle partitions, converts sort-merge joins to broadcast joins when a side turns out small, and splits skewed partitions.
It is enabled by default from Spark 3.2 onward and needs no configuration for most workloads.
parallelismFirst defaults to true, which makes AQE ignore advisoryPartitionSizeInBytes completely. Set it false before tuning partition size, or you are tuning a value nothing reads.
Skew handling requires a partition to exceed both 256 MB and 5× the median. Lowering one setting alone often changes nothing.
AQE does not handle aggregation skew, does not work without a shuffle, and does not reduce the amount of data you asked for.
Frequently Asked Questions
What is Adaptive Query Execution in Spark?
AQE is a Spark SQL feature that re-optimises a query while it is running. At each shuffle boundary it collects real statistics — actual partition sizes and row counts — and adjusts the remaining plan accordingly. It replaces plan-time estimates, which are often stale or missing, with measurements from the data itself.
Is AQE enabled by default?
Yes, since Spark 3.2, and it is on in Databricks runtimes. It was introduced in Spark 3.0 but had to be enabled manually at first. Check with spark.conf.get("spark.sql.adaptive.enabled").
Do I still need to set spark.sql.shuffle.partitions with AQE?
Usually not. The setting still defines how many partitions the shuffle initially produces, but AQE then merges small ones down to a sensible number. Leaving it at 200 is fine, and raising it is also reasonable — more starting partitions give AQE finer granularity to coalesce from.
Why does advisoryPartitionSizeInBytes have no effect?
Because spark.sql.adaptive.coalescePartitions.parallelismFirst defaults to true, and while it is on AQE ignores your advisory size and calculates its own smaller target from cluster parallelism. Set it to false and the advisory value is respected. Spark’s own documentation recommends this.
Does AQE fix data skew automatically?
Often, but not always. A partition is only treated as skewed if it is both larger than 256 MB and more than 5 times the median partition size. A 200 MB partition among 2 MB partitions is 100 times the median and still ignored. AQE’s skew handling also applies only to joins, so a skewed groupBy still needs salting.
What is the difference between the two broadcast threshold settings?
spark.sql.autoBroadcastJoinThreshold applies when Spark plans the query, using estimated sizes, and defaults to 10 MB. spark.sql.adaptive.autoBroadcastJoinThreshold applies when AQE re-plans at runtime using measured sizes. It is unset by default and falls back to the first value, but you can set it higher since a runtime measurement is more trustworthy than an estimate.
Does AQE work without a shuffle?
No. AQE re-plans at stage boundaries, and stage boundaries are created by shuffles. A job that only reads, filters and writes has no shuffle, so there is no point at which AQE can measure anything or change the plan.
Can AQE make a query slower?
Rarely, and the defaults are chosen to avoid it — parallelismFirst exists precisely to stop AQE reducing parallelism and slowing an existing job. There is a small overhead in collecting statistics and re-planning, which is negligible on anything but very short queries.
