enable_partition_pruning

• 19
• 18
• 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