知识点滴 -- KingbaseES 多列分区方法选择对分区裁剪的影响

前言

KingbaseES 对于多列分区，可以选择单级多列的范围分区，也可以选择范围加子分区的方式。但二者在不同场景下对于性能是有差异的，这里的性能差异主要是分区裁剪引起的差异。

构建例子

创建两张分区表，采取不同的分区策略：

create table t1_part_range(id1 integer,id2 integer, name text)
partition by range(id1,id2)
(
  partition p_1_1 values less than(1,2),
  partition p_1_2 values less than(1,3),
  partition p_1_3 values less than(1,4),
  partition p_1_4 values less than(1,maxvalue),
  partition p_2_1 values less than(2,2),
  partition p_2_2 values less than(2,3),
  partition p_2_3 values less than(2,4),
  partition p_2_4 values less than(2,maxvalue),
  partition p_3_1 values less than(3,2),
  partition p_3_2 values less than(3,3),
  partition p_3_3 values less than(3,4),
  partition p_3_4 values less than(3,maxvalue)
);

create table t1_part_list(id1 integer,id2 integer, name text)
partition by range(id1) subpartition by list(id2)
subpartition template
(
  subpartition sub_1 values(1),
  subpartition sub_2 values(2),
  subpartition sub_3 values(3),
  subpartition sub_4 values(default)
)
(
  partition p_1 values less than(2),
  partition p_2 values less than(3),
  partition p_3 values less than(4)
);

t1_part_range 采取的单级多列范围分区，t1_part_list 采用的是两级范围+列表分区。二者当第一个分区列值未提供情况时，分区裁剪是有差异的。

分区裁剪差异比较

范围分区裁剪：当 id1 分区列条件未提供时，需要扫描所有分区。

test=# explain select * from t1_part_range where id2=3;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Append  (cost=0.00..298.86 rows=72 width=40)
   ->  Seq Scan on t1_part_range_p_1_1  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_1_2  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_1_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_1_4  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_2_1  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_2_2  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_2_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_2_4  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_3_1  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_3_2  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_3_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_range_p_3_4  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
(25 rows)

列表分区裁剪：当id1分区列条件未提供时，只需要扫描三个分区。

test=# explain select * from t1_part_list where id2=3;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Append  (cost=0.00..74.72 rows=18 width=40)
   ->  Seq Scan on t1_part_list_p_1_p_1_sub_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_list_p_2_p_2_sub_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_list_p_3_p_3_sub_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
(7 rows)

Oracle 对于多列范围分区执行计划

对于同样的多列分区，执行计划如下：

Execution Plan
----------------------------------------------------------
Plan hash value: 561242485

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |     3 |  1487   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |               |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE MULTI-COLUMN|               |   435K|  1274K|  1487   (1)| 00:00:01 |KEY(MC)|KEY(MC)|
|*  3 |    TABLE ACCESS FULL          | T1_PART_RANGE |   435K|  1274K|  1487   (1)| 00:00:01 |KEY(MC)|KEY(MC)| 

---------------------------------------------------------------------------------------------------------------　　

实际只需扫描其中部分分区，而不需要扫描所有分区。具体分区：p_1_1 ，p_1_3 ， p_2_1 ， p_2_3， p_3_1， p_3_3 。 从这点看，oracle 的优化器更为智能。