# What does adaptive query execution change in a Spark query, and how do you see it in the plan?

Spark's way of re-planning a query while it runs: after each shuffle it looks at the real sizes, then merges small partitions, splits skewed ones and can switch a join to a broadcast.

- Page: https://tecyfy.com/concepts/adaptive-query-execution
- Subject: Data engineering in the AI era (https://tecyfy.com/concepts)
- Level: advanced
- Also called: AQE, spark.sql.adaptive.enabled, AQEShuffleRead, Skew join optimization, Coalesce shuffle partitions

A query plan is a guess made before any data is read. Spark estimates how big each table and each intermediate result will be, and picks joins and partition counts from those estimates. Adaptive query execution, AQE, lets Spark revise the guess. The query runs in pieces called query stages, split at each [shuffle](https://tecyfy.com/concepts/spark-shuffle). When a stage finishes, Spark knows the real size of every partition it wrote, and re-plans the rest of the query with those numbers. It has been on by default since Spark 3.2.0 (`spark.sql.adaptive.enabled`).

## What it changes

1. **Merges small partitions.** A shuffle into 200 partitions of a few megabytes leaves 200 tiny tasks. AQE combines neighbouring partitions. By default it favours parallelism: it ignores the 64 MB target and only keeps each merged partition over 1 MB. Spark's docs suggest setting `spark.sql.adaptive.coalescePartitions.parallelismFirst` to false on a busy cluster, so partitions grow toward 64 MB and fewer tasks run.
2. **Splits skewed partitions.** In a [sort-merge join](https://tecyfy.com/concepts/sort-merge-join), a partition over 5 times the median and over 256 MB counts as skewed. AQE splits it into pieces near 64 MB and copies the matching rows of the other side to each piece. This is the main defence against [data skew](https://tecyfy.com/concepts/data-skew).
3. **Switches the join.** If one side of a planned sort-merge join turns out smaller than the broadcast threshold (10 MB by default), AQE turns it into a broadcast hash join. The shuffle already ran, so it is not as good as planning a broadcast from the start, but it skips both sorts and reads the shuffle files locally.

## How to see it in a plan

`explain()` before the query runs prints `AdaptiveSparkPlan isFinalPlan=false`: the plan Spark would start with. The real plan exists only after a run. In the SQL / DataFrame tab of the Spark UI, or in `explain()` on a DataFrame that has already run, it shows `isFinalPlan=true` with a Final Plan and an Initial Plan.

We ran this on Spark 4.2. A join of 2 filtered copies of an orders table was planned as a `SortMergeJoin`, because nothing told Spark the filter would leave 103 rows. After the run, the final plan read:

```
BroadcastHashJoin [order_id], [order_id], Inner, BuildLeft
:- BroadcastExchange
:  +- AQEShuffleRead local
+- AQEShuffleRead local
```

Every `AQEShuffleRead` box is AQE's work. Its label says which kind: `coalesced` for merged partitions, `local` for a switched join, `skewed` for a split. A join that was split shows as `SortMergeJoin(skew=true)`. Click the box in the SQL tab to see the number of partitions before and after, and the count of skewed partitions and splits.

## When it is not enough

AQE works from what a shuffle wrote, so it cannot fix the first stage that reads the files, and it only splits skew inside sort-merge joins (and rebalance hints), not inside a `groupBy`. It also needs enough partitions to merge: with `spark.sql.shuffle.partitions` far too low, every partition is already too big and tasks [spill](https://tecyfy.com/concepts/spark-spill). Our [Spark UI guide](/data-engineering/spark-ui) shows the same skewed join before and after AQE's skew handling, and the [shuffle partition calculator](/data-engineering/spark-shuffle-partitions) works out how AQE will merge and split a given shuffle.

## Related

- [Spark Shuffle](https://tecyfy.com/concepts/spark-shuffle): The step that moves rows between executors so rows with the same key end up together, for a join, group by or repartition; it splits a job into stages.
- [Shuffle and Data Skew](https://tecyfy.com/concepts/data-skew): A shuffle moves rows between machines so rows with the same key meet, for a join or group by; skew is when one key has far more rows, so one task does most of the work.
- [Sort-Merge Join](https://tecyfy.com/concepts/sort-merge-join): Spark's default join for 2 large tables: it shuffles both by the join key, sorts each partition, then walks the 2 sorted sides together to find matching rows.
- [Spark Spill](https://tecyfy.com/concepts/spark-spill): When a task's sort, join or aggregation no longer fits in execution memory, Spark writes part of it to local disk and reads it back later, so the task finishes, only slower.
- [Spark Executor Memory](https://tecyfy.com/concepts/spark-executor-memory): The memory each Spark worker process gets: a Java heap, split into a shared pool for shuffles, joins and cached data and a pool for your own objects, plus overhead outside the heap.

## Sources

- [Performance Tuning: Adaptive Query Execution (Spark 4.2.0)](https://spark.apache.org/docs/latest/sql-performance-tuning.html#adaptive-query-execution)
- [Web UI (Spark 4.2.0)](https://spark.apache.org/docs/latest/web-ui.html)
- [EXPLAIN (Spark 4.2.0)](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-explain.html)