enable_partition_pruning

• 17
• 16
• 15
• 14
• 13
• 12
• 11

当 enable_partition_pruning 设置为默认值 on 时，对按 range 基于 timestamp 列进行分区的表的查询将生成如下所示的查询计划

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)

请注意，即使该列上存在索引，计划程序也会选择顺序扫描，在这种情况下这是合理的，因为查询是从日期范围内选择所有值，并且分区剪枝意味着已经选择了包含要检索的数据的正确分区。对于仅返回分区中数据子集的更严格的查询（例如），索引可能会被使用。

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)

当 enable_partition_pruning 设置为 off 时，上面显示的第一个查询将导致对所有不满足索引条件的分区进行索引扫描。

                                                                                              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)

• PostgreSQL 文档： enable_partition_pruning