The idea
Large tables are split into partitions on disk, often by a column like date or region. Partition pruning is the optimizer step that, from a query filter, decides which partitions could possibly match and reads only those.
How it works
- A table partitioned by date stores each day in its own directory or file group.
- A query filtering for one month lets the planner skip every directory outside that range.
- The engine reads metadata, not data, to make the decision, so pruning is nearly free.
Static and dynamic pruning
- Static pruning uses constant filters known at plan time, like a literal date.
- Dynamic pruning uses values discovered during execution, such as the keys from the other side of a join, to prune at runtime.
Good partition choice plus pruning can turn a scan of a whole table into a scan of a tiny slice, which is often the single biggest speedup in a warehouse.
Key idea
Partition pruning uses query filters and metadata to read only relevant partitions, skipping the rest before any data is scanned.