知识点滴 -- KingbaseES 分区剪枝功能详解

原创金仓数据库 2024-07-04

251

一、分区剪枝功能

分区剪枝(Partition Pruning)是针对分区表查询的性能优化技术。在分区剪枝中，优化器分析SQL语句中的 From 和 Where 子句，以构建分区访问列表，消除不需要的分区访问。分区剪枝大大减少了从磁盘检索数据所需的IO量，提高了查询性能并优化了资源利用率。

PS：KingbaseES 通过参数 enable_partition_pruning 设置启用或禁用分区表分区剪枝能力。

根据实际的SQL语句，KingbaseES 可使用静态或动态剪枝。静态剪枝发生在 plan 阶段，在 plan 阶段就已确定需要访问的有关分区的信息。动态剪枝发生在 execute 阶段，这意味着语句要访问的确切分区在 plan 阶段是未知的。静态剪枝的示例场景是一个SQL语句，该语句包含一个Where 条件，分区键列上有一个常量文本。动态修剪的一个例子是在 Where 条件中使用运算符或函数。

当您在范围或列表分区列上使用 between ... and 、like、 =、>、< 和 IN 列表谓词时，以及当您在哈希分区列中使用 = 或 IN 列表谓词时，KingbaseES 将修剪分区。

本文例子基于以下表

CREATE TABLE orders_range
(
    object_id  integer,
    object_name char(256)
)
PARTITION BY range (object_id)
(
  PARTITION p1 VALUES less than(10000),
  PARTITION p2 VALUES less than(20000),
  PARTITION p3 VALUES less than(30000),
  PARTITION p4 VALUES less than(40000),
  PARTITION p_max VALUES less than(maxvalue)
);

insert into orders_range select generate_series(1,100000),md5(random());

二、如何确定是否已使用分区修剪

不仅在给定查询的规划期间可以执行分区剪枝，在其执行期间也能执行分区剪枝。这非常有用，因为如果子句中包含查询规划时值未知的表达式时，这可以剪枝掉更多的分区；例如在 prepare 语句中定义的参数会使用从子查询拿到的值，或者嵌套循环连接内侧关系上的参数化值。执行期间的分区剪枝可能在下列任何时刻执行：

在查询计划的初始化期间。对于执行的初始化阶段就已知值的参数，可以在这里执行分区剪枝。这个阶段中被剪枝掉的分区将不会显示在查询的 explain 或 explain analyze 结果中。
在查询的实际执行期间。这里可以使用只有在实际查询执行时才能知道的值执行分区剪枝。这包括来自子查询的值以及来自执行时参数的值（例如来自于参数化嵌套循环连接的参数）。由于在查询执行期间这些参数的值可能会改变多次，所以只要分区剪枝使用到的执行参数发生改变，就会执行一次分区剪枝。要判断分区是否在这个阶段被剪枝，需要仔细地观察 explain analyze 输出中的 loop 属性。对应于不同分区的子计划可以具有不同的值，这取决于在执行期间每个分区被修剪的次数。如果每次都被剪枝，有些分区可能会显示为 (never executed)。

三、静态分区修剪

根据静态谓词确定何时使用静态修剪。在许多情况下，优化器确定编译时要访问的分区。如果使用静态谓词，则会发生静态分区修剪。如果在解析时，优化器可以识别访问的连续分区集，则执行计划中，将显示正在访问的分区的条件范围。

test=# explain analyze select * from orders_range where object_id=12345;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on orders_range_p2  (cost=0.00..496.00 rows=1 width=264) (actual time=0.236..0.901 rows=1 loops=1)
   Filter: (object_id = 12345)
   Rows Removed by Filter: 9999

四、动态分区修剪

如果可以修剪，但无法进行静态修剪，则进行动态修剪，因为分区键值仅在执行时获知。

1. 使用绑定变量进行动态修剪

对分区列使用绑定变量的语句会导致动态修剪。如：

 do
 $$
     declare
         c1 text;
     begin
         for c1 in execute 'explain select * from orders_range where object_id = :vid' using (random() * 100000)::int loop
             raise info '%',c1;
         end loop;
     end;
 $$;
 
信息:  Seq Scan on orders_range_p_max  (cost=0.00..2973.01 rows=1 width=264)
信息:  Filter: (object_id = 79184)
ANONYMOUS BLOCK

2. 使用子查询进行动态修剪

对分区列显式使用子查询的语句会导致动态修剪。分区节点的(never executed)，表示执行了分区修剪。

explain analyze 
with v1 as (select 33333 id from dual )
select count(*) from orders_range where object_id = (select id from v1);

                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4958.05..4958.06 rows=1 width=8) (actual time=1.029..1.030 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on dual  (cost=0.00..1.01 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
     ->  Append  (cost=0.00..4957.02 rows=5 width=0) (actual time=0.393..1.024 rows=1 loops=1)
         ->  Seq Scan on orders_range_p1  (cost=0.00..495.99 rows=1 width=0) (never executed)
               Filter: (object_id = $0)
         ->  Seq Scan on orders_range_p2  (cost=0.00..496.00 rows=1 width=0) (never executed)
               Filter: (object_id = $0)
         ->  Seq Scan on orders_range_p3  (cost=0.00..496.00 rows=1 width=0) (never executed)
               Filter: (object_id = $0)
         ->  Seq Scan on orders_range_p4  (cost=0.00..496.00 rows=1 width=0) (actual time=0.381..1.011 rows=1 loops=1)
               Filter: (object_id = $0)
               Rows Removed by Filter: 9999
         ->  Seq Scan on orders_range_p_max  (cost=0.00..2973.01 rows=1 width=0) (never executed)
               Filter: (object_id = $0)


explain analyze 
with v1 as (select 33333 id from dual )
select count(*) from orders_range where object_id in (select id from v1);
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=497.02..497.03 rows=1 width=8) (actual time=0.859..0.860 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=0.00..497.02 rows=1 width=0) (actual time=0.305..0.854 rows=1 loops=1)
         ->  Seq Scan on orders_range_p4  (cost=0.00..496.00 rows=1 width=0) (actual time=0.299..0.847 rows=1 loops=1)
               Filter: (object_id = 33333)
               Rows Removed by Filter: 9999
         ->  Seq Scan on dual  (cost=0.00..1.01 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)