MogDB学习笔记之 - SQL Patch

MogDB 继承了openGauss 3.0引入等SQL Patch功能，并进行了一些增强。我们都知道在数据库优化过程中，尤其是之前在进行Oracle 数据库优化时，需要经常去干预SQL 执行计划，确保应用SQL 走正常的执行计划。因此去固定sql的执行计划就显得十分的重要，这方面Oracle做的非常强大。不过现在MogDB也具备相应的功能了，这简直是DBA的一大福音。下面进行简单的 测试验证：

+++Session 1

enmotech=> create table test_sql_patch as select * from pg_settings;
INSERT 0 773
enmotech=>
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 773
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 1546
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 3092
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 6184
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 12368
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 24736
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 49472
enmotech=> insert into test_sql_patch select * from test_sql_patch;
INSERT 0 98944
。。。。。。
enmotech=> create index idx_test_sql_patch on test_sql_patch(name,setting);
CREATE INDEX
enmotech=>
enmotech=> \timing on
Timing is on.
enmotech=> select count(1) from test_sql_patch;
count
--------
395776
(1 row)

Time: 156.625 ms
enmotech=> select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows';
count
-------
512
(1 row)

Time: 1.791 ms
enmotech=> explain performance select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=30.33..30.34 rows=1 width=8) (actual time=0.838..0.839 rows=1 loops=1)
Output: count(1)
(Buffers: shared hit=7)
(CPU: ex c/r=-20846719600616188, ex row=512, ex cyc=-10673520435515488256, inc cyc=41775031060283808)
-> Index Only Scan using idx_test_sql_patch on public.test_sql_patch (cost=0.00..29.07 rows=504 width=0) (actual time=0.084..0.602 rows=512 loops=1)
Output: name, setting
Index Cond: (test_sql_patch.name = 'adaptive_hashagg_min_rows'::text)
Heap Fetches: 0
(Buffers: shared hit=7)
(CPU: ex c/r=20928311458155804, ex row=512, ex cyc=10715295466575771648, inc cyc=10715295466575771648)
Total runtime: 1.093 ms
(11 rows)

Time: 4.316 ms
enmotech=>

+++Session 2

enmotech=# select unique_sql_id,query from dbe_perf.statement where query like '%test_sql_patch%'
unique_sql_id | query

---------------+----------------------------------------------------------------
4280879837 | select count(?) from test_sql_patch where name=?;


enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619', 4280879837, 'indexonlyscan(test_sql_patch)');
create_hint_sql_patch
-----------------------
t
(1 row)

++++Session 1

enmotech=> explain select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows' and setting='10000';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=4.34..4.35 rows=1 width=8)
-> Index Only Scan using idx_test_sql_patch on test_sql_patch (cost=0.00..4.33 rows=4 width=0)
Index Cond: ((name = 'adaptive_hashagg_min_rows'::text) AND (setting = '10000'::text))
(3 rows)

Time: 1.123 ms
enmotech=>

enmotech=# select * from dbe_sql_util.drop_sql_patch('patch0619');
drop_sql_patch
----------------
t
(1 row)

enmotech=> explain select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows';
NOTICE: Plan influenced by SQL hint patch
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Aggregate (cost=30.33..30.34 rows=1 width=8)
-> Index Only Scan using idx_test_sql_patch on test_sql_patch (cost=0.00..29.07 rows=504 width=0)
Index Cond: (name = 'adaptive_hashagg_min_rows'::text)
(3 rows)

Time: 1.111 ms

如果是indexonlyscan的情况下，能否改成indexscan呢，我们删除sql patch并重建一下：

enmotech=# select * from dbe_sql_util.drop_sql_patch('patch0619');
drop_sql_patch
----------------
t
(1 row)

enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619', 4280879837, 'indexscan(test_sql_patch)');
create_hint_sql_patch
-----------------------
t
(1 row)

enmotech=#

再次验证一下执行计划：

enmotech=> explain select count(1) from test_sql_patch where name='adaptive_hashagg_min_rows';
NOTICE: Plan influenced by SQL hint patch
WARNING: unused hint: IndexScan(test_sql_patch)
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Aggregate (cost=30.37..30.38 rows=1 width=8)
-> Index Only Scan using idx_test_sql_patch on test_sql_patch (cost=0.00..29.11 rows=506 width=0)
Index Cond: (name = 'adaptive_hashagg_min_rows'::text)
(3 rows)

Time: 2.394 ms
enmotech=>

我们可以看有warning提示，说明这种情况下并没起作用，仍然使用了indexonlyscan.

为了查询慢sql监控信息，这里我调整了如下的相关参数：
enable_resource_track=on
instr_unique_sql_count=10000
resource_track_level=query
log_duration=on
log_min_duration_statement=1

除了简单的 控制索引使用，能否支持控制连接方式呢？比如强制使用hash join？ 再测一下：

enmotech=> create table test0619 as select * from test_sql_patch;
INSERT 0 395776
Time: 2246.460 ms
enmotech=> explain select a.name,count(1) from test_sql_patch a,test0619 b
enmotech-> where a.name=b.name
enmotech-> and b.setting=500000
enmotech-> group by a.name
enmotech-> order by 2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=32121.62..32123.55 rows=773 width=28)
Sort Key: (count(1))
-> HashAggregate (cost=32076.81..32084.54 rows=773 width=28)
Group By Key: a.name
-> Nested Loop (cost=0.00..30197.77 rows=375808 width=20)
-> Seq Scan on test0619 b (cost=0.00..11440.03 rows=734 width=32)
Filter: ((setting)::bigint = 500000)
-> Index Only Scan using idx_test_sql_patch on test_sql_patch a (cost=0.00..20.44 rows=512 width=20)
Index Cond: (name = b.name)
(9 rows)

Time: 2.791 ms
enmotech=>

enmotech=> select a.name,count(1) from test_sql_patch a,test0619 b
enmotech-> where a.name=b.name
enmotech-> and b.setting=500000
enmotech-> group by a.name
enmotech-> order by 2;
ERROR: invalid input syntax for type bigint: "off"
Time: 3.981 ms
enmotech=> select a.name,count(1) from test_sql_patch a,test0619 b
enmotech-> where a.name=b.name
enmotech-> and b.setting='500000'
enmotech-> group by a.name
enmotech-> order by 2;
name | count
------------------------------+--------
acce_min_datasize_per_thread | 262144
(1 row)

Time: 400.892 ms
enmotech=>


enmotech=# select unique_sql_id,query from dbe_perf.statement where query like '%test0619%';
unique_sql_id | query
---------------+--------------------------------------------------------
1699566349 | create table test0619 as select * from test_sql_patch;
(1 row)

enmotech=# select unique_sql_id,query from dbe_perf.statement where query like '%test0619%';
unique_sql_id | query
---------------+---------------------------------------------------------
1699566349 | create table test0619 as select * from test_sql_patch;
3134227090 | select a.name,count(?) from test_sql_patch a,test0619 b+
| where a.name=b.name +
| and b.setting=? +
| group by a.name +
| order by 2;
3627100211 | select a.name,count(?) from test_sql_patch a,test0619 b+
| where a.name=b.name +
| and b.setting=? +
| group by a.name +
| order by 2;
(3 rows)


enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619_2', 3627100211, 'hashjoin(test0619 test_sql_patch)');
create_hint_sql_patch
-----------------------
t
(1 row)

enmotech=> explain select a.name,count(1) from test_sql_patch a,test0619 b
enmotech-> where a.name=b.name
enmotech-> and b.setting='500000'
enmotech-> group by a.name
enmotech-> order by 2;
NOTICE: Plan influenced by SQL hint patch
WARNING: Error hint: HashJoin(test0619 test_sql_patch), relation name "test0619" is not found.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=34548.58..34550.51 rows=773 width=28)
Sort Key: (count(1))
-> HashAggregate (cost=34503.77..34511.50 rows=773 width=28)
Group By Key: a.name
-> Nested Loop (cost=0.00..32660.01 rows=368752 width=20)
-> Seq Scan on test0619 b (cost=0.00..14185.20 rows=721 width=20)
Filter: (setting = '500000'::text)
-> Index Only Scan using idx_test_sql_patch on test_sql_patch a (cost=0.00..20.50 rows=512 width=20)
Index Cond: (name = b.name)
(9 rows)

Time: 2.088 ms
enmotech=>

从测试来看似乎没有识别到我们的hint。可能是写法不对，改成别名试试看。

enmotech=# select * from dbe_sql_util.drop_sql_patch('patch0619_2');
drop_sql_patch
----------------
t
(1 row)

enmotech=# select * from dbe_sql_util.create_hint_sql_patch('patch0619_1',3627100211, 'hashjoin(a b)');
create_hint_sql_patch
-----------------------
t
(1 row)

enmotech=#

enmotech=> explain select a.name,count(1) from test_sql_patch a,test0619 b
enmotech-> where a.name=b.name
enmotech-> and b.setting='500000'
enmotech-> group by a.name
enmotech-> order by 2;
NOTICE: Plan influenced by SQL hint patch
QUERY PLAN
--------------------------------------------------------------------------------------------
Sort (cost=36665.86..36667.79 rows=773 width=28)
Sort Key: (count(1))
-> HashAggregate (cost=36621.05..36628.78 rows=773 width=28)
Group By Key: a.name
-> Hash Join (cost=14194.21..34777.29 rows=368752 width=20)
Hash Cond: (a.name = b.name)
-> Seq Scan on test_sql_patch a (cost=0.00..13195.76 rows=395776 width=20)
-> Hash (cost=14185.20..14185.20 rows=721 width=20)
-> Seq Scan on test0619 b (cost=0.00..14185.20 rows=721 width=20)
Filter: (setting = '500000'::text)
(10 rows)

Time: 1.957 ms
enmotech=>

此时正是我们需要的执行计划，强制其走hash join。

从官方文档来看，目前MogDB SQL Patch支持多种操作；例如 支持行数、扫描方式、连接方式、连接顺序、PBE custom/generic计划选择、语句级参数等。
基本上跟Oracle SQL patch接近了。