enable_partition_pruning
enable_partition_pruning is a configuration parameter determining whether partition pruning should be implemented by the planner.
enable_partition_pruning was added in PostgreSQL 11.
Usage
enable_partition_pruning enables the planner to take into account the definition of each partition and exclude it from being scanned if it does not contain any rows which would be returned by the query. This prevents expensive and unneccessary table scans.
constraint_exclusion is the equivalent parameter for tables using inheritance partitiioning.
Default value
The default value for enable_partition_pruning is: on.
Change history
- PostgreSQL 11
- added (commit 055fb8d3)
Examples
With enable_partition_pruning set to the default on, a query to a table partitioned by range on a timestamp column will result in a query plan like the following:
appdb=> EXPLAIN SELECT oat.* FROM object_access_tracking oat INNER JOIN object o ON (o.object_id = oat.object_id) WHERE oat.access_timestamp >= '2022-08-01'; QUERY PLAN --------------------------------------------------------------------------------------------------------- Hash Join (cost=149.45..15355.26 rows=186075 width=183) Hash Cond: (oat.object_id = o.object_id) -> Append (cost=0.00..14716.96 rows=186075 width=182) -> Seq Scan on object_access_tracking_2022_08 oat (cost=0.00..12679.66 rows=170836 width=183) Filter: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone) -> Seq Scan on object_access_tracking_2022_09 oat_1 (cost=0.00..1090.30 rows=15062 width=177) Filter: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone) -> Seq Scan on object_access_tracking_2022_10 oat_2 (cost=0.00..16.62 rows=177 width=125) Filter: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone) -> Hash (cost=85.31..85.31 rows=5131 width=4) -> Seq Scan on object o (cost=0.00..85.31 rows=5131 width=4) (11 rows)
Note that even though there is an index on the column, the planner selects a sequential scan, which is reasonable in this case as the query is selecting all values from the date range, and partition pruning means the correct partitions containing the data to be retrieved have been selected already. The index is likely be used for a more restrictive query returning only the subset of data in a partition, e.g.:
appdb=> EXPLAIN SELECT oat.* FROM object_access_tracking oat INNER JOIN object o ON (o.object_id = oat.object_id) WHERE oat.access_timestamp BETWEEN '2022-08-20' AND '2022-08-31'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=149.87..10796.03 rows=65913 width=182) Hash Cond: (oat.object_id = o.object_id) -> Index Scan using object_access_tracking_2022_08_pkey on object_access_tracking_2022_08 oat (cost=0.42..10473.42 rows=65913 width=182) Index Cond: ((access_timestamp >= '2022-08-20 00:00:00+02'::timestamp with time zone) AND (access_timestamp <= '2022-08-31 00:00:00+02'::timestamp with time zone)) -> Hash (cost=85.31..85.31 rows=5131 width=4) -> Seq Scan on object o (cost=0.00..85.31 rows=5131 width=4) (6 rows)
With enable_partition_pruning set to off, the first query shown above will result in an index scan over all partitions which do not fulfill the index conditions:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=149.86..65698.99 rows=65953 width=182) (actual time=66.444..99.291 rows=65067 loops=1)
Hash Cond: (oat.object_id = o.object_id)
-> Append (cost=0.41..65376.27 rows=65953 width=182) (actual time=65.547..85.194 rows=65067 loops=1)
-> Index Scan using object_access_tracking_2019_08_pkey on object_access_tracking_2019_08 oat (cost=0.41..1128.77 rows=1 width=192) (actual time=2.718..2.718 rows=0 loops=1)
Index Cond: ((access_timestamp >= '2022-08-20 00:00:00+02'::timestamp with time zone) AND (access_timestamp <= '2022-08-31 00:00:00+02'::timestamp with time zone))
[ ... lines omitted for brevity ... ]
-> Index Scan using object_access_tracking_2022_06_pkey on object_access_tracking_2022_06 oat_34 (cost=0.42..1723.25 rows=15 width=176)
Index Cond: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone)
-> Index Scan using object_access_tracking_2022_07_pkey on object_access_tracking_2022_07 oat_35 (cost=0.42..1796.70 rows=15 width=175)
Index Cond: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone)
-> Seq Scan on object_access_tracking_2022_08 oat_36 (cost=0.00..12673.74 rows=170362 width=182)
Filter: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone)
-> Seq Scan on object_access_tracking_2022_09 oat_37 (cost=0.00..1085.60 rows=14687 width=176)
Filter: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone)
-> Seq Scan on object_access_tracking_2022_10 oat_38 (cost=0.00..16.62 rows=177 width=125)
Filter: (access_timestamp >= '2022-08-01 00:00:00+02'::timestamp with time zone)
-> Hash (cost=85.31..85.31 rows=5131 width=4)
-> Seq Scan on object o (cost=0.00..85.31 rows=5131 width=4)
(83 rows)
References
- PostgreSQL documentation: enable_partition_pruning
