Predicate Pushdown
Concepts covered: Predicate Pushdown
Column projection already lets the reader skip columns a query never names. Predicate pushdown does the same for rows. When a filter compares a column to a literal, Catalyst hands that comparison to the Parquet reader together with the column list, and the reader uses it to avoid fetching data that cannot match. The test runs inside the scan, against the file, before rows are decoded into memory.
Where the filter runs
- Filter applied after load
- The reader decodes every row of the projected columns
- Every decoded row enters the Filter operator
- Rows that fail are discarded after their I/O is paid
- Stage input bytes equal the full projected size
- Filter pushed to the reader
- The reader receives the predicate before opening a file
- It tests each row group's statistics against the predicate
- Row groups that cannot match are never fetched
- Input bytes shrink with every row group skipped
trips = spark.read.parquet('/lake/trips_raw')
result = (
trips
.filter(F.col('city_id') == 42)
.select('trip_id', 'city_id', 'fare')
)
result.explain()Logical optimization pushes the filter and the column list down to the scan before any job runs. The FileScan line of the physical plan then carries 4 fields worth reading:
| Plan field | Value in this plan | What it tells you |
|---|---|---|
| ReadSchema | struct<trip_id:bigint,city_id:int,fare:double> | Projection: 3 of the 20 columns get decoded |
| DataFilters | [isnotnull(city_id), (city_id = 42)] | Data-column filters Spark offered to the reader |
| PushedFilters | [IsNotNull(city_id), EqualTo(city_id,42)] | Filters handed to the Parquet reader, which tests them against row-group statistics where the column type allows |
| PartitionFilters | [] | Directory-level filters, empty because this copy of trips has no partition columns |
Real plans add expression ids, as in city_id#12; they're left out here.
Why the Filter node stays
Directly above the FileScan, the plan still contains Filter (isnotnull(city_id) AND (city_id = 42)). Pushdown is coarse: the reader skips a row group only when its statistics prove no row inside can match. A row group that might hold city 42 is read whole, rows for cities 41 and 43 included, and the Filter removes them. So PushedFilters buys less I/O, and the Filter node keeps the answer exact.
What the reader will accept
- Pushed to the reader
- ==, <, <=, >, >= against a literal
- isin(...) with literal values
- isNull() and isNotNull()
- startswith('...') on a string column
- & and | joining any of the above
- Kept above the scan
- A function around the column: F.lower, F.substring, F.to_date
- Arithmetic on the column, as in F.col('fare') * 1.1 > 50
- A comparison of 2 columns, as in fare > tip
A blocked predicate can usually be rewritten as a comparison on the stored column. Both filters below keep the March 2024 trips from the string column trip_date, and only the second reaches the reader:
blocked = trips.filter(F.substring('trip_date', 1, 7) == '2024-03')
pushed = trips.filter(
(F.col('trip_date') >= '2024-03-01') & (F.col('trip_date') < '2024-04-01')
)The first plan still shows PushedFilters: [IsNotNull(trip_date)]. Spark infers that any row passing the substring test has a non-null date and pushes that inferred check, which rules out almost nothing. The second plan adds GreaterThanOrEqual(trip_date,2024-03-01) and LessThan(trip_date,2024-04-01).
trips stores trip_date as a string, and each file's row groups cover tight date ranges. Which filter lets the reader skip row groups for 2024-03-12?
- F.to_date('trip_date') == F.lit('2024-03-12'), since Catalyst folds the conversion into the literal
- F.col('trip_date') == '2024-03-12', since a bare column compared to a literal becomes EqualTo
- F.substring('trip_date', 1, 10) == '2024-03-12', since the substring spans the whole value
- None of them, since footers keep no min and max for string columns
Fewer rows reach the shuffle
Every row the reader drops is also a row the rest of the stage never touches, shuffle write included. The animation totals fares per city for 1 day across 12 trips in 3 partitions. Its tables sit in memory with no row groups, so the filter runs right after the scan in the same stage instead of inside the reader; downstream, the effect is identical.
from pyspark.sql import functions as F
result = (
trips
.filter(F.col('trip_date') == '2024-03-12')
.groupBy('city')
.agg(F.sum('fare').alias('total_fare'))
)
result.collect()| trip_id | city | trip_date | fare |
|---|---|---|---|
| 1 | SF | 2024-03-11 | 18.5 |
| 2 | NYC | 2024-03-12 | 22 |
| 3 | SF | 2024-03-12 | 9.75 |
| 4 | LA | 2024-03-13 | 31.2 |
| 5 | NYC | 2024-03-11 | 14 |
| 6 | LA | 2024-03-12 | 27.4 |
| 7 | SF | 2024-03-13 | 12.1 |
| 8 | NYC | 2024-03-12 | 16.5 |
| 9 | LA | 2024-03-11 | 20 |
| 10 | SF | 2024-03-12 | 11.25 |
| 11 | NYC | 2024-03-13 | 19.8 |
| 12 | LA | 2024-03-11 | 8.6 |
Five of the 12 trips are dated 2024-03-12, so at most 5 rows reach partial aggregation, and the shuffle moves per-city partial sums instead of raw trips. The job runs as 2 stages with 1 shuffle, from the groupBy.
Proving the I/O was saved
explain() shows intent. To confirm the saving, run the query and open it in the SQL tab of the Spark UI. If the scan node's number of output rows equals the table's row count, no row group was skipped, however good PushedFilters looked. After a layout change, compare the stage's input size: skipping shows up as fewer bytes read while the Filter's output stays the same.
About this lesson
This section is part of the Storage Formats: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.