- 概念描述
- 测试验证
- 知识总结
- 参考文档
概念描述
测试并验证MogDB 5.0 SQL Patch功能的实用性。
测试验证
+++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=#
重建SQL Patch之后，我们再次验证一下执行计划是否符合我们的预期：
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
接下来我们进一步进行相关测试和验证：
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。由此可以见，可以很方便的控制表的join方式。实际上除了hash，还可以强制走nest loop或者sort merge join等。
知识总结
从官方文档来看，目前MogDB SQL Patch支持多种操作；例如 支持行数、扫描方式、连接方式、连接顺序、PBE custom/generic计划选择、语句级参数等。
基本上跟Oracle SQL patch接近了。
最后修改时间：2023-09-28 09:42:51
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
