Reading `.explain()` in its different modes

Description

Since module 2, every .explain() in this guide used its simplest form: no arguments, showing only the final physical plan. That was enough to read whether an operation triggered a shuffle, or whether a join chose BroadcastHashJoin. But .explain() accepts a mode parameter with five distinct values — simple, extended, cost, formatted, codegen — and each one answers a different question about the same query. This lesson runs all five, one by one, over exactly the same join and aggregation you already know — this time over the complete fact_orders_at_scale, ten million rows — and shows what new information each mode adds that the others don't have.

Connection to the module. Lesson 2 already used mode="extended" to read Catalyst's four phases. This lesson completes the catalog: the default mode you already knew without knowing its name (simple), the one that adds size estimates (cost), the one that numbers every operator for large plans (formatted), and the one confirming whether Spark compiled real code for your query (codegen).

An analogy: the same map, with five levels of detail

A modern GPS doesn't always show you the same level of detail. Sometimes you just want the general route, with no distractions — the equivalent of simple. Sometimes you want to see the complete reasoning: why the GPS discarded a street, what traffic rules it considered — the equivalent of extended, with its four phases. Sometimes you want to know how much estimated traffic there is on each stretch, before leaving — cost, with its size estimates. Sometimes you need a numbered, step-by-step list, to follow precisely on a very large and complex map — formatted. And, very occasionally, you want to confirm the navigator really compiled the instructions into an efficient program for your specific vehicle, instead of improvising every turn on the fly — codegen. None of the five is "the correct one"; each serves a different question.

Worked example, part 1: module 5's same query, in four modes

# explain_modes.py
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql.types import (
    StructType, StructField, StringType, IntegerType, DoubleType, TimestampType,
)

spark = SparkSession.builder.appName("kiosko-spark").master("local[*]").getOrCreate()

scale_schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("franchise_id", IntegerType(), False),
    StructField("store_id", StringType(), False),
    StructField("product_id", StringType(), False),
    StructField("quantity", IntegerType(), False),
    StructField("unit_price", DoubleType(), False),
    StructField("order_ts", TimestampType(), False),
])
dim_store_schema = StructType([
    StructField("store_id", StringType(), False),
    StructField("store_name", StringType(), False),
    StructField("city", StringType(), False),
])
orders_at_scale_df = spark.read.csv(
    "kiosko_orders_at_scale.csv", schema=scale_schema, header=True, enforceSchema=False,
)
dim_store_df = spark.read.csv("dim_store.csv", schema=dim_store_schema, header=True, enforceSchema=False)

fact_orders_at_scale_df = orders_at_scale_df.withColumn("revenue", F.col("quantity") * F.col("unit_price"))
q = (
    fact_orders_at_scale_df
    .join(dim_store_df, "store_id")
    .groupBy("store_id")
    .agg(F.sum("revenue").alias("total_revenue"))
)

for mode in ["simple", "extended", "cost", "formatted", "codegen"]:
    print(f"\n\n######## MODE = {mode} ########")
    q.explain(mode=mode)

What to expect — mode="simple" (the one you've already used since module 2, without knowing its name). Running python3 explain_modes.py, the first section is exactly this (executed in this run, over the complete fact_orders_at_scale):

######## MODE = simple ########
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- HashAggregate(keys=[store_id#2], functions=[sum(revenue#11)])
   +- Exchange hashpartitioning(store_id#2, 200), ENSURE_REQUIREMENTS, [plan_id=40]
      +- HashAggregate(keys=[store_id#2], functions=[partial_sum(revenue#11)])
         +- Project [store_id#2, revenue#11]
            +- BroadcastHashJoin [store_id#2], [store_id#7], Inner, BuildRight, false, false
               :- Project [store_id#2, (cast(quantity#4 as double) * unit_price#5) AS revenue#11]
               :  +- Filter isnotnull(store_id#2)
               :     +- FileScan csv [store_id#2,quantity#4,unit_price#5] Batched: false, ...
               +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, false]),false), [plan_id=35]
                  +- Filter isnotnull(store_id#7)
                     +- FileScan csv [store_id#7] Batched: false, ...

simple is the default value — q.explain() with no arguments produces exactly this — only the final physical plan, with no logical phases and no size estimates. It's the right mode when the only question is "which strategy did Spark choose?" — the question modules 4 and 5 answered in every one of their lessons.

mode="extended". Produces exactly the four phases you already read in this module's lesson 2 — Parsed, Analyzed, Optimized, Physical — now over ten million rows instead of forty. The plan's structure is identical to lesson 2's because the data's size doesn't change the logical plan's shape nor which optimization rules apply — what changes, instead, are the size estimates, which is exactly what the next mode adds.

What to expect — mode="cost".

######## MODE = cost ########
== Optimized Logical Plan ==
Aggregate [store_id#2], [store_id#2, sum(revenue#11) AS total_revenue#13], Statistics(sizeInBytes=5.8 GiB)
+- Project [store_id#2, revenue#11], Statistics(sizeInBytes=5.8 GiB)
   +- Join Inner, (store_id#2 = store_id#7), Statistics(sizeInBytes=9.0 GiB)
      :- Project [store_id#2, (cast(quantity#4 as double) * unit_price#5) AS revenue#11], Statistics(sizeInBytes=224.4 MiB)
      :  +- Filter isnotnull(store_id#2), Statistics(sizeInBytes=573.4 MiB)
      :     +- Relation [order_id#0,franchise_id#1,store_id#2,product_id#3,quantity#4,unit_price#5,order_ts#6] csv, Statistics(sizeInBytes=573.4 MiB)
      +- Project [store_id#7], Statistics(sizeInBytes=41.0 B)
         +- Filter isnotnull(store_id#7), Statistics(sizeInBytes=100.0 B)
            +- Relation [store_id#7,store_name#8,city#9] csv, Statistics(sizeInBytes=100.0 B)

== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- HashAggregate(keys=[store_id#2], functions=[sum(revenue#11)], output=[store_id#2, total_revenue#13])
   +- Exchange hashpartitioning(store_id#2, 200), ENSURE_REQUIREMENTS, [plan_id=40]
      ... (identical to mode="simple"'s physical plan)

This is the mode that answers a question modules 4 and 5 left implicit: where does the number come from that decides whether a table qualifies for BroadcastHashJoin? Look at the Relation [store_id#7,store_name#8,city#9] csv, Statistics(sizeInBytes=100.0 B) line — that's dim_store.csv's size estimate, the same figure (in bytes) module 5 cited when explaining why it qualified for broadcast, now directly visible in the plan, with no need to look it up separately. And notice the contrast: fact_orders_at_scale's side estimates 9.0 GiB for the Join's result — far above the broadcast threshold (10 MB by default) — so that side could never be the one that gets copied; only dim_store, with its 100.0 B, qualifies. cost is the only mode showing these numbers explicitly.

What to expect — mode="formatted".

######## MODE = formatted ########
== Physical Plan ==
AdaptiveSparkPlan (12)
+- HashAggregate (11)
   +- Exchange (10)
      +- HashAggregate (9)
         +- Project (8)
            +- BroadcastHashJoin Inner BuildRight (7)
               :- Project (3)
               :  +- Filter (2)
               :     +- Scan csv  (1)
               +- BroadcastExchange (6)
                  +- Filter (5)
                     +- Scan csv  (4)


(1) Scan csv 
Output [3]: [store_id#2, quantity#4, unit_price#5]
Batched: false
Location: InMemoryFileIndex [file:/.../kiosko_orders_at_scale.csv]
PushedFilters: [IsNotNull(store_id)]
ReadSchema: struct<store_id:string,quantity:int,unit_price:double>

(2) Filter
Input [3]: [store_id#2, quantity#4, unit_price#5]
Condition : isnotnull(store_id#2)

(3) Project
Output [2]: [store_id#2, (cast(quantity#4 as double) * unit_price#5) AS revenue#11]
Input [3]: [store_id#2, quantity#4, unit_price#5]

(4) Scan csv 
Output [1]: [store_id#7]
Batched: false
Location: InMemoryFileIndex [file:/.../dim_store.csv]
PushedFilters: [IsNotNull(store_id)]
ReadSchema: struct<store_id:string>

(5) Filter
Input [1]: [store_id#7]
Condition : isnotnull(store_id#7)

(6) BroadcastExchange
Input [1]: [store_id#7]
Arguments: HashedRelationBroadcastMode(List(input[0, string, false]),false), [plan_id=35]

(7) BroadcastHashJoin
Left keys [1]: [store_id#2]
Right keys [1]: [store_id#7]
Join type: Inner
Join condition: None

(8) Project
Output [2]: [store_id#2, revenue#11]
Input [3]: [store_id#2, revenue#11, store_id#7]

(9) HashAggregate
Input [2]: [store_id#2, revenue#11]
Keys [1]: [store_id#2]
Functions [1]: [partial_sum(revenue#11)]
Aggregate Attributes [1]: [sum#25]
Results [2]: [store_id#2, sum#26]

(10) Exchange
Input [2]: [store_id#2, sum#26]
Arguments: hashpartitioning(store_id#2, 200), ENSURE_REQUIREMENTS, [plan_id=40]

(11) HashAggregate
Input [2]: [store_id#2, sum#26]
Keys [1]: [store_id#2]
Functions [1]: [sum(revenue#11)]
Aggregate Attributes [1]: [sum(revenue#11)#24]
Results [2]: [store_id#2, sum(revenue#11)#24 AS total_revenue#13]

(12) AdaptiveSparkPlan
Output [2]: [store_id#2, total_revenue#13]
Arguments: isFinalPlan=false

formatted reorganizes exactly the same information as simple, but in two ways that become valuable on plans much larger than this one: first, every operator gets a number ((1) through (12)) that shows up both in the tree above and in its detail block further down — so you can spot at a glance where each piece lives, without counting indentation levels. Second, every detail block explicitly separates Input (what that operator receives) from Output/Arguments (what it produces or how it's configured) — information that in simple is compressed into a single dense line. For this lesson's short plan, the difference is cosmetic; for a plan with twenty or thirty operators — common in a production pipeline with several chained joins and aggregations — formatted is much easier to navigate.

Worked example, part 2: codegen, before and after running

What to expect — mode="codegen", over a DataFrame that hasn't run yet.

######## MODE = codegen ########
Found 0 WholeStageCodegen subtrees.

This surprises anyone expecting to see Java code right away: 0 subtrees, over exactly the same query that in simple already showed a complete physical plan. The reason connects to something you already saw in lesson 2: the physical plan is wrapped in AdaptiveSparkPlan isFinalPlan=false — it isn't final yet. Whole-stage code generation compiles the final plan, after Adaptive Query Execution finishes deciding its real shape; before that, there's nothing compiled yet to show.

# codegen_after_action.py -- continuation of the previous script, same q object
rows = q.collect()
print("result:", rows)

print("\n######## MODE = codegen, AFTER collect() ########")
q.explain(mode="codegen")

What to expect (executed in this run, over the same q object, after .collect()):

result: [Row(store_id='S02', total_revenue=9699999.999998083), Row(store_id='S01', total_revenue=9574999.999993788), Row(store_id='S03', total_revenue=7262500.000003301)]

######## MODE = codegen, AFTER collect() ########
Found 3 WholeStageCodegen subtrees.
== Subtree 1 / 3 (maxMethodCodeSize:131; maxConstantPoolSize:108(0.16% used); numInnerClasses:0) ==
*(1) Filter isnotnull(store_id#7)
+- FileScan csv [store_id#7] Batched: false, ...

Generated code:
/* 001 */ public Object generate(Object[] references) {
/* 002 */   return new GeneratedIteratorForCodegenStage1(references);
/* 003 */ }
... (real generated Java bytecode, omitted for length -- confirmed real, not a summary)

== Subtree 2 / 3 (maxMethodCodeSize:359; maxConstantPoolSize:327(0.50% used); numInnerClasses:2) ==
*(2) HashAggregate(keys=[store_id#2], functions=[partial_sum(revenue#11)], output=[store_id#2, sum#26])
+- *(2) Project [store_id#2, revenue#11]
   +- *(2) BroadcastHashJoin [store_id#2], [store_id#7], Inner, BuildRight, false, false
      :- *(2) Project [store_id#2, (cast(quantity#4 as double) * unit_price#5) AS revenue#11]
      :  +- *(2) Filter isnotnull(store_id#2)
      :     +- FileScan csv [store_id#2,quantity#4,unit_price#5] Batched: false, ...
      +- BroadcastQueryStage 0
         +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, false]),false), [plan_id=62]
            +- *(1) Filter isnotnull(store_id#7)
               +- FileScan csv [store_id#7] Batched: false, ...

== Subtree 3 / 3 (maxMethodCodeSize:196; maxConstantPoolSize:236(0.36% used); numInnerClasses:0) ==
*(3) HashAggregate(keys=[store_id#2], functions=[sum(revenue#11)], output=[store_id#2, total_revenue#13])
+- AQEShuffleRead coalesced
   +- ShuffleQueryStage 1
      +- Exchange hashpartitioning(store_id#2, 200), ENSURE_REQUIREMENTS, [plan_id=109]
         +- *(2) HashAggregate(keys=[store_id#2], functions=[partial_sum(revenue#11)], output=[store_id#2, sum#26])
            +- *(2) Project [store_id#2, revenue#11]
               +- *(2) BroadcastHashJoin [store_id#2], [store_id#7], Inner, BuildRight, false, false
                  ...

Now there are three compiled subtrees. Notice the asterisk (*(1), *(2), *(3)) preceding several operators within each subtree — that's the visible mark of which operators got fused into the same piece of code: Subtree 2 fuses a Filter, a Project, and a BroadcastHashJoin into a single compiled Java class (*(2) on all three), instead of three separately interpreted steps. BroadcastExchange and AQEShuffleRead, by contrast, carry no asterisk — they're points where the data flow crosses a boundary (a shuffle write, a read of an already-materialized result) that whole-stage codegen can't fuse across. Also notice the result's unrounded sum(revenue) (9699999.999998083 instead of 9700000.0) — the same floating-point phenomenon module 4 already documented over ten million sums; rounded with F.round(F.sum("revenue"), 2), it gives exactly 9700000.0, as earlier modules confirmed.

Diagram: which question each mode answers

flowchart TD
    Q["Same query:\njoin + groupBy + sum"] --> S["simple\nwhich strategy did Spark choose?"]
    Q --> E["extended\nwhich phases did the plan go through?"]
    Q --> C["cost\nwhat sizes did Catalyst estimate?"]
    Q --> F["formatted\nhow do I read a large plan, operator by operator?"]
    Q --> G["codegen\nwas real code compiled, and where did operators get fused?"]

    style S fill:#69c,stroke:#333
    style E fill:#9c6,stroke:#333
    style C fill:#fc6,stroke:#333
    style F fill:#c9c,stroke:#333
    style G fill:#f96,stroke:#333

Common mistakes

Using mode="cost" expecting to see real bytes, measured during execution. What happens: someone sees Statistics(sizeInBytes=5.8 GiB) in the Optimized Logical Plan and reads it as a real measurement, taken after running the query. Why it happens: the word "Statistics" sounds like a measured fact, not a prediction. How to spot it: if your explanation of these figures never includes the word "estimated" or "estimate," check again — these figures show up over the Optimized Logical Plan, a phase that comes before any real execution (this module's lesson 2). How to fix it: cost shows estimates based on the source files' size and simple propagation rules — not on real data yet; for real, measured bytes, after execution, the right source is the Spark UI (or its REST API), as you already saw in module 4, lesson 5.

Expecting mode="codegen" to show subtrees before any action, just like simple shows the complete physical plan immediately. What happens: someone runs .explain(mode="codegen") over a freshly built DataFrame, sees Found 0 WholeStageCodegen subtrees, and concludes something failed. Why it happens: the other four modes (simple, extended, cost, formatted) do show complete content with no prior action needed, so it's reasonable to expect the same behavior from codegen. How to spot it: if you see Found 0 WholeStageCodegen subtrees and haven't called any action (.collect(), .count(), .show()) on that same object yet, that's exactly the expected behavior, not an error. How to fix it: codegen describes the already-compiled code of the final plan — the one Adaptive Query Execution finishes locking in after execution; call an action first, on the same DataFrame object, and call .explain(mode="codegen") again on that same object to see the real subtrees.

Confusing codegen's asterisk (*(N)) with formatted's operator number. What happens: someone sees *(2) in codegen's output and (9) in formatted's output, and assumes they're the same kind of numbering, or that they can be compared directly. Why it happens: both are numbers in parentheses, near operator names, in two different modes of the same command family. How to spot it: if you try to match formatted's number (9) against some *(9) in codegen, you won't find a direct relationship — they're completely independent numbering systems. How to fix it: in formatted, the number identifies an operator's position within the plan's complete tree, in order of appearance. In codegen, the number inside the asterisk identifies which compiled subtree that operator belongs to — every operator sharing the same asterisk number got fused into the same piece of Java code; operators from different subtrees, like BroadcastExchange, carry no asterisk because they don't belong to any fused subtree.

Exercises

Exercise 1 — Calculate, by hand, whether the Join's result (an estimated 9.0 GiB) qualifies for broadcast. Using spark.sql.autoBroadcastJoinThreshold's default threshold (10 MB, already cited in module 5), compare that value against 9.0 GiB (the Join's result, according to mode="cost") and against 100.0 B (dim_store.csv, according to the same mode). Confirm which of the two, if either, would qualify.

threshold_mb = 10
join_result_gib = 9.0
dim_store_bytes = 100.0

threshold_bytes = threshold_mb * 1024 * 1024
join_result_bytes = join_result_gib * 1024 * 1024 * 1024

print(f"broadcast threshold = {threshold_bytes:,.0f} bytes")
print(f"estimated Join result = {join_result_bytes:,.0f} bytes -- qualifies: {join_result_bytes < threshold_bytes}")
print(f"dim_store.csv = {dim_store_bytes:,.0f} bytes -- qualifies: {dim_store_bytes < threshold_bytes}")

broadcast threshold = 10,485,760 bytes
estimated Join result = 9,663,676,416 bytes -- qualifies: False
dim_store.csv = 100 bytes -- qualifies: True

Exercise 2 — Count how many operators carry an asterisk in the worked example's Subtree 2, and which ones do NOT get one. Reviewing the Subtree 2 / 3 text already shown, count how many operators have the *(2) mark in front, and name the two operators in that same block carrying no asterisk at all.

Exercise 3 — Explain, without code, why mode="formatted" is more useful than mode="simple" on a thirty-operator plan, even though both show the same information. In 2-3 sentences, justify why formatted's numbering and block separation matter more as a plan grows, even if the informational content is identical to simple's.

Summary and next step

This lesson ran .explain() in its five modes — simple, extended, cost, formatted, codegen — over exactly the same query, and showed which different question each one answers: simple confirms the chosen strategy (what you've already used since module 2); extended shows the four logical and physical phases (this module's lesson 2); cost exposes the size estimates behind decisions like broadcast join; formatted numbers every operator to navigate large plans; and codegen confirms which operators got fused into real Java code — only available after the plan reaches its final shape, following a real action.

Before moving on you should be able to: choose the right .explain() mode based on the question you want answered; explain why mode="codegen" shows 0 subtrees before any action; and read mode="cost"'s estimates knowing they're predictions, not measurements.

Lesson 4 completes the isFinalPlan=false story: exactly what Adaptive Query Execution is, which parts of the plan it can change after execution, and how to read that difference with real evidence.

Resources

• Apache Spark — SQL Performance Tuning (the official reference for .explain()'s five modes, with the exact explain(mode="...") syntax — the complete foundation for this lesson). spark.apache.org/docs/latest/sql-performance-tuning.html.
• Apache Spark — Monitoring and Instrumentation (the Spark UI's REST API for real execution metrics, distinct from mode="cost"'s estimates — already cited in module 4). spark.apache.org/docs/latest/monitoring.html.
• This guide's DESIGN doc (spark-and-distributed-processing-guide/DISENO.md) — this lesson's exact specification: .explain(mode="formatted") and the other modes, over module 5's query read at scale.