Skip to content

Predicate Pushdown

Query-optimization method — instantiates Demand-Triggered Deferred Evaluation

Moves a query's filters down to the data source so rows that cannot match are never read, decoded, or transmitted in the first place.

Predicate pushdown relocates a query's filter conditions as close to the data source as the plan allows — into the scan, the storage layer, or even a remote system — so that rows failing the filter are never read, decoded, or transmitted at all. Instead of pulling data up to a filter, it pushes the filter down to the data. What makes it THIS mechanism is that it turns a need predicate into a gate at the point of production: the filter is not merely evaluated sooner, it is evaluated where the data lives, letting the source itself skip whole files, blocks, or partitions that cannot contain a matching row.

Example

A Spark job over Parquet files in object storage asks for events from one day in one country. Without pushdown, every executor reads all the Parquet files in full, decodes billions of rows, and only then a filter throws almost all of them away. With predicate pushdown the date = '2026-03-01' AND country = 'JP' predicate travels down to the Parquet reader, which consults each file's per-column min/max statistics and row-group indexes and skips any chunk whose ranges exclude that date or country — most files are never read past their footer. The same predicate against a date-partitioned layout prunes entire directories before a single file is opened.

The data that crosses the network and reaches the query engine is already close to the answer set: the filter did its work at the source, not after a full haul.

How it works

  • Find the pushable predicates. Determine which filter conditions reference only base columns the source can evaluate on its own.
  • Bind each to its source. Trace each predicate to the scan, partition, or file it constrains, so it can be evaluated there.
  • Evaluate at the source. The storage layer or remote system applies the filter via partition pruning, min/max zone maps, or its own indexes, emitting only surviving rows.
  • Leave the rest behind. Predicates referencing computed or post-join columns stay where they must run; only the pushable ones move.

Tuning parameters

  • Pushdown depth — how far down the filter goes: query engine → file format → storage or remote source. Deeper skips more but needs a source able to evaluate it.
  • Predicate selectivity — how much a pushed filter actually prunes. A weakly selective predicate pushed down adds evaluation cost low in the stack for little skip.
  • Statistics granularity — partition-level vs. row-group vs. page-level min/max. Finer statistics prune more precisely but cost more to store and consult.
  • Conjunct splitting — whether to decompose a compound predicate and push only its pushable parts, leaving a residual filter above. More splitting pushes more but complicates the residual.

When it helps, and when it misleads

Its strength is selective queries over large partitioned or columnar sources, and federated queries where hauling raw data across a network dominates cost. When the layout cooperates, pushdown converts a full scan into reading a handful of relevant chunks.

It misleads when the data isn't laid out to exploit it: unsorted data with useless min/max ranges, or a predicate on a high-cardinality unpartitioned column, skips nothing while still paying to evaluate the filter low. Worse, a predicate pushed to a remote source that evaluates it differently — collation, null handling, type coercion — can silently return the wrong rows. The classic misuse is assuming a filter "will be pushed down" without checking, then wondering why the query still scans everything. The discipline is to read the physical plan to confirm which predicates actually pushed, and to lay data out (partition, sort, cluster) so the selective ones prune.[1]

How it implements the components

Predicate Pushdown fills the filter-placement side of the archetype — deciding what is needed and moving that test to the source — not row reconstruction or demand detection:

  • need_predicate — the filter condition is the need test that decides which rows are wanted.
  • deferred_dependency_map — tracing each predicate to the base column and source it can be evaluated against.
  • realization_path — relocating the filter's evaluation point down toward the source; its placement is the whole mechanism.

It does not defer stitching columns into rows at the top of the plan — that complementary move is the Late-Materialization Query Plan — and it does not skip later operands of a boolean expression, which is Short-Circuit Evaluation.

References

[1] Predicate pushdown (with its sibling, projection pushdown) is a standard rule in relational query optimizers; columnar formats such as Parquet and ORC expose per-chunk statistics specifically so engines can push filters into the scan and skip data. Whether a given predicate can be pushed depends on the source's capabilities. ↩