分区剪枝

分区剪枝是指优化器自动提取出需要扫描的分区，减少扫描的数据块，从而避免全表扫描，提高性能。Vastbase支持静态分区剪枝和动态分区剪枝。

分区剪枝功能仅在数据库版本为V2.2 Build 15(Patch No.3)及以上版本支持。

静态分区剪枝

功能描述

静态分区剪枝是一种优化技术，用于提高查询性能。当在对分区表执行SQL查询时，如果查询条件中包含分区键，优化器会在编译阶段就能够判断出哪些分区是不必要的，并且可以直接排除这些分区的扫描。

支持的分区类型及表达式类型：

分区类型	表达式	分区等级
范围(Range)分区	<,<=,=,>,>=,like,any,all	一级、二级分区
列表(List)分区	<,<=,>,>=,<>(不等于)	一级、二级分区
哈希(Hash)分区	=	一级、二级分区
间隔(Interval)分区	<,<=,=,>,>=,any,all	一级、二级分区

注意事项

多个分区键的List分区表，操作符<>存在剪枝不完全的情况，不建议使用。
List分区表查询中如果有多个<>操作符的查询条件，存在剪枝不完全的情况，不建议使用。
二级分区表分区键一致时存在剪枝不完全的情况，不建议使用。
表达式like仅支持字符类型。

示例

示例1: 一级分区表静态剪枝。

1、创建测试表并插入数据。

create table par1_1187644(id int,a1 text,a2 date,a3 varchar(30))
partition by list (id)
(
partition p1 values(100),
partition p2 values(200),
partition p3 values(300),
partition p4 values(400),
partition p5 values(null));

insert into par1_1187644 values(100,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187644 values(200,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187644 values(300,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187644 values(400,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187644 values(null,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));

2、指定条件查询数据。

select * from par1_1187644 where id>212 limit 5;

结果返回如下：

 id  | a1 |         a2          | a3
-----+----+---------------------+----
 300 | A  | 2022-01-01 00:00:00 | A
 300 | B  | 2022-01-02 00:00:00 | B
 300 | C  | 2022-01-03 00:00:00 | C
 300 | D  | 2022-01-04 00:00:00 | D
 300 | E  | 2022-01-05 00:00:00 | E
(5 rows)

3、查看执行计划验证剪枝效果。

explain (costs off) select * from par1_1187644 where id>212 limit 5;

结果返回如下，可以看出只扫描了部分分区：

                    QUERY PLAN
--------------------------------------------------
 Limit
   ->  Partition Iterator
         Iterations: 2
         ->  Partitioned Seq Scan on par1_1187644
               Filter: (id > 212)
               Selected Partitions:  3..4
(6 rows)

示例2: 二级分区表静态剪枝。

1、创建二级分区表。

create table par1_1187650(id int,a1 text,a2 date,a3 varchar(30))
partition by range (id)
subpartition by list(a1)
(
partition p1 values less than(100)(
subpartition p1_1 values('g'),
subpartition p1_2 values('n'),
subpartition p1_3 values('z'),
subpartition p1_4 values(null)),
partition p2 values less than(200)(
subpartition p2_1 values('g'),
subpartition p2_2 values('n'),
subpartition p2_3 values('z'),
subpartition p2_4 values(null)),
partition p3 values less than(300)(
subpartition p3_1 values('g'),
subpartition p3_2 values('n'),
subpartition p3_3 values('z'),
subpartition p3_4 values(null)),
partition p4 values less than(401)(
subpartition p4_1 values('g'),
subpartition p4_2 values('n'),
subpartition p4_3 values('z'),
subpartition p4_4 values(null)));

2、插入测试数据。

insert into par1_1187650 values(generate_series(1,100),'g',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187650 values(generate_series(101,200),'n',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187650 values(generate_series(201,300),'z',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1187650 values(generate_series(301,400),null,generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));

3、指定条件查询数据。

select * from par1_1187650 where id>212 and a1>'d' limit 5;

结果返回如下：

 id  | a1 |         a2          | a3
-----+----+---------------------+----
 213 | z  | 2022-01-13 00:00:00 | M
 214 | z  | 2022-01-14 00:00:00 | N
 215 | z  | 2022-01-15 00:00:00 | O
 216 | z  | 2022-01-16 00:00:00 | P
 217 | z  | 2022-01-17 00:00:00 | Q
(5 rows)

4、查看执行计划验证剪枝效果。

explain (costs off) select * from par1_1187650 where id>212 and a1>'d' limit 5;

结果返回如下，可以看出只扫描了部分一级、二级分区，其中Selected Subpartitions字段的值代表二级分区的数量。

                       QUERY PLAN
---------------------------------------------------------
 Limit
   ->  Partition Iterator
         Iterations: 2, Sub Iterations: 6
         ->  Partitioned Seq Scan on par1_1187650
               Filter: ((id > 212) AND (a1 > 'd'::text))
               Selected Partitions:  3..4
               Selected Subpartitions:  3:3, 4:3
(7 rows)

动态分区剪枝

功能描述

动态分区剪枝是与静态分区剪枝相对的一种优化技术，同样用于提高查询性能。在处理分区表时，当SQL查询条件中包含变量或者表达式，它们的值只有在运行时才能确定的情况下，数据库引擎能够在执行阶段根据实际的查询参数值来决定应当访问哪些分区，并排除不必要的分区扫描。

Vastbase 支持对Range、List、Hash、Interval分区表进行动态分区剪枝，支持的场景如下：

支持PREPARE/EXECUTE语法对一级、二级分区表动态分区剪枝。需注意查询条件需包含分区键，且条件值为参数。
支持InitPlan场景的一级、二级分区表动态分区剪枝。需注意分区键的条件为子连接，且为非相关子连接。
支持Nested Loop且连接条件可下推至分区表场景的一级、二级分区表动态分区剪枝，查询走nestloop计划，且包含分区键的条件，其中分区键存在本地分区索引。

前置条件

使用动态分区剪枝需设置GUC参数enable_runtime_prune为on，该参数默认值为on，参数类型为USERSET。

示例

1、创建测试表并插入测试数据。

create table par1_1188069(id int,a1 text,a2 date,a3 varchar(30))
partition by range (id)
subpartition by list(a1)
(
partition p1 values less than(100)(
subpartition p1_1 values('d'),
subpartition p1_2 values('k'),
subpartition p1_3 values('q'),
subpartition p1_4 values(null)),
partition p2 values less than(200)(
subpartition p2_1 values('d'),
subpartition p2_2 values('k'),
subpartition p2_3 values('q'),
subpartition p2_4 values(null)),
partition p3 values less than(300)(
subpartition p3_1 values('d'),
subpartition p3_2 values('k'),
subpartition p3_3 values('q'),
subpartition p3_4 values(null)),
partition p4 values less than(401)(
subpartition p4_1 values('d'),
subpartition p4_2 values('k'),
subpartition p4_3 values('q'),
subpartition p4_4 values(null)));

create index on par1_1188069(id)local;

insert into par1_1188069 values(generate_series(1,100),'d',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1188069 values(generate_series(101,200),'k',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1188069 values(generate_series(201,300),'q',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));
insert into par1_1188069 values(generate_series(301,400),null,generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25));

2、确认GUC参数enable_runtime_prune的值是否为on。

show enable_runtime_prune;

结果返回如下：

 enable_runtime_prune
----------------------
 on
(1 row)

3、使用PREPARE语法创建预备查询语句。

prepare i1_1188069(int,int) as select * from par1_1188069 where id>$1 and id<$2 limit 3;

4、查看执行计划。

explain (analyze,costs off) execute i1_1188069(130,230);

结果返回如下，可以看出只扫描了部分分区：

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.107..0.109 rows=3 loops=1)
   ->  Partition Iterator (actual time=0.106..0.108 rows=3 loops=1)
         Iterations: PART
         ->  Partitioned Index Scan using par1_1188069_id_idx on par1_1188069 (actual time=0.018..0.019 rows=3 loops=2)
               Index Cond: ((id > $1) AND (id < $2))
               Selected Partitions:  PART
               Selected Subpartitions:  PART
 Total runtime: 0.288 ms
(8 rows)

5、关闭GUC参数enable_runtime_prune对比查询效果。

set enable_runtime_prune off;
show enable_runtime_prune ;

结果返回如下：

 enable_runtime_prune
----------------------
 off
(1 row)

6、使用PREPARE语法创建预备查询语句。

prepare i1_1188070(int,int) as select * from par1_1188069 where id>$1 and id<$2 limit 3;

7、查看执行计划。

explain (analyze,costs off) execute i1_1188070(130,230);

结果返回如下，可以看出执行了全表扫描。

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.045..0.047 rows=3 loops=1)
   ->  Partition Iterator (actual time=0.044..0.045 rows=3 loops=1)
         Iterations: 4, Sub Iterations: 16
         ->  Partitioned Index Scan using par1_1188069_id_idx on par1_1188069 (actual time=0.025..0.026 rows=3 loops=6)
               Index Cond: ((id > $1) AND (id < $2))
               Selected Partitions:  1..4
               Selected Subpartitions:  ALL
 Total runtime: 0.200 ms
(8 rows)

VastbaseG100

分区剪枝

静态分区剪枝

功能描述

注意事项

示例

动态分区剪枝

功能描述

前置条件

示例

弹框头部