KingbaseES 多列分区的方法与性能

前言

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

例子

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

create table t1_part_ranage(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_ranage 采取的单级多列范围分区，t1_part_list 采用的是两级范围+列表分区。二者当第一个分区列值未提供情况时，分区裁剪是有差异的。

分区裁剪差异

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

test=# explain select * from t1_part_ranage where id2=3;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Append  (cost=0.00..298.86 rows=72 width=40)
   ->  Seq Scan on t1_part_ranage_p_1_1  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_1_2  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_1_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_1_4  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_2_1  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_2_2  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_2_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_2_4  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_3_1  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_3_2  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_p_3_3  (cost=0.00..24.88 rows=6 width=40)
         Filter: (id2 = 3)
   ->  Seq Scan on t1_part_ranage_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)