Metadata-only aggregates and partition pruning
When can an aggregate be answered from metadata alone, and when does partition pruning actually fire?
Aggregate pushdown and metadata-only answers
Whether an aggregate may be pushed is already settled: it must decompose into (map, combine, finalize), the same algebra that allowed materialized-view rollups and partial aggregation. What is new is how far down it can go, and the bottom rung is different in kind.
Columnar footers already store count, min, max and null count per row group. So SELECT COUNT(*), MIN(c) and MAX(c) can be answered from metadata with zero data read: cost O(number of files) instead of O(rows). With a table format whose manifests carry the row count of every file, it can be O(1): one manifest read. This is not a reduction. It is an asymptotic collapse: the query stops depending on the size of the data.
The condition is exact and often broken: the predicate must be fully consumed by pruning. Metadata says how many rows a file has, not how many match. Any residual filter invalidates the metadata answer. So COUNT(*) with a WHERE clause can be answered from metadata only when partition pruning resolves the predicate completely and nothing is left over. Partial pruning gives nothing. This is a discontinuity: one conjunct that cannot be pruned moves you from O(1) to O(rows).
A boundary note. Tables that use merge-on-read (deletes are recorded separately in deletion vectors or positional delete files and applied when reading) break this. The stored row count is for the file, not for the live rows. The engine must either apply the deletes (which defeats the metadata path) or keep a maintained live count. This is why COUNT(*) can be instant on a copy-on-write table (changes rewrite whole files) and slow on a merge-on-read table with identical data.
At the federation rung, pushing GROUP BY to the source transfers groups instead of rows. The reduction ratio is n/g (rows over groups). It is large when g is much smaller than n, worthless when the grouping key has very many distinct values, and harmful if the remote system aggregates badly. The decision therefore needs an NDV (number of distinct values) estimate for the grouping key. This closes a loop: the pushdown whose payoff varies most depends on the one estimate that is provably hardest to make.
In one sentence: decomposability sets legality; footer statistics turn some aggregates from O(rows) into O(files); that path needs the predicate to be fully pruned, not partly; and merge-on-read deletes disable it.
Partition pruning (static)
Partitioning makes the directory structure a coarse index. Pruning is evaluating the predicate against partition values with no data access: O(partitions) of metadata work, not O(rows).
The constraint is about whether the predicate is expressible after normalization. WHERE dt = '2024-01-01' prunes. WHERE date(ts) = '2024-01-01' against a table partitioned on dt does not, because the optimizer cannot relate ts to dt. That relationship exists only as a convention in the head of whoever wrote the ingestion job. This is a sargability failure at the partition level, and it is the most common reason a correctly partitioned table is fully scanned.
Hidden partitioning is the fix. See it as a general move, not as one table format's feature. You declare the partition value as a transform of a data column: day(ts), bucket(16, id), truncate(10, name). The relationship moves from human convention into catalog metadata, where the optimizer can mechanically rewrite a predicate on ts into a predicate on the partition. The user never writes the partition column. The general statement:
The defect of Hive-style partitioning (partition folders such as dt=2024-01-01/) was that the relationship lived only in the pipeline author's head. The same shape appeared in the planning articles: enforced constraints are what allow join elimination.
The pruning step has a scaling failure of its own: its cost is proportional to the number of partitions. Metadata systems that list partitions by listing directories do a fixed-rate operation against a partition count that keeps growing. Listing 10⁵ to 10⁶ partitions takes minutes, and then the pruning is the query. Manifest-based metadata (a file that lists all data files and their partition values) exists to break that proportionality. The money version of the same story is that each LIST request on object storage is billed individually.
In one sentence: pruning is an index built from directory structure; it works only if the predicate is expressible on the partition value, which requires the transform to be declared and not conventional; and its own cost grows with partition count until the metadata design must change.