Predicate Pushdown and Column Statistics

The fastest way to process data is not to read it. Every layer of an analytical stack is built around proving, from metadata alone, that a region of data cannot satisfy a filter. Understanding the hierarchy of that proof explains why one query scans 40 GB and a superficially similar one scans 400 MB.

The hierarchy

Partition pruning eliminates whole partitions from a filter on the partition key, using only catalogue or manifest metadata. No data files are opened. This is the cheapest and most powerful level.

File pruning compares the filter against per-file min-max statistics in the table format's manifest. Files whose range cannot contain a match are never opened. This works on any column with statistics, not only the partition key, and it is the main advantage table formats hold over directory-based layouts.

Row group pruning happens inside an opened Parquet file, using the footer's per-row-group statistics. The footer is read, most row groups are skipped, and only the surviving column chunks are fetched.

Page pruning goes one level finer, using the optional page index to skip individual pages within a column chunk. It matters most for highly selective filters on well-sorted columns, where a query might read a few pages from a large file.

Late materialisation is the last piece: evaluate the filter on its own column first, produce a selection vector, and only then read the other requested columns for the surviving rows. On a selective filter over a wide table this avoids decoding most of the data the query nominally projects.

Why statistics can be useless

Min and max prune only when they are narrow relative to the filter. In data written in arrival order, a random column's values are spread across every file, so every file's range spans nearly the full domain, every test passes, and nothing is skipped. The statistics are present, correct, and worthless.

This is the fundamental point about physical layout: pruning effectiveness is a property of data organisation, not of the format. Sorting or clustering on the columns people filter by is what converts existing statistics into skipped bytes.

For high-cardinality equality filters, min-max is weak even when sorted, since a single value's presence cannot be excluded by a range that contains it. Bloom filters address this directly, giving a probabilistic "definitely not present" for equality predicates on columns such as user IDs, at the cost of storage and false positives that cause harmless extra reads.

When it breaks

Functions on the filtered column disable pushdown. WHERE year(ts) = 2024 compares a computed value against statistics that describe ts, so the engine usually cannot prune. Rewriting as a range on ts restores it, and this single pattern accounts for a large share of unexpectedly expensive queries.

Type mismatches silently disable it too. Comparing a string column to a number, or a timestamp to a differently-typed literal, inserts a cast that most engines cannot push through. The query returns correct results and scans everything.

Nulls break naive ranges. Min-max says nothing about nulls, so IS NULL needs the separate null count statistic. Formats record it and engines vary in whether they use it.

Statistics can be stale or absent. Files written by tools that omit statistics, or manifests not refreshed after a rewrite, leave the engine unable to prune. Query plans should be inspected for bytes scanned rather than trusted, and a sudden cost increase with no query change is usually a metadata problem.

Correlated filters mislead planners. Selectivity estimates typically assume independence between predicates, so a filter on city and a filter on country are estimated as a product when they are nearly redundant. The engine underestimates the result size and may choose a bad join strategy, which is a planning failure downstream of otherwise correct pruning.