How to Achieve Pruning When Querying by Non-Partitioned Columns in PostgreSQL

One of the most valuable things about partitioned tables is pruning - the database's ability to eliminate entire partitions based on a query predicate. Under conventional wisdom, pruning can only be achieved when querying by the partition key - this makes choosing the right key extremely difficult. However, if your data follows certain patterns, using some clever tricks you can achieve pruning even when filtering by non-partition key columns. In this article, I demonstrate how to achieve partition pruning when filtering by non-partition key columns. image by abstrakt design Table of Contents Table Partition Partition Pruning for Key Columns Local Indexes Global Indexes Pruning on Non-Partition Key Columns Talking to the Optimizer The constraint_exclusion Parameter Introducing Outliers Handling Outliers Gaps and Islands The Backstory Final Thoughts Table Partition Imagine you run a popular website with many users. Your product team wants to gain some insight into how the system is…