Partition Pruning

Partition pruning excludes storage partitions that cannot satisfy a query’s partition predicates. A date-and-country directory layout can let a reader discard unrelated locations before opening their data files.

Where the partition values are recorded decides what the reader has to do to prune. In the directory convention, the values are in the paths themselves — Spark’s partition discovery reads them from a Hive-style layout such as country=US/date=2024-01-20/ — so pruning requires listing directories and parsing names, and an engine that lists a large tree pays for that before it reads anything. A table format instead keeps per-file partition values in its own metadata, so the reader can select files without walking the entire storage tree. Iceberg uses a snapshot’s manifest list and the relevant manifests, with summaries that can exclude irrelevant manifests. Planning may therefore read several metadata files; its cost depends on their organization, the predicate, and caching. Some systems record the values without anyone declaring a partition column at all: Snowflake’s micro-partitions carry per-column ranges created automatically as data is loaded, and pruning uses those, which is the same idea without a partition key to choose.

The partition column is therefore a design choice with two ways to go wrong, and both are common. Partition by something queries do not filter on and nothing is pruned, while every write still creates another directory. Partition by something too fine — an identifier, or an hour on a low-volume table — and each partition holds a handful of rows, which trades pruning for the small-file problem and a metadata listing that costs more than the scan it saved. The column to partition by is the one that appears in the filters of the queries that matter, at a granularity that leaves each partition worth opening. A query for one ingestion date can prune other ingestion dates. A query for event time needs a valid relationship between event time and ingestion time before using that shortcut. Late arrivals can make a convenient ingestion-date filter omit required events.

Pruning chooses candidate locations, not necessarily matching rows. Remaining files still need the applicable filters. Column projection and row-group statistics can reduce work further — see predicate pushdown for the filter-level counterpart — and their support and ordering depend on the reader. Pruning describes excluding candidate storage units. Pushdown describes moving a filter to a source or reader that can evaluate it or use it to skip data. These mechanisms can overlap: a pushed predicate can help exclude whole files before they are opened, not just reduce reads within a file.

Compare the query result against a trusted reference and inspect the plan and runtime metrics. A plan listing three candidate files does not prove three network requests or a particular billed byte count. Keep null values and interval boundaries explicit.

See External Tables and the Lake/Warehouse Boundary for worked examples.

References

• partition discovery
• Redshift Spectrum query performance
• querying partitioned tables
• Apache Iceberg: Table specification
• Snowflake: Micro-partitions and data clustering