Partition pruning
Partition pruning is a query-planning technique that excludes data partitions unable to contain rows matching a query’s predicates.
Architecting an Apache Iceberg Lakehouse
Partition pruning is the elimination of data partitions that cannot satisfy a query's filters before or during data access.
- How it is used
- Optimizes lakehouse queries by eliminating irrelevant partitions and files before data access.
- How deep it goes
- Explains planning, manifest, file, and row-group filtering, then stops short of detailed partition-design procedures.
- Examples
- Uses January 2024 filtering, a sales_amount range test, Dremio queries, manifest statistics, and Parquet row-group metadata.
- What you can do after
- Apply it via Dremio and Apache Iceberg to reduce scanned data, query latency, object-store operations, and compute cost.
Read how Architecting an Apache Iceberg Lakehouse explains partition pruning →
100 SQL Server Mistakes and How to Avoid Them
Partition elimination is an optimization that excludes table partitions that cannot contain rows matching a query's predicates.
- How it is used
- Reduces I/O for large-table reporting queries by excluding partitions outside an EventTime predicate.
- How deep it goes
- Explains predicate logic, data-type conversion, simple parameterization, I/O statistics, and query plans as practical pruning checks.
- Examples
- Demonstrates EventTime reporting queries, AND versus OR filters, DATE versus DATETIME variables, and OPTION(RECOMPILE).
- What you can do after
- Diagnose/debug with it by checking predicates, type conversions, parameterization, I/O statistics, and plans for eliminated partitions.
Read how 100 SQL Server Mistakes and How to Avoid Them explains partition pruning →
PostgreSQL Mistakes and How to Avoid Them
Partition pruning is the query-planning technique of excluding table partitions that cannot contain rows matching the query conditions.
- How it is used
- Uses partition-key predicates to reduce scans and simplify time-based data maintenance.
- How deep it goes
- Demonstrates RANGE partitioning, monthly partitions, EXPLAIN verification, measured runtimes, and the requirement to predicate on the key.
- Examples
- Uses monthly payments partitions, timestamp lookup plans, buffer counts, runtimes, bulk loading, and dropping an obsolete partition.
- What you can do after
- Diagnose pruning with EXPLAIN (ANALYZE, BUFFERS) and write predicates that let PostgreSQL exclude irrelevant partitions.
Read how PostgreSQL Mistakes and How to Avoid Them explains partition pruning →
Related concepts
- partitioning keyused by · A partitioning key is a value or attribute used to determine which partition stores or receives a record or data item.
- Apache Icebergused by · Apache Iceberg is an open table format for managing large analytical datasets with metadata, schema evolution, snapshots, and transactional operations across distributed or object storage.
- search optimizationused by · Search optimization is a Snowflake table optimization service that maintains search access paths for faster selective queries.
- performanceaddresses · Performance is the measurable effectiveness or operational behavior of a system under specified conditions, assessed through relevant qualities such as task results, speed, responsiveness, throughput, or resource use.
- clustering keyproduced by · A clustering key is one or more columns or expressions that determine how a table’s data is organized for efficient filtering and joining.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.