云和恩墨MogDB数据库分区管理动态分区裁剪

原创由迪 2023-06-07

259

动态分区裁剪

动态分区裁剪主要支持以下三种类型：

Prepare-Execute（绑定变量）执行方式，分区裁剪表达式和静态裁剪类似，唯一区别是静态表达式一侧为Const，动态分区裁剪表达式一侧为外部参数或外部参数组成的表达式。例如：
```
prepare ps(int) as select * from prune_tt01 where a > $1;
explain verbose execute ps(12);
```
查询语句中包含子查询，主要分为对主查询中的分区表进行裁剪和子查询中的分区表进行裁剪两种方式：
- 对主查询中的分区表进行裁剪，子查询一般为非相关子查询，子查询作为主查询分区裁剪表达式的一侧，另一侧为分区键，例如
```
select * from prune_tt01 where a > (select a from t2 limit 1);
```
- 对子查询中的分区表进行裁剪，子查询一般为相关子查询，且该子查询不支持提升。注意：相关子查询中涉及到对分区表多次裁剪，explain analyze显示的最后一次的裁剪结果。例如： any子查询：
```
explain analyze select * from t2 where a > any(select a from prune_tt01 where prune_tt01.a = t2.a);
```
  exists子查询：
```
explain analyze select * from t2 where exists (select a from prune_tt01 where prune_tt01.a = t2.a limit 1);
```
NestLoop参数化查询，即查询计划为NestLoop+indexscan查询方式，要求分区表的分区键为两个表的连接键，同时分区键上创建有索引。

以下为动态分区裁剪的案例。

drop table if exists prune_tt01;
CREATE TABLE prune_tt01(a int, b int)
PARTITION BY RANGE(a)
(
  PARTITION prune_tt01_p1 VALUES LESS THAN(5),
  PARTITION prune_tt01_p2 VALUES LESS THAN(10),
  PARTITION prune_tt01_p3 VALUES LESS THAN(15),
  PARTITION prune_tt01_p4 VALUES LESS THAN(MAXVALUE)
);
INSERT INTO prune_tt01 VALUES (generate_series(1, 20), generate_series(1,20));
CREATE INDEX index_prune_tt01 ON prune_tt01 USING btree(a) LOCAL;

drop table if exists tt02;
create table tt02(a int, b int);
INSERT INTO tt02 VALUES (generate_series(1, 20), generate_series(1,20));

prepare-execute（绑定变量）场景：

MogDB=# prepare ps(int) as select * from prune_tt01 where a > $1;
PREPARE
MogDB=# explain verbose execute ps(12);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..36.86 rows=716 width=8)
   Output: a, b
   Iterations: PART
   Selected Partitions:  PART
   ->  Partitioned Seq Scan on public.prune_tt01  (cost=0.00..36.86 rows=716 width=8)
         Output: a, b
         Filter: (prune_tt01.a > $1)
(7 rows)

MogDB=# explain analyze execute ps(12);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..36.86 rows=716 width=8) (actual time=0.099..0.110 rows=8 loops=1)
   Iterations: 2
   Selected Partitions:  3..4
   ->  Partitioned Seq Scan on prune_tt01  (cost=0.00..36.86 rows=716 width=8) (actual time=0.031..0.034 rows=8 loops=2)
         Filter: (a > $1)
         Rows Removed by Filter: 3
 Total runtime: 0.218 ms
(7 rows)

子查询场景：

对主查询中的分区表进行裁剪：

MogDB=# explain verbose select * from prune_tt01 where a > (select a from t2 where a > 12 limit 1);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.04..36.90 rows=716 width=8)
   Output: prune_tt01.a, prune_tt01.b
   Iterations: PART
   Selected Partitions:  PART
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.04 rows=1 width=4)
           Output: t2.a
           ->  Seq Scan on public.t2  (cost=0.00..1.75 rows=49 width=4)
                 Output: t2.a
                 Filter: (t2.a > 12)
   ->  Partitioned Seq Scan on public.prune_tt01  (cost=0.00..36.86 rows=716 width=8)
         Output: prune_tt01.a, prune_tt01.b
         Filter: (prune_tt01.a > $0)
(13 rows)

MogDB=# explain analyze select * from prune_tt01 where a > (select a from t2 where a > 12 limit 1);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.04..36.90 rows=716 width=8) (actual time=0.172..0.180 rows=7 loops=1)
   Iterations: 2
   Selected Partitions:  3..4
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.04 rows=1 width=4) (actual time=0.093..0.093 rows=1 loops=1)
           ->  Seq Scan on t2  (cost=0.00..1.75 rows=49 width=4) (actual time=0.091..0.091 rows=1 loops=1)
                 Filter: (a > 12)
                 Rows Removed by Filter: 12
   ->  Partitioned Seq Scan on prune_tt01  (cost=0.00..36.86 rows=716 width=8) (actual time=0.020..0.020 rows=7 loops=2)
         Filter: (a > $0)
         Rows Removed by Filter: 4
 Total runtime: 0.301 ms
(12 rows)

对子查询中的分区表进行裁剪：

any子查询：

MogDB=# explain verbose  select * from t2 where a > any(select a from prune_tt01 where prune_tt01.a = t2.a);
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on public.t2  (cost=0.00..582.83 rows=30 width=8)
   Output: t2.a, t2.b
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4)
           Output: prune_tt01.a
           Iterations: PART
           Selected Partitions:  PART
           ->  Partitioned Bitmap Heap Scan on public.prune_tt01  (cost=4.34..15.01 rows=11 width=4)
                 Output: prune_tt01.a
                 Recheck Cond: (prune_tt01.a = t2.a)
                 ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0)
                       Index Cond: (prune_tt01.a = t2.a)
(13 rows)

MogDB=# explain analyze select * from t2 where a > any(select a from prune_tt01 where prune_tt01.a = t2.a);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t2  (cost=0.00..582.83 rows=30 width=8) (actual time=2.130..2.130 rows=0 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 60
   SubPlan 1
     ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4) (actual time=1.860..1.877 rows=20 loops=60)
           Iterations: 1
           Selected Partitions:  4
           ->  Partitioned Bitmap Heap Scan on prune_tt01  (cost=4.34..15.01 rows=11 width=4) (actual time=0.566..0.576 rows=20 loops=60)
                 Recheck Cond: (a = t2.a)
                 Heap Blocks: exact=20
                 ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0) (actual time=0.482..0.482 rows=20 loops=60)
                       Index Cond: (a = t2.a)
 Total runtime: 2.600 ms
(13 rows)

exists子查询：

MogDB=# explain verbose select * from t2 where exists (select a from prune_tt01 where prune_tt01.a = t2.a limit 1);
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t2  (cost=0.00..319.92 rows=30 width=8)
   Output: t2.a, t2.b
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Limit  (cost=4.34..5.31 rows=1 width=4)
           Output: prune_tt01.a
           ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4)
                 Output: prune_tt01.a
                 Iterations: PART
                 Selected Partitions:  PART
                 ->  Partitioned Bitmap Heap Scan on public.prune_tt01  (cost=4.34..15.01 rows=11 width=4)
                       Output: prune_tt01.a
                       Recheck Cond: (prune_tt01.a = t2.a)
                       ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0)
                             Index Cond: (prune_tt01.a = t2.a)
(15 rows)

MogDB=# explain analyze select * from t2 where exists (select a from prune_tt01 where prune_tt01.a = t2.a limit 1);
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on t2  (cost=0.00..319.92 rows=30 width=8) (actual time=0.058..0.875 rows=20 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 40
   SubPlan 1
     ->  Limit  (cost=4.34..5.31 rows=1 width=4) (actual time=0.826..0.826 rows=20 loops=60)
           ->  Partition Iterator  (cost=4.34..15.01 rows=11 width=4) (actual time=0.789..0.789 rows=20 loops=60)
                 Iterations: 1
                 Selected Partitions:  4
                 ->  Partitioned Bitmap Heap Scan on prune_tt01  (cost=4.34..15.01 rows=11 width=4) (actual time=0.162..0.162 rows=20 loops=60)
                       Recheck Cond: (a = t2.a)
                       Heap Blocks: exact=20
                       ->  Partitioned Bitmap Index Scan on index_prune_tt01  (cost=0.00..4.33 rows=11 width=0) (actual time=0.123..0.123 rows=20 loops=60)
                             Index Cond: (a = t2.a)
 Total runtime: 1.151 ms
(14 rows)

nestloop场景：

以下参数设置仅为了模拟SQL语句使用nestloop（通常情况下优化器会根据表的数据量选择最优的访问路径）

MogDB=# SET enable_material = OFF;
SET
MogDB=# SET enable_mergejoin = OFF;
SET
MogDB=# SET enable_hashjoin = OFF;
SET

MogDB=# explain verbose select * from prune_tt01 inner join tt02 on prune_tt01.a = tt02.a;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1501.91 rows=23091 width=16)
   Output: prune_tt01.a, prune_tt01.b, tt02.a, tt02.b
   ->  Seq Scan on public.tt02  (cost=0.00..31.49 rows=2149 width=8)
         Output: tt02.a, tt02.b
   ->  Partition Iterator  (cost=0.00..0.57 rows=11 width=8)
         Output: prune_tt01.a, prune_tt01.b
         Iterations: PART
         Selected Partitions:  PART
         ->  Partitioned Index Scan using index_prune_tt01 on public.prune_tt01  (cost=0.00..0.57 rows=11 width=8)
               Output: prune_tt01.a, prune_tt01.b
               Index Cond: (prune_tt01.a = tt02.a)
(11 rows)

MogDB=# explain analyze select * from prune_tt01 inner join tt02 on prune_tt01.a = tt02.a;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1501.91 rows=23091 width=16) (actual time=0.078..0.535 rows=20 loops=1)
   ->  Seq Scan on tt02  (cost=0.00..31.49 rows=2149 width=8) (actual time=0.023..0.030 rows=20 loops=1)
   ->  Partition Iterator  (cost=0.00..0.57 rows=11 width=8) (actual time=0.441..0.462 rows=20 loops=20)
         Iterations: 1
         Selected Partitions:  4
         ->  Partitioned Index Scan using index_prune_tt01 on prune_tt01  (cost=0.00..0.57 rows=11 width=8) (actual time=0.146..0.158 rows=20 loops=20)
               Index Cond: (a = tt02.a)
 Total runtime: 0.770 ms
(8 rows)

MogDB 是云和恩墨基于opengauss 企业级数据库

mogdb

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Mogdb 学习记录（共582篇）

Mogdb 学习指南

云和恩墨MogDB数据库分区管理 动态分区裁剪

动态分区裁剪

文章被以下合辑收录

评论

云和恩墨MogDB数据库分区管理动态分区裁剪