Catalyst optimizer & explain()

Apache Spark · Spark SQL

Theory

Catalyst is the query optimizer inside Spark SQL, and every DataFrame or SQL query goes through it. Your code first becomes an unresolved logical plan. The analyzer resolves table and column names and types against the catalog (this is where 'column not found' errors come from). The optimizer then applies rules such as predicate pushdown, column pruning, constant folding, combining filters and rewriting subqueries into joins. The planner turns the optimized logical plan into a physical plan by choosing operators and join strategies, and code generation compiles parts of it to JVM bytecode.

df.explain() prints the physical plan; explain('extended') shows all four plans (parsed, analyzed, optimized, physical) and explain('formatted') gives a readable numbered summary. For example, orders.filter(amount > 20000).filter(region = 'North').select('order_id') becomes a single Filter on both conditions plus a Project of only the needed columns in the optimized plan.

Why we use it

explain() is the main tool for understanding why a query is slow: you can see whether filters are pushed to the file scan, which columns are read, where shuffles (Exchange) happen and which join strategy was picked. Interviewers often ask you to walk through Catalyst's phases.

When to use it

Run explain() whenever a query is slow or behaves unexpectedly, before and after a change, and to confirm broadcast joins, partition pruning or pushed filters. Use 'formatted' for long plans. Remember the plan printed before running may change at runtime with adaptive query execution.

How it works

  1. DataFrame code or SQL string
  2. Parse into an unresolved logical plan
  3. Analyzer resolves tables, columns and types
  4. Do all names resolve? Yes: Optimizer applies rule-based rewrites. No: Fail with an AnalysisException.
  5. Planner picks physical operators and join strategies
  6. Whole-stage code generation compiles the stages
  7. Executors run the physical plan

Syntax

df.explain()                    # physical plan
df.explain("extended")          # parsed, analyzed, optimized, physical
df.explain("formatted")         # numbered operators + details
df.explain("cost") / ("codegen")
spark.sql("EXPLAIN EXTENDED SELECT ...")

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[1]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

orders = spark.createDataFrame([
    (101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (102, 2, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (103, 1, "North", "Mouse",  3,  1500.0, "2026-01-06"),
    (104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (105, 4, "West",  "Tablet", 1, 25000.0, "2026-01-08"),
    (106, 2, "East",  "Phone",  1, 15000.0, "2026-01-08"),
    (107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
    (108, 3, "South", "Mouse",  4,  2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
   "qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

q = (orders.filter(F.col("amount") > 20000)
           .filter(F.col("region") == "North")
           .select("order_id", "amount"))
q.explain("extended")

# Output:
# == Parsed Logical Plan ==
# 'Project ['order_id, 'amount]
# +- Filter (region#2 = North)
#    +- Filter (amount#5 > cast(20000 as double))
#       +- Project [order_id#0, customer_id#1, region#2, product#3, qty#4, amount#5, to_date(order_date#6, None, Some(Asia/Calcutta), true) AS order_date#7]
#          +- LogicalRDD [order_id#0, customer_id#1, region#2, product#3, qty#4, amount#5, order_date#6], false
#
# == Analyzed Logical Plan ==
# order_id: int, amount: double
# Project [order_id#0, amount#5]
# +- Filter (region#2 = North)
#    +- Filter (amount#5 > cast(20000 as double))
#       +- Project [order_id#0, customer_id#1, region#2, product#3, qty#4, amount#5, to_date(order_date#6, None, Some(Asia/Calcutta), true) AS order_date#7]
#          +- LogicalRDD [order_id#0, customer_id#1, region#2, product#3, qty#4, amount#5, order_date#6], false
#
# == Optimized Logical Plan ==
# Project [order_id#0, amount#5]
# +- Filter ((isnotnull(amount#5) AND isnotnull(region#2)) AND ((amount#5 > 20000.0) AND (region#2 = North)))
#    +- LogicalRDD [order_id#0, customer_id#1, region#2, product#3, qty#4, amount#5, order_date#6], false
#
# == Physical Plan ==
# *(1) Project [order_id#0, amount#5]
# +- *(1) Filter ((isnotnull(amount#5) AND isnotnull(region#2)) AND ((amount#5 > 20000.0) AND (region#2 = North)))
#    +- *(1) Scan ExistingRDD[order_id#0,customer_id#1,region#2,product#3,qty#4,amount#5,order_date#6]

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