Partition Pruning and Exclusion
Deep dive into how the optimizer uses partition keys to exclude irrelevant partitions, drastically reducing data scanned.
What is Partition Pruning?
Welcome to a key optimization technique in PostgreSQL: Partition Pruning. This is where the database intelligently skips scanning partitions that cannot possibly contain the data a query is looking for.
Think of it as filtering bookshelves: if you're looking for a book published in 2023, you wouldn't check shelves marked '1990-1999' or '2000-2010'.
How the Optimizer Works
When you execute a query on a partitioned table, PostgreSQL's query planner examines the WHERE clause. It compares the conditions in your query to the definitions of your table's partitions.
If the query's conditions guarantee that certain partitions cannot possibly hold any matching rows, the optimizer simply excludes those partitions from the scan plan. This significantly reduces the amount of data that needs to be read from disk.
All lessons in this course
- Query Optimization with Partitioning
- Attaching and Detaching Partitions
- Partition Pruning and Exclusion
- Partition-wise Joins and Aggregates