Spark EXPLAIN cheat sheet - reading physical plans, exchanges, join strategies, pushdown and AQE final plans

#Getting the plan

df.explain()                 # physical plan only
df.explain("formatted")      # numbered operators + details per node (best for reading)
df.explain("extended")       # parsed, analyzed, optimized logical + physical
df.explain("cost")           # optimized logical plan with size/row statistics
df.explain("codegen")        # generated Java code per whole-stage codegen block

EXPLAIN FORMATTED
SELECT c.country, sum(o.amount)
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-11-01'
GROUP BY c.country;

Tip

With AQE on (the default since Spark 3.2), explain() before execution shows AdaptiveSparkPlan isFinalPlan=false. That's the initial plan. Join strategies and partition counts can change at runtime. For the plan that actually ran, open the SQL / DataFrame tab in the Spark UI after the query finishes, or call explain() on the same DataFrame after an action has completed.

Read the plan bottom-up: scans at the bottom, the final operator at the top. In formatted mode, the numbers in parentheses link the tree to the detail sections below it.

#Scans and pushdown

FileScan parquet [order_id,customer_id,amount,order_date]
  PartitionFilters: [isnotnull(order_date), (order_date >= 2026-11-01)]
  PushedFilters: [IsNotNull(customer_id)]
  DataFilters: [isnotnull(customer_id)]
  ReadSchema: struct<order_id:bigint,customer_id:bigint,amount:decimal(18,2)>

Delta and Iceberg scans show their own node names (for example BatchScan for DSv2 sources) and file skipping comes from table statistics. Names vary by version, so check your build's output.

#Exchanges (shuffles)

Every Exchange is a stage boundary, and the shuffle write and read time shows up in the stage metrics. Two exchanges on the same key in a row usually mean a repartition() that the join or aggregate then redid. Remove the manual one.

Warning

Exchange SinglePartition on a large dataset is a single-task bottleneck, and can run out of memory in one executor. A window function with Window.orderBy(...) and no partitionBy is the usual cause, and Spark logs a warning about moving all data to a single partition.

#Join strategies

The BuildRight/BuildLeft label on hash joins shows which side is loaded into memory. If the big table is on the build side, the statistics are wrong. Run ANALYZE TABLE ... COMPUTE STATISTICS or use a hint.

#Aggregates

Expand with several count(DISTINCT ...) columns multiplies the input rows. Use approx_count_distinct where an exact count isn't required.

#Python and Arrow nodes

A BatchEvalPython above a FileScan with an empty PushedFilters means the UDF blocked pushdown. Apply built-in filters before the UDF, or replace the UDF with built-in functions.

#AQE nodes

AdaptiveSparkPlan isFinalPlan=true
+- == Final Plan ==
   ...
   +- AQEShuffleRead coalesced
   +- AQEShuffleRead coalesced and skewed

On Spark 3.0–3.1 the same node is named CustomShuffleReader. If you expected skew handling and see no skewed label, check spark.sql.adaptive.skewJoin.skewedPartitionFactor and skewedPartitionThresholdInBytes. Skew split also applies only to sort-merge joins (and shuffled hash joins in newer versions), not to aggregates.

#Hints that change the plan

SELECT /*+ BROADCAST(c) */ ...            -- force BroadcastHashJoin with c as build side
SELECT /*+ MERGE(o, c) */ ...             -- force SortMergeJoin
SELECT /*+ SHUFFLE_HASH(c) */ ...         -- force ShuffledHashJoin with c as build side
SELECT /*+ SHUFFLE_REPLICATE_NL(c) */ ... -- cartesian-style nested loop
SELECT /*+ REPARTITION(200, customer_id) */ ...
SELECT /*+ COALESCE(16) */ ...
SELECT /*+ REBALANCE(order_date) */ ...   -- AQE-aware even output partitions before a write

from pyspark.sql.functions import broadcast
orders.join(broadcast(customers), "customer_id")
orders.join(customers.hint("shuffle_hash"), "customer_id")

Caution

A BROADCAST hint overrides the size threshold. If the hinted side grows (a "small" dimension that becomes 5 GB), the job fails with driver out-of-memory or a broadcast timeout. Prefer fixing statistics over hard-coding hints in production jobs, and review existing hints when data volumes change.

#Whole-stage codegen

*(3) Project means the operator runs inside whole-stage codegen block 3. Operators without the *(n) prefix break codegen. Python UDFs, some generators and wide schemas above spark.sql.codegen.maxFields are common causes. One unfused operator is fine. A chain of them on a hot path is worth rewriting.

On Databricks with Photon, supported operators appear with Photon prefixes (for example PhotonShuffleExchangeSink), and a ColumnarToRow transition marks where execution drops back to the JVM engine. Operator names change between runtime versions.

#Quick diagnosis

#Prevent it next time

Save explain("formatted") output for critical jobs in the repository and diff it in code review. An unexpected CartesianProduct, a new SinglePartition exchange or a lost PartitionFilters entry is cheaper to catch in a diff than in a 3 a.m. stage that never finishes.