Columnar pruning and predicate pushdown, explained simply

The cost of reading everything

Every day, data teams run queries that touch terabytes of Parquet files, only to discard 99% of the rows and columns they read. When you're working with large datasets in Apache Spark, performance isn't just about CPU and memory—it's about touching as little data as possible. Columnar pruning and predicate pushdown, explained simply, means reading only the columns and rows you actually need, right at the storage layer.

This is one of the most powerful optimizations in modern data engines, yet we see teams leave enormous performance gains on the table by not understanding or trusting how it works. By the end of this article, you'll know how to verify these optimizations are happening, why they fail sometimes, and how to structure your queries to make them stick.

Select needed columns early

Column pruning sounds obvious: if you need three columns from a table with fifty columns, read only three. But the order matters, and many teams don't realize Spark can only push column selection down to the reader if you explicitly select them.

Consider a real scenario: you're calculating the average sale price per region from a fact table with hundreds of columns including user history, item metadata, and timestamps.

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("ColumnPruning").getOrCreate()

# Bad: Let's load everything first
orders_full = spark.read.parquet("s3://data/orders/")
result = orders_full.select("region", "price").groupBy("region").avg("price")

# Good: Select early, before any other operations
orders = spark.read.parquet("s3://data/orders/").select("region", "price")
result = orders.groupBy("region").avg("price")

# Even better: Push selection into read operation
orders = spark.read.parquet("s3://data/orders/") \
    .select("region", "price") \
    .filter(col("price") > 0) \
    .groupBy("region").avg("price")

The difference? In the "good" example, Spark's Catalyst optimizer sees that you're only touching region and price, so the Parquet reader only deserializes those columns from disk. You save I/O proportional to the columns you skip. On a 100-column table, reading 2 columns instead of 100 can mean 50x faster reads.

The key is understanding that Spark's logical plan gets analyzed before the physical plan executes. If you select columns after complex operations like joins or window functions, the optimizer may not be able to push the selection back to the reader.

Filters pushed to Parquet readers

Predicate pushdown is where column pruning's sibling lives: instead of reading all rows and then filtering, Spark tells the Parquet reader "only give me rows where this condition is true." The reader uses Parquet's row groups and statistics to skip entire blocks of data without touching them.

Parquet files store metadata about each row group: min/max values for every column. When you filter on date > '2024-01-01', Spark tells the reader to skip any row group where the max date is before 2024-01-01.

# Parquet reader will skip row groups with dates before 2024-01-01
transactions = spark.read.parquet("s3://data/transactions/") \
    .filter(col("transaction_date") >= "2024-01-01") \
    .select("user_id", "amount")

# This also works, but less efficient if done after a join
large_table = spark.read.parquet("s3://data/large_table/")
result = large_table.join(users, "user_id") \
    .filter(col("transaction_date") >= "2024-01-01")  # Pushed down, but only after join

In the second example, the filter still gets pushed, but it happens after the join reads data. The first pattern is better because it filters early, reducing the amount of data that enters the join operation.

For a dataset with 1000 row groups covering a full year of data, a filter on a single month might skip 90% of row groups, turning a multi-minute query into seconds. Real teams see this repeatedly: a sales query filtered to the last 7 days runs in 3 seconds instead of 45 seconds.

Check PushedFilters in plans

You should never guess whether predicate pushdown is happening. Spark's physical plan tells you exactly what filters were pushed.

Call .explain() on any DataFrame to see the logical and physical plans. Look for the PushedFilters line in the Parquet scan operator.

df = spark.read.parquet("s3://data/orders/") \
    .filter((col("region") == "EMEA") & (col("price") > 100)) \
    .select("order_id", "price", "region")

df.explain()

When you run this, the output includes something like:

== Physical Plan ==
*(2) Project [order_id#10, price#11, region#12]
+- *(1) Filter ((region#12 = EMEA) AND (price#11 > 100))
   +- *(0) FileScan parquet [order_id#10,price#11,region#12]
       PushedFilters: [IsNotNull(region), EqualTo(region,EMEA), IsNotNull(price), GreaterThan(price,100)]
       ReadSchema: struct<order_id:bigint,price:double,region:string>

The PushedFilters line shows the conditions passed to the Parquet reader. No PushedFilters line? The filter is being evaluated in Spark after reading, which means you're reading unnecessary data.

Common reasons filters don't push down:

• The filter column isn't in the Parquet file's schema
• The filter uses a UDF (discussed next)
• The filter is on a computed column that doesn't map to Parquet's physical columns
• The datasource reader doesn't support predicate pushdown (some formats and sources don't)

Get in the habit of running explain() on critical queries. It takes 30 seconds and often reveals optimization opportunities.

Avoid UDFs on filter columns

User-defined functions break pushdown. If you filter using a UDF, Spark can't push the filter to the Parquet reader because it doesn't know what the function does without executing it.

from pyspark.sql.functions import udf
from pyspark.sql.types import BooleanType

# Bad: UDF on filter column blocks pushdown
is_high_value = udf(lambda x: x > 1000, BooleanType())
orders = spark.read.parquet("s3://data/orders/") \
    .filter(is_high_value(col("price")))

# Good: Use native Spark functions
orders = spark.read.parquet("s3://data/orders/") \
    .filter(col("price") > 1000)

# Also acceptable: UDF in select, filter on native columns
def extract_region(address: str) -> str:
    # complex parsing logic
    return address.split(",")[-1]

extract_region_udf = udf(extract_region, StringType())
orders = spark.read.parquet("s3://data/orders/") \
    .filter(col("country") == "US") \
    .select("order_id", extract_region_udf(col("address")).alias("region"))

The third pattern is the right balance: filter on columns that the Parquet reader can handle natively, then apply complex logic to the reduced dataset. You get both the benefits of predicate pushdown and the flexibility of UDFs.

If you absolutely must use a UDF for filtering, consider pre-computing that column when you write the Parquet file. Add a is_high_value boolean column during ETL, then filter on it during analysis.

Common mistakes

Filtering after window functions: Many teams write aggregations, then filter the results, thinking the filter pushed down. It doesn't—the window has already been computed over all data.

# Filter doesn't push down; computed after window
orders_with_rank = spark.read.parquet("s3://data/orders/") \
    .withColumn("rank", row_number().over(Window.partitionBy("user_id").orderBy("date"))) \
    .filter(col("rank") == 1)

# Better: Filter first, then rank
recent_orders = spark.read.parquet("s3://data/orders/") \
    .filter(col("date") >= "2024-01-01") \
    .withColumn("rank", row_number().over(Window.partitionBy("user_id").orderBy("date"))) \
    .filter(col("rank") == 1)

Selecting all columns, then dropping some: This defeats column pruning. Always select what you need, not what you don't.

Complex filter expressions with OR: Some OR conditions can't be pushed. Spark is conservative: if it can't guarantee correctness, it won't push. Keep filters simple where possible.

Trusting the UI without checking the plan: The Spark UI shows tasks and stages, but it doesn't show whether filters were pushed. Always verify with explain().

Key takeaways

• Column pruning reduces I/O by reading only the columns you need. Select early in your pipeline.
• Predicate pushdown skips entire row groups in Parquet files using metadata. Filters on partition columns and range conditions are most effective.
• Always call .explain() on critical queries and look for PushedFilters in the physical plan. Seeing an empty PushedFilters line means your filter runs after reading, wasting I/O.
• Native Spark functions push down; UDFs don't. Filter with functions, apply UDFs to the result.
• Filter before joins, window functions, and aggregations to reduce the amount of data flowing through your pipeline.
• Parquet file statistics make predicate pushdown work. Ensure your data is properly partitioned and sorted for common filter patterns.