Pruning vs Pushdown

Concepts covered: Predicate Pushdown

Pruning and pushdown both spare Spark from reading data, but they act at different moments on different machines. Pruning removes whole directories from the file listing on the driver while the plan is built. Pushdown removes row groups inside each file as an executor opens it. A query can get either, both or neither, and each is enabled by a different choice at write time.

AspectPartition pruningPredicate pushdown
Unit skippedA directory and every file in itA row group inside a file
Metadata usedDirectory names, as in event_date=2024-03-12Min, max and null count in each footer
Decided byThe driver, while planningEach task, as it opens its file
Cost of a skipped unitNothing: never listed as a split, never openedOne footer read per file
Enabled at write time bypartitionBy on the columnSorting or clustering on the column
Plan fieldPartitionFiltersPushedFilters
Best for columns that areLow-cardinality and always filtered: date, regionHigh-cardinality: city, user, device

Functions on the column

The driver holds the full list of partition values in memory, so pruning can evaluate any deterministic expression over them. With event_date as the partition column, F.month('event_date') == 3 still prunes to the March directories. The footer test understands only the plain comparisons listed in the first section, so the same F.month around a data column pushes nothing.

One table, both levels

A trips table queried by day and by city should prune on the day and push down the city:

(trips
    .repartition('event_date')
    .sortWithinPartitions('event_date', 'city_id')
    .write
    .partitionBy('event_date')
    .mode('overwrite')
    .parquet('/lake/trips'))

repartition('event_date') is a hash shuffle, so the write runs in 2 stages and each day's rows land in the same task, giving a single file per day directory. sortWithinPartitions adds no shuffle. The sort leads with event_date because the writer needs each task's rows grouped by partition column; when the existing order starts with anything else, Spark inserts its own sort by event_date, and your city order is not guaranteed to survive it.

Along the arrows the data shrinks: 365 directories become 1 file on the driver, then that file's 16 row groups become 1 and its 20 columns become 3 before the Filter sees a row.
  • Query (source, event_date and city_id filters)
  • Prune directories (transform, driver, PartitionFilters)
  • Plan splits (transform, driver, files left)
  • Read footer (transform, task, per file)
  • Skip row groups (quality gate, PushedFilters vs min/max)
  • Decode 3 columns (transform, ReadSchema)
  • Filter (transform, exact rows)
  • Result (consumer, driver)
  • Query -> Prune directories
  • Prune directories -> Plan splits
  • Plan splits -> Read footer
  • Read footer -> Skip row groups
  • Skip row groups -> Decode 3 columns
  • Decode 3 columns -> Filter
  • Filter -> Result
  • Driver, planning: Query, Prune directories, Plan splits
  • Executor, scan: Read footer, Skip row groups, Decode 3 columns, Filter
  • Output: Result

Put numbers on it. The table holds a year: 365 directories, each holding a 2GB file of 16 row groups, 730GB in all. The query asks for city 42 on 2024-03-12 and selects 3 of the 20 columns.

StepSkippedLeft to read
StartNothingAll 730GB, in 365 files
Prune on event_dateThe other 364 directories, dropped from the listing before any split is plannedA 2GB file
Push down city_idThe other 15 row groups, ruled out by the footerA 128MB row group
Project 3 columnsThe other 17 column chunksThe 3 chunks' share of 128MB

Pruning did most of the work, and cheaply: the 364 dropped directories cost the driver a listing entry each, and no task touched them. Put the same 365 files in a single flat directory, with event_date stored inside each file, and pushdown still finds the right row group, because each footer's min and max for event_date are both that file's own date. The difference is the work spent ruling the rest out. The driver plans splits over all 730GB, 16 per file, and each of those 5,840 tasks reads its file's footer before finding nothing to fetch, where the partitioned layout needed 16 tasks.

A query on /lake/events filters event_date == '2024-03-12'. explain() shows PartitionFilters: [] and PushedFilters: [IsNotNull(event_date), EqualTo(event_date,2024-03-12)]. What does that tell you?

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.