mul ab 的执行结果是_唯我所控，PostgreSQL执行计划控制工具PG_HINT_PLAN
前言在主流的数据库产品中，都有优化器提示这个功能，也叫做Hint。Hint的作用就是可以帮助我们更好的控制执行计划。而在PostgreSQL中，我们可以安装pg_hint_plan插件来使用提示调整执行计划，达到优化SQL的效果。当前的pg_hint_plan版本支持到了最新的PostgreSQL 13。我们可以在github上选择合适的版本进行下载.下载完安装比较简单，就是直接make和make
前言
在主流的数据库产品中，都有优化器提示这个功能，也叫做Hint。Hint的作用就是可以帮助我们更好的控制执行计划。而在PostgreSQL中，我们可以安装pg_hint_plan插件来使用提示调整执行计划，达到优化SQL的效果。
当前的pg_hint_plan版本支持到了最新的PostgreSQL 13。我们可以在github上选择合适的版本进行下载.
下载完安装比较简单，就是直接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，会非常的慢。
我们先使用参数enable_mergejoin=off看下执行计划。
如果关闭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;
当然你也可以自己写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;
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;
这样就简单多了。
程序不配合改造可以使用提示表
现在我们可以使用hint来指定正确的执行计划。按照正常的逻辑，我们只要把改造后的SQL给到开发那边就行了，但是你要知道有一些开发是不愿意配合改造的，对于这点pg_hint_plan也可以做到不修改程序代码，直接给你固定执行计划。
这需要使用到hint_plan.hints这个表，默认这个表有四个字段。
id代表了唯一编号，这是一个自动增长的列。
norm_query_string这代表了sql语句，如果有常量的字符，可以使用?代替。
application_name 代表了应用程序的名称。填写psql代表从命令行登入，如果填空则代表任意的应用程序。
hints代表了hint，这里可以是hint的代码。
hint代码这里需要注意，不能填写包含的注释标记。
接下来我们来测试一下。将刚刚的语句插入，然后application_name 这里填空，hint这里写Set(enable_mergejoin off)
登录psql，查看执行计划是否固定。结果研究了大半天，一点效果都没有。
后面发现居是要打开一个开关才行。
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
设置好了这个开关，在执行就好了。
如果有很多条带各种常量的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
瓜分20万奖金 获得内推名额 丰厚实物奖励 易参与易上手
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)