GaussDB分区表查询性能异常

CY 学数据库 2023-05-30

129

GaussDB分区表查询性能异常

问题现象

使用分区表进行相关查询业务，SQL性能慢。

原因分析

导致分区表业务慢的常见原因有以下几种：

分区索引失效，顺序扫描导致的SQL性能慢
分区表无法进行分区剪枝导致的SQL性能慢
SQL计划选择非最优导致的SQL性能慢

处理方法

1.判断是否存在索引异常的行为。

部分分区DDL如果不带UPDATE GLOBAL INDEX子句，会导致分区表Global索引失效。同时用户也可以使用ALTER TABLE或者ALTER INDEX使分区表索引失效。当分区表或者部分分区上索引不可用后，会导致查询SQL无法走索引扫描，从而出现慢SQL场景。

查询索引类型和状态

分区表的索引有两种，Local索引和Global索引。Local索引会在每个分区创建一个索引，Global索引会在整个分区表上创建一个全局索引。对于Local索引，需要确认整个索引自身状态和目标查询分区的索引状态；对于Global索引，只需要确认整个索引自身状态。

可以通过系统表pg_index查询分区表的所有索引，并获取索引是否可用的状态，其索引类型在pg_class中给出。下面给出了查询分区表t1的全部索引状态及索引类型的命令参考：

gaussdb=# select c.relname, i.indisusable, c.relkind from pg_class c join pg_index i on c.oid=i.indexrelid     join pg_class r on i.indrelid=r.oid where r.relname='t1';       relname       | indisusable | relkind  --------------------+-------------+---------  t1_c1_idx          | t           | i  t1_c2_tableoid_idx | f           | I

其中indisusable字段为't'表示该索引可用，为'f'表示索引已经失效，在查询业务中该索引无法使用;relkind为'i'表示该索引为Local索引，为'I'表示该索引为Global索引。

或者通过元语句\d命令查询分区表的索引信息：

gaussdb=# \d t1       Table "public.t1"  Column |  Type   | Modifiers  --------+---------+-----------  c1     | integer |   c2     | integer |  Indexes:     "t1_c1_idx" btree (c1) LOCAL TABLESPACE pg_default     "t1_c2_tableoid_idx" btree (c2) TABLESPACE pg_default UNUSABLE Partition By RANGE(c1) Number of partitions: 2 (View pg_partition to check each partition range.)

可以看到表t1的两条索引t1_c1_idx、t1_c2_tableoid_idx被列出。其中t1_c1_idx带有LOCAL关键字，表示该索引为Local索引，t1_c2_tableoid_idx没有LOCAL关键字，表示该索引为Global索引。同时t1_c2_tableoid_idx标记了UNUSABLE，表示该索引不可用。

查询索引分区状态

对于Local索引，还需要确认目标查询分区的索引状态，这个信息可以从系统表pg_partition中查询。

gaussdb=# select p.relname,p.indisusable from pg_partition p join pg_class c on p.parentid=c.oid where c.relname='t1_c1_idx';   relname  | indisusable  -----------+-------------  p1_c1_idx | t  p2_c1_idx | f (2 rows)

可以看到索引分区p1_c1_idx可用，索引分区p2_c1_idx不可用。在分区p2上的查询无法使用索引t1_c1_idx。

重建异常的索引/索引分区

如果分区表的索引/索引分区状态异常，会导致查询SQL无法走索引扫描，需要重建索引/索引分区。重建索引的命令如下：

ALTER INDEX t1_c2_tableoid_idx REBUILD;

重建索引分区的命令如下：

ALTER INDEX t1_c1_idx REBUILD PARTITION p2_c1_idx;

2.判断分区表是否存在剪枝异常的场景。

当分区表的分区键所在列存在条件时，可以触发分区剪枝。数据库会在优化器/执行器阶段识别到需要扫描的分区，而不会扫描全部分区。只有分区表的业务是顺序扫描，或者使用Local索引进行扫描时候，才可能触发分区剪枝；Global索引不会触发分区剪枝，这是因为Global索引是在整个分区表上创建的单个索引，不存在剪枝的概念。

剪枝是被动触发的，当查询业务满足分区剪枝条件时，会自动触发分区剪枝。

判断是否触发了分区剪枝

可以通过查询计划来判断是否触发了分区剪枝。当扫描分区数少于分区总数时，即触发了分区剪枝。分区剪枝分为静态剪枝和动态剪枝，静态剪枝是指在优化器阶段就能识别到裁剪的分区；动态剪枝是指优化器阶段只能确定可以进行剪枝，但不知道具体裁剪到哪个分区，在执行器阶段才会确定裁剪的目标分区。

下面的业务触发了分区静态剪枝，计划中的'Iterations: 1'表示扫描了1个分区，'Selected Partitions: 1'表示扫描的目标分区下标是1。

这个下标是一个逻辑数组下标，对于范围分区/间隔分区，按照分区定义上界值升序排列；对于列表分区，按照第一个枚举值升序排列；对于哈希分区，按照计算后的哈希值升序排列。可以通过函数pg_get_tabledef获取分区定义，其列出的分区即为该对应下标。

gaussdb=# explain select * from t1 where c1 < 100;                               QUERY PLAN                                -----------------------------------------------------------------------  Partition Iterator  (cost=0.00..27.86 rows=716 width=8)    Iterations: 1    ->  Partitioned Seq Scan on t1  (cost=0.00..27.86 rows=716 width=8)          Filter: (c1 < 100)          Selected Partitions:  1 (5 rows)

下面的业务没有触发分区剪枝，计划中的'Iterations: 2'表示扫描了2个分区，'Selected Partitions: 1..2'表示扫描的目标分区下标是1和2。可以看到扫描的分区数等于分区表的总分区数，表示未进行任何分区剪枝。

gaussdb=# explain select * from t1;                                QUERY PLAN                                ------------------------------------------------------------------------  Partition Iterator  (cost=0.00..31.49 rows=2149 width=8)    Iterations: 2    ->  Partitioned Seq Scan on t1  (cost=0.00..31.49 rows=2149 width=8)          Selected Partitions:  1..2 (4 rows)

下面的业务触发了分区动态剪枝，计划中的'Iterations: PART'和'Selected Partitions: PART'表示优化器识别到分区表可以进行分区剪枝，但具体

gaussdb=# prepare p1 as select * from t1 where c1 < $1; gaussdb=# explain execute p1(100);                                          QUERY PLAN                                          --------------------------------------------------------------------------------------------  Partition Iterator  (cost=9.80..28.75 rows=716 width=8)    Iterations: PART    ->  Partitioned Bitmap Heap Scan on t1  (cost=9.80..28.75 rows=716 width=8)          Recheck Cond: (c1 < $1)          Selected Partitions:  PART          ->  Partitioned Bitmap Index Scan on t1_c1_idx  (cost=0.00..9.62 rows=716 width=0)                Index Cond: (c1 < $1)                Selected Partitions:  PART (8 rows)

支持分区剪枝的场景

当分区键所在列存在条件时，可以进行分区剪枝。条件剪枝支持场景为：比较表达式（<，<=，=，>=，>）、逻辑表达式（AND、OR）、数组表达式。需要注意的是，列表和哈希分区不支持除等号外的其他比较表达式的剪枝。如果条件全为常量，则进行静态剪枝；如果条件带有参数、部分隐式转换、immutable函数，则进行动态剪枝。

当分区表作为参数化路径计划的内表且分区键所在列为索引检索条件时，支持动态剪枝。

不支持分区剪枝的场景

分区键所在列的条件为子查询表达式、无法直接强转的类型转换、stable/volatile函数时，不支持分区剪枝。

分区表在生成除参数化路径外的其他JOIN计划时，不支持动态剪枝。

业务改写适配分区剪枝

当业务设计不合理，导致原本逻辑上可以走分区剪枝的计划，最终未走分区剪枝时，可以改写业务以适配分区剪枝，从而提升分区表查询业务性能。比如使用HINT固定计划、在客户端修改绑参类型等方法。

3.判断分区表是否是因为SQL计划选择非最优。

gaussdb

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

关注作者

GaussDB分区表查询性能异常

GaussDB分区表查询性能异常

评论