openGauss/MogDB 5.0.0版本SQL PATCH使用

适用范围

SQL PATCH主要设计给DBA、运维人员及其他需要对SQL进行调优的角色使用，用户通过其他运维视图或定位手段识别到业务语句存在计划不优导致的性能问题时，可以通过创建SQL PATCH对业务语句进行基于Hint的调优。目前支持行数、扫描方式、连接方式、连接顺序、PBE custom/generic计划选择、语句级参数设置、参数化路径的Hint。此外，对于部分由特定语句触发系统内部问题导致系统可服务性受损的语句，在不对业务语句变更的情况下，也可以通过创建用于单点规避的SQL PATCH，对问题场景提前报错处理，避免更大的损失。

概述

上一篇文章主要是介绍SQL PATCH的特性及使用方法《openGauss/MogDB 5.0.0支持SQL PATCH》。现在这篇讲到了SQL PATCH的使用情况。

问题1

有一张t表，100W行数据，跑同样的查询时，会遇到有时索引扫描有时全表扫描的情况。

openGauss=# \d t
                  Table "public.t"
 Column |              Type              | Modifiers
--------+--------------------------------+-----------
 c1     | integer                        |
 c2     | character varying(50)          |
 c3     | timestamp(0) without time zone |
Indexes:
    "t_c1_idx" btree (c1) TABLESPACE pg_default
复制

执行的查询为

select * from t where c1 < 700000 and c2 ='0312c21fa727e4063d404c6efec4ca48';

select * from t where c1 < 500000 and c2 ='0312c21fa727e4063d404c6efec4ca48';

openGauss=# explain select * from t where c1 < 700000 and c2 ='0312c21fa727e4063d404c6efec4ca48';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..24346.00 rows=1 width=45)
   Filter: ((c1 < 700000) AND ((c2)::text = '0312c21fa727e4063d404c6efec4ca48'::text))
(2 rows)

openGauss=# explain select * from t where c1 < 500000 and c2 ='0312c21fa727e4063d404c6efec4ca48';
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using t_c1_idx on t  (cost=0.00..20296.53 rows=1 width=45)
   Index Cond: (c1 < 500000)
   Filter: ((c2)::text = '0312c21fa727e4063d404c6efec4ca48'::text)
(3 rows)
复制

开启set track_stmt_stat_level = 'L1,L1'; --打开FullSQL统计信息，查看dbe_perf.statement_history中t表消息。

openGauss=# select unique_query_id, query, query_plan from dbe_perf.statement_history where query like '% t %';
 unique_query_id |                  query                  |                              query_plan
-----------------+-----------------------------------------+-----------------------------------------------------------------------
       724701992 | select * from t where c1 < ? and c2 =?; | Datanode Name: ognode                                                +
                 |                                         | Index Scan using t_c1_idx on t  (cost=0.00..20296.53 rows=1 width=45)+
                 |                                         |   Index Cond: (c1 < '***')                                           +
                 |                                         |   Filter: ((c2)::text = '***'::text)                                 +
                 |                                         |                                                                      +
                 |                                         |
       724701992 | select * from t where c1 < ? and c2 =?; | Datanode Name: ognode                                                +
                 |                                         | Seq Scan on t  (cost=0.00..24346.00 rows=1 width=45)                 +
                 |                                         |   Filter: ((c1 < '***') AND ((c2)::text = '***'::text))              +
                 |                                         |                                                                      +
                 |                                         |
复制

二者的unique_query_id一样，可以通过unique_query_id hint索引扫描。

openGauss=# select * from dbe_sql_util.create_hint_sql_patch('p1',724701992,'indexscan(t)');
 create_hint_sql_patch
-----------------------
 t
(1 row)

openGauss=# explain select * from t where c1 < 500000 and c2 ='0312c21fa727e4063d404c6efec4ca48';
NOTICE:  Plan influenced by SQL hint patch
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using t_c1_idx on t  (cost=0.00..20296.53 rows=1 width=45)
   Index Cond: (c1 < 500000)
   Filter: ((c2)::text = '0312c21fa727e4063d404c6efec4ca48'::text)
(3 rows)

openGauss=# explain select * from t where c1 < 700000 and c2 ='0312c21fa727e4063d404c6efec4ca48';
NOTICE:  Plan influenced by SQL hint patch
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using t_c1_idx on t  (cost=0.00..28331.65 rows=1 width=45)
   Index Cond: (c1 < 700000)
   Filter: ((c2)::text = '0312c21fa727e4063d404c6efec4ca48'::text)
(3 rows)
复制

两个查询均走了索引扫描，可以减小很多IO的消耗。

问题2

另外一张t_lei表，SQL执行的时，有时选择了Bitmap Heap Scan，Bitmap Index Scan，模拟一下。

--创建测试t_lei表
create table t_lei as select oid,relname from pg_class;
create index idx_t_lei_1 on t_lei(oid);
--查看执行计划
openGauss=# explain analyze select * from t_lei where oid<100 and relname='dump';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_lei_1 on t_lei  (cost=0.00..7.67 rows=1 width=68) (actual time=0.005..0.005 rows=0 loops=1)
   Index Cond: (oid < 100::oid)
   Filter: (relname = 'dump'::name)
 Total runtime: 0.098 ms
(4 rows)

openGauss=# explain analyze select * from t_lei where oid<500 and relname='dump';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_lei  (cost=4.28..12.75 rows=1 width=68) (actual time=0.024..0.024 rows=0 loops=1)
   Recheck Cond: (oid < 500::oid)
   Filter: (relname = 'dump'::name)
   Rows Removed by Filter: 4
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on idx_t_lei_1  (cost=0.00..4.28 rows=4 width=0) (actual time=0.010..0.010 rows=4 loops=1)
         Index Cond: (oid < 500::oid)
 Total runtime: 0.138 ms
(8 rows)

openGauss=# select unique_query_id, query, query_plan from dbe_perf.statement_history where query like '%t_lei%';
 unique_query_id |                     query                      |                                query_plan
-----------------+------------------------------------------------+--------------------------------------------------------------------------
      3674902144 | select * from t_lei where oid<? and relname=?; | Datanode Name: ognode                                                   +
                 |                                                | Index Scan using idx_t_lei_1 on t_lei  (cost=0.00..7.67 rows=1 width=68)+
                 |                                                |   Index Cond: (oid < '***'::oid)                                        +
                 |                                                |   Filter: (relname = '***'::name)                                       +
                 |                                                |                                                                         +
                 |                                                |
      3674902144 | select * from t_lei where oid<? and relname=?; | Datanode Name: ognode                                                   +
                 |                                                | Bitmap Heap Scan on t_lei  (cost=4.28..12.75 rows=1 width=68)           +
                 |                                                |   Recheck Cond: (oid < '***'::oid)                                      +
                 |                                                |   Filter: (relname = '***'::name)                                       +
                 |                                                |   ->  Bitmap Index Scan on idx_t_lei_1  (cost=0.00..4.28 rows=4 width=0)+
                 |                                                |         Index Cond: (oid < '***'::oid)                                  +
                 |                                                |                                                                         +
                 |                                                |
(2 rows)
复制

用unique_query_id指定hint，让查询走索引扫描。

openGauss=# select * from dbe_sql_util.create_hint_sql_patch('p2', 3674902144, 'indexscan(t_lei)');
 create_hint_sql_patch
-----------------------
 t
(1 row)

openGauss=# explain analyze select * from t_lei where oid<100 and relname='dump';
NOTICE:  Plan influenced by SQL hint patch
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_lei_1 on t_lei  (cost=0.00..7.67 rows=1 width=68) (actual time=0.005..0.005 rows=0 loops=1)
   Index Cond: (oid < 100::oid)
   Filter: (relname = 'dump'::name)
 Total runtime: 0.134 ms
(4 rows)

openGauss=# explain analyze select * from t_lei where oid<500 and relname='dump';
NOTICE:  Plan influenced by SQL hint patch
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t_lei_1 on t_lei  (cost=0.00..18.52 rows=1 width=68) (actual time=0.018..0.018 rows=0 loops=1)
   Index Cond: (oid < 500::oid)
   Filter: (relname = 'dump'::name)
   Rows Removed by Filter: 4
 Total runtime: 0.145 ms
(5 rows)
复制

受到hint的影响，之前执行计划选择了Bitmap Heap Scan，Bitmap Index Scan的现在是索引扫描了。

结论

以上模拟出来的情况都是查询一致，解析器会根据筛选条件不一样选择不一样的执行计划。创建hint之后，受到hint的影响会强制执行计划为索引扫描。