前言
在主流的数据库产品中,都有优化器提示这个功能,也叫做Hint。Hint的作用就是可以帮助我们更好的控制执行计划。而在PostgreSQL中,我们可以安装pg_hint_plan
插件来使用提示调整执行计划,达到优化SQL的效果。
当前的pg_hint_plan
版本支持到了最新的PostgreSQL 13。我们可以在github上选择合适的版本进行下载.
![d5dd92c6bceb04f4831aa6c6796292cf.png](https://i-blog.csdnimg.cn/blog_migrate/b4f557ffeed3422100e5c88625ea007c.png)
下载完安装比较简单,就是直接make和make install,然后进到数据库设置shared_preload_libraries参数加载pg_hint_plan
。
postgres=# alter system set shared_preload_libraries=pg_stat_statements,pg_hint_plan;
加载完成后重启数据库,确认pg_hint_plan
已安装。
pg_hint_plan使用
pg_hint_plan使用非常简单,我们只要在SQL中加入提示注释,以字符/*+
开头,以字符*/
结束。提示由提示的名称和参数组成。下面再来看看之前的SQL语句。
这个SQL运行280秒,在Oracle中使用hash join。而在PostgreSQL中使用Merge join,导致需要sort,会非常的慢。
![16d570143c79626cb17094df8f4fc3fd.png](https://i-blog.csdnimg.cn/blog_migrate/83baa31cea1596a43fe2f36517b90cf7.png)
我们先使用参数enable_mergejoin=off看下执行计划。
![7954fd360b4b3ba64a7b6333ce9593a5.png](https://i-blog.csdnimg.cn/blog_migrate/9e1b1e57a46101c0b894ce48025e47bd.png)
如果关闭mergejoin,正常的操作顺序就是b表和a表做hash join,然后再和c表做hash join。
那么就可以写如下hint,直接不让使用mergeJoin,这么写就和直接禁用参数一致。
/*+
NoMergeJoin(a b)
NoMergeJoin(a b c)
*/
select a.docid, b.contactid, (b.callendtime - b.callstarttime) * 24 *60 * 60 timesum
from searchresultclickinfo a, KMS_DLG_CONTACTDETAIL b, kmsdocument c where
a.docid =to_char(c.docid) and a.userid =b.staffid
and a.crtime>to_date('2019-02-10', 'yyyy-mm-dd') and a.crtime <= to_date('2019-02-11', 'yyyy-mm-dd')
and a.crtime between b.callstarttime and b.callendtime;
![cf15041f923919679e2df68a487b28bd.png](https://i-blog.csdnimg.cn/blog_migrate/a34a9316d44f7d64fd11b53e5457edb7.png)
当然你也可以自己写join方式,这么写的话对于B表一个全分区扫描后,还要做一个hash操作。效率比上面的略低。
/*+
Leading (a b)
hashJoin(a b)
hashJoin(a b c)
*/
select a.docid, b.contactid, (b.callendtime - b.callstarttime) * 24 *60 * 60 timesum
from searchresultclickinfo a, KMS_DLG_CONTACTDETAIL b, kmsdocument c where
a.docid =to_char(c.docid) and a.userid =b.staffid
and a.crtime>to_date('2019-02-10', 'yyyy-mm-dd') and a.crtime <= to_date('2019-02-11', 'yyyy-mm-dd')
and a.crtime between b.callstarttime and b.callendtime;
![ab51fad9fc16a72404043476e2ad7aea.png](https://i-blog.csdnimg.cn/blog_migrate/1c1c7e3d6d172f2e7b1fd6d21f9d0174.png)
pg_hint_table提示中指定参数
pg_hint_table
还可以直接在hint里面禁用参数。比如刚刚的enable_mergejoin=off
,我们也可以直接设置在hint里面。
/*+ Set(enable_mergejoin off) */
select a.docid, b.contactid, (b.callendtime - b.callstarttime) * 24 *60 * 60 timesum
from searchresultclickinfo a, KMS_DLG_CONTACTDETAIL b, kmsdocument c where
a.docid =to_char(c.docid) and a.userid =b.staffid
and a.crtime>to_date('2019-02-10', 'yyyy-mm-dd') and a.crtime <= to_date('2019-02-11', 'yyyy-mm-dd')
and a.crtime between b.callstarttime and b.callendtime;
![4404e9b6f9c6bf76aa00a03d754636b4.png](https://i-blog.csdnimg.cn/blog_migrate/c97291beafdd400452fc85a49182946e.png)
这样就简单多了。
程序不配合改造可以使用提示表
现在我们可以使用hint来指定正确的执行计划。按照正常的逻辑,我们只要把改造后的SQL给到开发那边就行了,但是你要知道有一些开发是不愿意配合改造的,对于这点pg_hint_plan也可以做到不修改程序代码,直接给你固定执行计划。
这需要使用到hint_plan.hints
这个表,默认这个表有四个字段。
![a7a16cc258bf188072a40c9e5687d7e7.png](https://i-blog.csdnimg.cn/blog_migrate/ab955c5e04f30791c95d7cb560579478.png)
id
代表了唯一编号,这是一个自动增长的列。
norm_query_string
这代表了sql语句,如果有常量
的字符,可以使用?
代替。
application_name
代表了应用程序的名称。填写psql代表从命令行登入,如果填空则代表任意的应用程序。
hints
代表了hint,这里可以是hint的代码。
hint代码这里需要注意,不能填写包含的注释标记。
接下来我们来测试一下。将刚刚的语句插入,然后application_name
这里填空,hint这里写Set(enable_mergejoin off)
![f50fb83a2852e4b55d377ea598ebdfe6.png](https://i-blog.csdnimg.cn/blog_migrate/f5189cc37116bf883a482d1d991b52af.png)
登录psql,查看执行计划是否固定。结果研究了大半天,一点效果都没有。
后面发现居要打开一个开关才行。
![47cc076250a02d8278010d120578f6df.png](https://i-blog.csdnimg.cn/blog_migrate/233b04615166f9a1bc0b91b07f5c98fa.jpeg)
kms=# show pg_hint_plan.enable_hint_table;
pg_hint_plan.enable_hint_table
--------------------------------
off
(1 row)
kms=# set pg_hint_plan.enable_hint_table=on;
SET
Time: 0.287 ms
kms=# show pg_hint_plan.enable_hint_table;
pg_hint_plan.enable_hint_table
--------------------------------
on
(1 row)
Time: 0.252 ms
设置好了这个开关,在执行就好了。
![5ffd9e508b492dbdfc5ad91c093b654b.png](https://i-blog.csdnimg.cn/blog_migrate/9e85898f774315538ecf1fe824fdcfde.png)
如果有很多条带各种常量的SQL,想要绑定就直接把常量换成?就可以。
结尾
今天使用pg_hint_plan暂时就到这里了,更多高级的用法和玩法,可以参考官方文档及我列出的参考文献。
参考文献
pg_hint_plan项目页面 https://ja.osdn.net/projects/pghintplan/
pg_hint_plan 文档 http://pghintplan.osdn.jp/pg_hint_plan-ja.html
github https://github.com/ossc-db/pg_hint_plan