File Pruning from Per-File Min and Max Statistics

A predicate like WHERE order_date = DATE '2025-03-27' over a table of ten thousand files. If the query engine opens every file and reads its footer to discover that only three contain that date, the scan has paid ten thousand footer reads to avoid ten thousand data reads. Pruning is the mechanism that moves that decision one level up, so the elimination happens before any file is opened.

The information needed is trivial in principle: for each file, the smallest and largest value of each column. The difficulty is entirely in where those bounds live, how they are maintained, and how often they actually discriminate.

Bounds as a necessary but not sufficient test

A min/max pair defines a closed interval. If the predicate’s accepted set does not intersect the file’s interval, the file cannot contain a matching row and may be skipped. The converse does not hold: an overlapping interval means the file might contain a match, so it must be read. Pruning is therefore one-sided. It can only ever produce false positives, never false negatives, provided the bounds are correct.

Correctness is where implementations diverge. The bounds must be computed over the actual values in the file, not over a sample, and they must be computed under the column’s real ordering. For a string column the ordering is byte-wise comparison, not locale collation, so a file containing 'apple' and 'Banana' has min 'Banana' and max 'apple' under UTF-8 byte order. A predicate x < 'a' intersects that interval and the file is read even though both values arguably fail under a case-insensitive comparison. This is not a bug; it is the consequence of the spec fixing one ordering, and it means a query with a collation-dependent predicate must not trust pruning at all.

NaN is the second correctness trap. IEEE 754 leaves comparison with NaN unordered, so minimum and maximum over a float column containing NaN are not a total order, and no interval describes the column’s value set. Writers either omit statistics for such columns or record sentinel values that mean “no useful bound”, and readers must treat a missing bound as “cannot prune” rather than as “no matches”. Treating absent statistics as an empty range is the classic way to silently drop rows.

Three places the same information is stored

The same min/max idea appears at three granularities, and a well-designed scan uses all three.

Inside a Parquet file, each column chunk’s footer carries statistics for that chunk: minimum, maximum, and null count. A writer that fills a file with a single row group at the 128 MiB default produces one chunk per column, which leaves nothing for row-group skipping to eliminate; writers that expect skipping therefore target much smaller row groups so that each chunk holds a narrower value range. This level prunes within a file, and it is invisible to the table format.

At the table level, the manifest entry for each data file carries a bounds map: the lower and upper bound for a designated set of columns, plus the null count and record count for the file. These are the values a planner reads without opening the data file, which is what makes pruning cheap. Their size matters: bounds are stored for a bounded set of columns rather than all of them, because the manifest is read on every query and its width is multiplied by the number of files. Implementations let an operator narrow the set, and they truncate long values to a fixed prefix, which keeps a string bound from carrying a whole document into every manifest entry.

At the partition level, the manifest list carries a summary of each manifest’s partition values, allowing whole manifests to be skipped before their entries are read. That is the level described in the metadata tree, and it is what keeps planning cost proportional to surviving files.

-- Planner-side reasoning, expressed as the filter a scan would derive
SELECT count(*) FROM sales
WHERE order_date = DATE '2025-03-27'
  AND amount > 1000;

-- File A: order_date [2025-03-01, 2025-03-31], amount [0, 50]
--   date intersects, amount does not          -> skip
-- File B: order_date [2025-03-27, 2025-03-27], amount [10, 900]
--   both intersect                           -> read
-- File C: order_date [2025-04-01, 2025-04-30], amount [0, 5000]
--   date does not intersect                   -> skip

Note that File B is read even though no row may satisfy both predicates jointly. Per-column bounds are a bounding box, and a bounding box over a sparse point set says nothing about correlation. This is the single largest source of disappointing pruning rates, and no amount of statistics work fixes it. Only clustering the data so that correlated values land in the same file does.

Why pruning collapses on unsorted data

The pruning rate depends on the ratio of a file’s value range to the query’s range. With $N$ rows spread uniformly at random over $D$ distinct values and files of $n$ rows, a file’s range covers roughly $n$ of the $D$ values, so a point predicate matches roughly $n/D$ of the files. When files hold a small fraction of the value domain, that fraction is close to one and pruning eliminates almost nothing. Concretely: a table with one million distinct customer identifiers, files of one million rows each, gives every file a range spanning the whole domain. Every file overlaps every predicate. The statistics are present, correct, and useless.

Sorting by the predicate column inverts the ratio. If the table is ordered by order_date, each file covers a narrow contiguous slice, and a date predicate eliminates all but the files whose slices contain the date: typically one file, sometimes two if the date straddles a file boundary. This is why ingestion order is a design decision and not an implementation detail; the layout that a writer produces determines the pruning rate that every future reader gets. The same reasoning explains why the cluster’s treatment of partitioning and of compaction is really about statistics as much as about file counts.

A useful rule of thumb: pruning pays when the number of distinct values in a column exceeds the number of files by at least an order of magnitude and the data is ordered or clustered on that column. When both hold, a point query reads one file. When either fails, it reads all of them.

The cost side

Manifest bytes are not free. A table with 100,000 data files and a bound for each of twenty columns has twenty lower and twenty upper bounds per file entry, so the statistics alone are twenty million values before any path or count is stored. At eight or sixteen bytes per value that is hundreds of megabytes of manifest data for a table whose actual data may be only a few terabytes, which is why implementations truncate aggressively and let an operator reduce the set of columns that carry bounds. Truncation preserves correctness for the lower bound (a truncated minimum is still $\le$ every value) and for the upper bound when the truncation increments the last byte, but a writer that truncates an upper bound by simply cutting the string produces an upper bound that is smaller than some values in the file, which makes pruning drop matching rows. The spec’s rule is that upper bounds must be rounded up; implementations that get this wrong fail silently, returning fewer rows than exist.

There is a second, subtler cost: stale statistics. A file’s bounds describe the file’s contents, which are immutable, so they cannot go stale. What goes stale is the set of files: after compaction rewrites files, the new files need new bounds, and any planning cache keyed on the old manifest is invalid. Snapshot-scoped caching handles this because a snapshot’s manifests are immutable, but it means a cache hit requires an exact snapshot match rather than a table match.

When pruning cannot help at all

Three query shapes defeat file pruning structurally. Predicates on a column with no recorded bounds, which includes any column past the statistics limit and any column whose type has no defined ordering. Predicates that are not range-shaped, such as LIKE '%suffix', regexp_like, or inequality against a function of the column, because no interval test can eliminate a file. And predicates that only become selective after a join, since the join’s filtering happens after the scan has already chosen its files; the format has no mechanism to push a join’s value domain down into file selection, which is why dimension-table-driven filtering on a large fact table is often better served by clustering than by statistics.

The honest summary is that min/max pruning is a cheap filter that works spectacularly on ordered, high-cardinality columns and does nothing on unordered ones. It is not a substitute for layout; it is a reward for getting layout right.