How deeply does PostgreSQL Mistakes and How to Avoid Them cover partition pruning?

Demonstrates RANGE partitioning, monthly partitions, EXPLAIN verification, measured runtimes, and the requirement to predicate on the key.

Partition pruning

As explained in 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.

Partition pruning excludes partitions that cannot contain matching rows. PostgreSQL can do this when a query predicates on the partition key, reducing the data it must scan. section 4.2

Time-partitioned payments

Partition a payments table by RANGE on tstamp into monthly partitions. A query for one timestamp scans only payments_p_2022_3 instead of the entire table; the demonstrated runtime falls from about 62 seconds to 2.5 seconds.

Use the query plan to verify pruning:

Include the partition key in the predicate; otherwise the planner cannot exclude partitions based on that condition. section 4.2
Treat pruning as a complement to partitioning: smaller scans help performance, while dropping a whole old partition simplifies maintenance. section 4.2

How PostgreSQL Mistakes and How to Avoid Them covers it

Cover of PostgreSQL Mistakes and How to Avoid Them

liveBook manning.com

Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

liveBook manning.com

Architecting an Apache Iceberg LakehousePartition pruning is the elimination of data partitions that cannot satisfy a query's filters before or during data access.
liveBook manning.com

100 SQL Server Mistakes and How to Avoid ThemPartition elimination is an optimization that excludes table partitions that cannot contain rows matching a query's predicates.

Compare how 3 books explain 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.

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.