Deep Dive: Predicate Pushdown

Jeremy Tregunna

Jeremy Tregunna

— 4 min read
Share
Deep Dive: Predicate Pushdown

What if the database had no idea what your WHERE clause clause actually meant until it had already dragged every single row off disk into memory?

That used to be the default, ages ago. The storage layer was just a dumb block server handing your scan operator the pages to read each and every one of them. The executor would evaluate the predicates, and everything downstream operated on whatever survived.

Simple, correct...but also beyond slow as tables grew, and queries got selective. Take this query:

SELECT product_id SUM(quantity)
FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND amount > 500
  AND region = 'EU';

Under the old way, the engine would open the sales table (hundreds of GB potentially), read every row group, every column chunk, decompress everything, decode everything, and then throw away 90-99% of it. The predicate only existed at the very top of the plan, all the expensive work happened first.

When you think that the storage layer has nothing useful to say about which data matters, and that moving data is cheap, why wouldn't you prefer this simple solution?

Predicate Pushdown Just Moves the Filter Earlier

A predicate is any condition that can eliminate rows (=, >, BETWEEN, IN, AND/OR, etc.). Pushdown is the act of evaluating it as close to the physical data as possible.

There are 4 natural layers where a predicate can be applied:

  1. After everything else (joins, aggregates, window functions...)
  2. In the middle of the operator tree
  3. Inside the scan operator itself
  4. Inside the storage reader, using metadata before any data bytes are touched

The goal is to push the predicate as far down that list as safely possible.

The Storage Layer Finally Gets a Say

The biggest wins happen when the file format itself can answer the question "could this chunk possibly contain matching rows?" without reading the rows.

Modern columnar formats (e.g., Parquet) are built exactly for this.

A Parquet file is not one giant undifferentiated blob. It's divided into row groups (horizontal slices of rows). Inside each row group are column chunks (one per column). The file footer (and optional page indexes) store statistics for every column chunk: min, max, null_count, and sometimes a dictionary or bloom filter.

When the reader receives a predicate, it consults those statistics first:

  • amount > 500 on a chunk whose max <= 500 skips the entire chunk (often the whole row group).
  • sale_date BETWEEN ... on a chunk whose range has no overlap also skips.
  • region = 'EU' on a low-cardinality column with a dictionary means you check the dictionary codes instead of the values.

Only the row groups that survive get their column chunks read, decompressed, and decoded.

This is predicate pushdown at the storage layer. Without the statistics (or if the reader doesn't use them), you are back to full scans no matter how clever the query planner is.

The Query Planner Has to Cooperate

The optimizer must rewrite the plan so the predicates actually reach the scan. Classic transformations include:

  • Pushing filters below joins when they only reference one side.
  • Pushing into derived tables / CTEs / views (with care around aggregates and outer joins)
  • Combining with projection pushdown so you only even open the columns needed for the predicate and the final output.

Once the predicate arrives at the scan operator, it is handed to the file reader. In a vectorized engine the surviving rows are turned into vectors and a cheap bitmask filter is applied immediately after decoding.

The I/O saving is obvious. The secondary wins are just as important, though: less decompression work, less memory pressure, less CPU time spent decoding rows you will never use, and in distributed systems, far less data shuffled between nodes.

Residual Predicates and Other Realities

Even after aggressive pruning you usually still need a residual filter. Min/max only tells you if a chunk might contain matches, not that the exact rows match. So the engine reads the candidates and re-evaluates the original predicate (or a simplified version) on them. That is expected and cheap once the data volume has already been reduced by 5-10x.

Not every predicate can be pushed:

  • User-defined functions
  • Non-deterministic expressions
  • Certain complex expressions involving multiple tables before the join is resolved
  • Predicates that would change semantics if pushed (some outer joins)

Stale statistics also hurt. A chunk whose min/max says it overlaps your predicate might actually contain zero matching rows after data has been updated or the stats were never collected.

Where It Actually Lands in Modern Designs

Row oriented OLTP systems push predicates into index seeks and range scans, typically. The storage engine returns only candidate rows; a residual filter cleans up the rest.

Columnar analytical systems push predicates into the file reader using exactly the row group statistics, dictionaries, and bloom filters described above. The scan operator becomes a thin coordinator.

The storage layer is no longer semantically blind. It understands enough about predicates to prune aggressively. The query planner's job is to hand it the right predicates. The execution engine's job is to apply whatever is left efficiently on the survivors.

The goal is to run on dramatically less data.

That single change in assumption is responsible for a huge fraction of the performance difference between "it works" and "it's fast" on large analytical workloads.

The rest is just engineering details on top of that corrected assumption.

Read more