Partition-wise Joins and Aggregates
Discover how PostgreSQL can join and aggregate partitioned tables partition-by-partition for big performance gains.
What Are Partition-wise Operations
When two tables are partitioned the same way, PostgreSQL can join matching partitions to each other instead of joining the whole tables. This is a partition-wise join.
The same idea applies to grouping: a partition-wise aggregate.
Enabling the Feature
These optimizations are off by default because they increase planning cost. Turn them on per session or in postgresql.conf.
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;All lessons in this course
- Query Optimization with Partitioning
- Attaching and Detaching Partitions
- Partition Pruning and Exclusion
- Partition-wise Joins and Aggregates