Predicate pushdown

What is predicate pushdown?

Predicate pushdown hands your filter down to the layer that actually reads the data, so rows that fail the condition never get loaded in the first place. The slow alternative is to pull every row into memory and filter afterwards, spending I/O on rows you were always going to discard.

A predicate is just the condition in your query, like country = 'BE' or order_date >= DATE '2026-01-01'. The query optimizer works out which predicates the storage layer or file reader can evaluate on its own and pushes those into the scan. Whether it happened shows up in the execution plan: a pushed filter sits on the table scan itself instead of as a separate step above it.

How Parquet lets a reader skip row groups

Columnar storage makes pushdown far more effective, because it carries statistics a reader can check before decoding any values. A Parquet file is split into row groups, and each column chunk records the minimum and maximum value it holds. The reader compares your predicate against those bounds and skips any row group that cannot contain a match.

Take a filter of order_date >= DATE '2026-01-01'. A row group whose recorded maximum order_date is 2025-06-30 cannot hold one matching row, so the reader never pulls it off disk. Parquet's page index sharpens this further, keeping min and max per page so a reader can binary-search straight to the pages that matter. Delta Lake applies the same trick at file level through data skipping, storing min, max and null counts for the first 32 columns by default, and Z-ordering rearranges the rows so those bounds stay tight and rule out more files.

When pushdown stops working

Two things quietly switch it off. The first is wrapping the column in a function. Min and max statistics describe the raw stored values, so a filter like YEAR(order_date) = 2026 leaves the reader nothing to compare against: it would have to compute YEAR() on every row before it could judge the condition. Rewrite it as a range on the bare column, order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01', and the statistics become usable again.

The second is a format that keeps no statistics. CSV and JSON files carry no min or max metadata, so there is nothing to skip on and the reader has to scan every row and filter afterwards. That is one concrete reason a lakehouse stores its tables as Parquet rather than flat text.

Predicate pushdown versus projection pushdown

The two are complementary. Predicate pushdown cuts the rows you read; projection pushdown cuts the columns you read, by honouring the SELECT list instead of pulling every field. Engines like Apache Spark and Trino apply both whenever the data source can support them.

The same principle turns up under another name in Power Query. Query folding is Power Query translating your steps into a native source query so the filter runs at the source rather than in the report, and predicate pushdown is that idea one layer down, inside the database or file reader.