mul ab 的执行结果是_唯我所控，PostgreSQL执行计划控制工具PG_HINT

本文链接：https://blog.csdn.net/weixin_39723441/article/details/111662947

前言

在主流的数据库产品中，都有优化器提示这个功能，也叫做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

设置好了这个开关，在执行就好了。