AutoHint：智能HINT推荐

概述

AutoHint（智能HINT推荐）将自动识别慢SQL执行计划树中耗时较长的算子，生成候选HINT进行替换，并通过贪心策略进行探索，最终生成能够提升当前慢SQL执行性能的HINT组合推荐给用户。在提升慢SQL性能的同时可以为用户节约人工调优所带来的人力与时间成本。

容灾集群下不支持智能HINT推荐。

前置条件

数据库运行正常。
确保当前用户拥有super权限。
若反馈基数估计与自适应代价估计功能开启，需要提前运行多次查询保证反馈基数估计模型与代价估计模型收敛。

使用指导

参考AutoHint语法运行相关查询即可得到HINT推荐结果。

最佳实践

反馈基数估计功能与自适应代价关闭场景（设置enable_adaptive_cost=off，adaptive_cardest_strategy=use_statistics）：

启动数据库，导入数据。

执行慢SQL语句，如含有多种连接路径和连接类型的查询SQL，观察默认场景下计划的执行时间与执行树。以JOB数据集的query11d语句为例。

gaussdb=# EXPLAIN ANALYZE SELECT MIN(cn.name) AS from_company,
       MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_based_on_book
FROM company_name AS cn,
     company_type AS ct,
     keyword AS k,
     link_type AS lt,
     movie_companies AS mc,
     movie_keyword AS mk,
     movie_link AS ml,
     title AS t
WHERE cn.country_code !='[pl]'
  AND ct.kind != 'production companies'
  AND ct.kind IS NOT NULL
  AND k.keyword IN ('sequel',
                    'revenge',
                    'based-on-novel')
  AND mc.note IS NOT NULL
  AND t.production_year > 1965
  AND lt.id = ml.link_type_id
  AND ml.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_type_id = ct.id
  AND mc.company_id = cn.id
  AND ml.movie_id = mk.movie_id
  AND ml.movie_id = mc.movie_id
  AND mk.movie_id = mc.movie_id;

使用AutoHint相关命令进行Hint推荐。

gaussdb=# AUTOHINT ANALYZE 
SELECT MIN(cn.name) AS from_company,
       MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_based_on_book
FROM company_name AS cn,
     company_type AS ct,
     keyword AS k,
     link_type AS lt,
     movie_companies AS mc,
     movie_keyword AS mk,
     movie_link AS ml,
     title AS t
WHERE cn.country_code !='[pl]'
  AND ct.kind != 'production companies'
  AND ct.kind IS NOT NULL
  AND k.keyword IN ('sequel',
                    'revenge',
                    'based-on-novel')
  AND mc.note IS NOT NULL
  AND t.production_year > 1965
  AND lt.id = ml.link_type_id
  AND ml.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_type_id = ct.id
  AND mc.company_id = cn.id
  AND ml.movie_id = mk.movie_id
  AND ml.movie_id = mc.movie_id
  AND mk.movie_id = mc.movie_id;

得到推荐HINT与预期提升效果。

                  AUTOHINT
---------------------------------------------
 Recommended hintset:
   MergeJoin(@sel$1 k@sel$1 mk@sel$1 mc@sel$1)

 Expected boost:
   planning:     -7%
   execution:    62%
   overall:      60%

使用推荐的HINT，观察执行计划是否改变，以及执行性能是否得到提升。

gaussdb=# EXPLAIN ANALYZE SELECT /*+MergeJoin(@sel$1 k@sel$1 mk@sel$1 mc@sel$1)*/ MIN(cn.name) AS from_company,
       MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_based_on_book
FROM company_name AS cn,
     company_type AS ct,
     keyword AS k,
     link_type AS lt,
     movie_companies AS mc,
     movie_keyword AS mk,
     movie_link AS ml,
     title AS t
WHERE cn.country_code !='[pl]'
  AND ct.kind != 'production companies'
  AND ct.kind IS NOT NULL
  AND k.keyword IN ('sequel',
                    'revenge',
                    'based-on-novel')
  AND mc.note IS NOT NULL
  AND t.production_year > 1965
  AND lt.id = ml.link_type_id
  AND ml.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_type_id = ct.id
  AND mc.company_id = cn.id
  AND ml.movie_id = mk.movie_id
  AND ml.movie_id = mc.movie_id
  AND mk.movie_id = mc.movie_id;

反馈基数估计功能与自适应代价估计功能开启场景：

导入数据后，执行慢SQL多次，直到观察到SQL执行计划稳定后，说明反馈基数估计模型与代价估计模型已收敛，后续操作与上述步骤一致。