Predicate Pushdown

Predicate pushdown moves filtering closer to the data source or reader, so that rows are excluded before they reach later operators. Two useful cases are source-side filtering and statistics-based skipping; they have different requirements.

• The source evaluates the filter. Reading from a relational database, the engine can send the predicate as part of the query — a WHERE clause the database applies with whatever indexes it has. Nothing about columnar storage is involved; the requirement is that the connector can express the filter in the source’s own language, which is why one that cannot fold a predicate falls back to fetching rows and filtering locally.
• The reader skips data using statistics. Reading files, the predicate is matched against recorded minimum and maximum values to avoid reading blocks that cannot match. Parquet provides one example, with statistics associated with the row group and column chunk level of its columnar layout. Skipping does not require that layout in every system. Iceberg can use file statistics in manifests before opening data files, including supported Avro files. PostgreSQL BRIN indexes summarize ranges of heap blocks, illustrating statistics-based skipping in a row store. The useful question is which summaries the reader has and which predicates it can safely test against them.

Both reduce work, and they fail for different reasons: the first when the filter cannot be translated, the second when the statistics cannot answer the question. The paragraphs below are about the second unless they say otherwise.

For a requested interval [20 January, 21 January), a group ending before the 20th can be excluded. A group spanning the interval is only a candidate: it might contain dates on either side and no matching row. Missing usable statistics require a conservative decision.

Projection chooses columns; partition pruning excludes files using partition values. They can work with predicate pushdown, but a filter may require reading a column absent from the final output. Functions, types, and reader support affect what can be pushed down, and the usual blocker is the shape of the filter rather than the engine’s willingness. Whether a given expression survives is something to check rather than predict, since engines rewrite some of them — a comparison against a function of a constant is folded to a constant, and some optimizers can turn a date function into a range — while others are passed through intact and the reader refuses them. A predicate that compares a stored column with a constant can be matched against recorded minimum and maximum values; one that wraps the column in an expression cannot, because the statistics describe the column and not the expression. year(order_date) = 2024 and order_date >= '2024-01-01' AND order_date < '2025-01-01' select the same rows, and the second is the form a chunk’s date range can be tested against directly — which is the reason to write it that way rather than relying on a rewrite that may or may not happen in your engine and version. The same applies to a column cast to another type before comparison, and to a predicate over a column the format records no statistics for.

Inspect the query plan and storage metrics to establish actual savings. Correct filtered output or a count of candidate groups alone does not prove how many bytes the engine read. Preserve the exact filter semantics when moving work between operators.

See Data Formats: Parquet, ORC, Avro, and Arrow for worked examples.

References

• the page index
• filters in pyarrow.parquet.read_table
• Parquet filter pushdown settings
• Apache Iceberg: Table specification
• PostgreSQL: BRIN indexes