Adaptive Query Execution (AQE)

Apache Spark · Performance Tuning

Theory

Adaptive Query Execution re-optimises a query while it runs, using real statistics from finished shuffle stages instead of estimates made before the job started. It is on by default (spark.sql.adaptive.enabled = true). After each shuffle Spark knows the true size of every partition and can change the rest of the plan.

AQE does three main things. Coalescing partitions: if 200 shuffle partitions turn out tiny, it merges neighbours up to an advisory size (spark.sql.adaptive.advisoryPartitionSizeInBytes, 64 MB by default). Switching join strategy: if one side of a sort-merge join turns out small, it converts the join to a broadcast hash join (or a shuffled hash join when partitions are small). Skew join handling: a partition bigger than 5 times the median and above 256 MB is split into smaller tasks, with the matching side replicated. explain() shows AdaptiveSparkPlan, with isFinalPlan=true once it has run.

Why we use it

Static plans rely on estimates that are often wrong after filters and joins. AQE fixes the most common performance problems automatically: too many tiny tasks, a slow sort-merge join that could be a broadcast, and a few huge skewed partitions that keep the whole stage waiting.

When to use it

Leave AQE on; it is the default for good reason. Tune advisoryPartitionSizeInBytes or the skew thresholds only when the Spark UI shows tasks still too small, too large or skewed. AQE acts only at shuffle boundaries, so it cannot fix skew in a stage without a shuffle.

How it works

  1. Query starts with an initial plan
  2. Run the first stage up to a shuffle
  3. Collect real partition sizes from the shuffle
  4. Are partitions tiny, one join side small, or a partition skewed? Yes: Re-plan: coalesce, switch to broadcast, or split skew. No: Keep the plan.
  5. Run the next stage and repeat
  6. Final plan (isFinalPlan=true)

Syntax

spark.conf.get("spark.sql.adaptive.enabled")                 # 'true' by default
spark.sql.adaptive.coalescePartitions.enabled                  # merge small partitions
spark.sql.adaptive.advisoryPartitionSizeInBytes                # target size, 64MB
spark.sql.adaptive.skewJoin.enabled                            # split skewed partitions
spark.sql.autoBroadcastJoinThreshold                           # 10MB broadcast limit

Example

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("techtown-spark").getOrCreate()

for k in ["spark.sql.adaptive.enabled",
          "spark.sql.adaptive.coalescePartitions.enabled",
          "spark.sql.adaptive.skewJoin.enabled",
          "spark.sql.autoBroadcastJoinThreshold"]:
    print(k, "=", spark.conf.get(k))

# A larger synthetic orders table and a small customers table
orders = (spark.range(200_000)
    .withColumn("customer_id", (F.col("id") % 5 + 101).cast("int"))
    .withColumn("amount", (F.col("id") % 500).cast("double")))
customers = spark.createDataFrame(
    [(101, "Asha", "Jaipur"), (102, "Ravi", "Pune"), (103, "Meera", "Delhi"),
     (104, "Karan", "Jaipur"), (105, "Priya", "Mumbai")],
    "customer_id INT, name STRING, city STRING")

spark.conf.set("spark.sql.shuffle.partitions", "200")
by_city = (orders.join(customers, "customer_id")
                 .groupBy("city").agg(F.sum("amount").cast("long").alias("sales"))
                 .orderBy("city"))
for row in by_city.collect():        # collect() runs this exact plan, so explain() shows its final form
    print(row["city"], row["sales"])

# After an action the adaptive plan is final: look for isFinalPlan=true, the join strategy,
# and AQEShuffleRead coalesced (200 planned shuffle partitions merged into a few)
by_city.explain()

# Output:
# spark.sql.adaptive.enabled = true
# spark.sql.adaptive.coalescePartitions.enabled = true
# spark.sql.adaptive.skewJoin.enabled = true
# spark.sql.autoBroadcastJoinThreshold = 10485760b
# Delhi 9980000
# Jaipur 19920000
# Mumbai 10060000
# Pune 9940000
# == Physical Plan ==
# AdaptiveSparkPlan isFinalPlan=true
# +- == Final Plan ==
#    ResultQueryStage 3
#    +- *(4) Sort [city#5 ASC NULLS FIRST], true, 0
#       +- AQEShuffleRead coalesced
#          +- ShuffleQueryStage 2
#             +- Exchange rangepartitioning(city#5 ASC NULLS FIRST, 200), ENSURE_REQUIREMENTS, [plan_id=166]
#                +- *(3) HashAggregate(keys=[city#5], functions=[sum(amount#2)])
#                   +- AQEShuffleRead coalesced
#                      +- ShuffleQueryStage 1
#                         +- Exchange hashpartitioning(city#5, 200), ENSURE_REQUIREMENTS, [plan_id=135]
#                            +- *(2) HashAggregate(keys=[city#5], functions=[partial_sum(amount#2)])
#                               +- *(2) Project [amount#2, city#5]
#                                  +- *(2) BroadcastHashJoin [customer_id#1], [customer_id#3], Inner, BuildLeft, false
#                                     :- BroadcastQueryStage 0
#                                     :  +- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=78]
#                                     :     +- *(1) Project [cast(((id#0L % 5) + 101) as int) AS customer_id#1, cast((id#0L % 500) as double) AS amount#2]
#                                     :        +- *(1) Filter isnotnull(cast(((id#0L % 5) + 101) as int))
#                                     :           +- *(1) Range (0, 200000, step=1, splits=2)
#                                     +- *(2) Project [customer_id#3, city#5]
#                                        +- *(2) Filter isnotnull(customer_id#3)
#                                           +- *(2) Scan ExistingRDD[customer_id#3,name#4,city#5]
# +- == Initial Plan ==
#    Sort [city#5 ASC NULLS FIRST], true, 0
#    +- Exchange rangepartitioning(city#5 ASC NULLS FIRST, 200), ENSURE_REQUIREMENTS, [plan_id=51]
#       +- HashAggregate(keys=[city#5], functions=[sum(amount#2)])
#          +- Exchange hashpartitioning(city#5, 200), ENSURE_REQUIREMENTS, [plan_id=48]
#             +- HashAggregate(keys=[city#5], functions=[partial_sum(amount#2)])
#                +- Project [amount#2, city#5]
#                   +- BroadcastHashJoin [customer_id#1], [customer_id#3], Inner, BuildLeft, false
#                      :- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)),false), [plan_id=43]
#                      :  +- Project [cast(((id#0L % 5) + 101) as int) AS customer_id#1, cast((id#0L % 500) as double) AS amount#2]
#                      :     +- Filter isnotnull(cast(((id#0L % 5) + 101) as int))
#                      :        +- Range (0, 200000, step=1, splits=2)
#                      +- Project [customer_id#3, city#5]
#                         +- Filter isnotnull(customer_id#3)
#                            +- Scan ExistingRDD[customer_id#3,name#4,city#5]

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic