PgSQL · 特性分析 · Plan Hint · 数据库内核月报

## 背景有一个功能，是社区官方版”永远”不考虑引入的(参见[PG TODO](https://wiki.postgresql.org/wiki/Todo)，查找”Oracle-style”)，即类似Oracle的Plan Hint。社区开发者的理念是，引入Hint功能，会掩盖优化器本身的问题，导致缺陷不被暴露出来。但对于我们的使用者来讲，遇到某些SQL的查询计划不好，性能出了问题，使用了其他方法又不奏效的情况下，首先的目标还是想尽快解决问题，而Hint就可以在这种时候帮助到我们。可喜的是，通过集成“民间”的 pg_hint_plan 插件（[文档](http://pghintplan.osdn.jp/pg_hint_plan.html)），RDS for PG已经支持了Hint功能（RDS for PPAS也是支持的）。现在我们来研究一下这个插件如何使用，又是如何做到改变优化器优化过程，让优化器听我们“指挥”的。 ## 使用 Plan Hint在RDS for PG里面缺省是没有打开的，可以LOAD命令启用： ~~~ postgres=# LOAD 'pg_hint_plan'; LOAD ~~~ 但注意这只在会话级别有效，重新连接后将失效。如果想要每次连接都自动启用Hint，可以使用下面的命令（注意必须以RDS的根用户执行，否则会遇到权限错误）。这样下次连接时，Hint就默认启用了。 ~~~ postgres=> alter role all set session_preload_libraries = 'pg_hint_plan'; ALTER ROLE ~~~ 为了便于说明，我们使用下面的shell脚本来创建2张表： ~~~ for i in `seq 2` ; do psql -c "drop table t${i}" psql -c "create table t${i}(a int, b int);" psql -c "insert into t${i} select generate_series(1,1000), random() *1000+1" psql -c "create index t${i}_i_a on t${i}(a)" psql -c "create index t${i}_i_b on t${i}(b)" done ~~~ 然后在t1上进行查询，不使用和使用Hint的查询计划分别如下： ~~~ postgres=> explain select * from t1 where a = 1; QUERY PLAN ----------------------------------------------------------------- Index Scan using t1_i_a on t1 (cost=0.28..8.29 rows=1 width=8) Index Cond: (a = 1) (2 rows) postgres=> /*+ SeqScan(t1) */ explain select * from t1 where a = 1; QUERY PLAN --------------------------------------------------- Seq Scan on t1 (cost=0.00..17.50 rows=1 width=8) Filter: (a = 1) (2 rows) ~~~ 可以看出，利用Hint后，我们成功强制使用了表扫描。Hint一般以SQL注释的形式，出现在SQL的前面，并以/+开头，以/结尾。注意/*和+之间不能有空格。 ## Hint的种类 pg_hint_plan插件支持的Hint有很多种，分成如下几类(具体参见[pg_hint_plan文档](http://pghintplan.osdn.jp/hint_list.html)）： ~~~ 扫描类（Scan Method），指定表的访问路径，举例如下： --顺序扫描，参数为表名，也可以带模式名 SeqScan(t1) --索引扫描，参数为表名和索引名，注意两者之间是空格，没有逗号 IndexScan(t1 t1_i_a) --TID扫描 TidScan(t1) --禁止顺序扫描 NoSeqScan(t1) --禁止索引扫描 NoIndexScan(t1) 连接类（Join Method），指定表连接的方法，举例如下： NestLoop(t1 t2) MergeJoin(t1 t2) HashJoin(t1 t2) 连接顺序类（Join Order），指定连接的顺序，举例如下： --使t3和t1先连接，最后和t2连接 Leading(t2 (t3 t1)) SET类，即改变任意的GUC变量，举例如下： --改变random_page_cost Set(random_page_cost 3.0) ROW类型，改变表的连接结果集的估计大小，举例如下: --将t1和t2的连接结果的估计大小扩大10倍 Rows(t1 t2 *10) ~~~ ## 内核实现看完了形形色色的Hint，我们会想，这些Hint是怎么改变复杂的优化器逻辑，使其生成我们需要的查询计划的呢？我们从其源码看起（源码可以从[这里](http://iij.dl.sourceforge.jp/pghintplan/)下载）。插件主要的代码集中在pg_hint_plan.c里面。从其中`PG_init`函数的代码可以看出，它利用了`planner_hook`（优化器的函数钩子，实际上是全局变量，存放函数地址，可以被插件更改，换成插件自定义函数的地址），用`pg_hint_plan_planner`取代了原来的优化器逻辑。这样PG在处理一个SQL时，将调用`pg_hint_plan_planner`来做优化。而`pg_hint_plan_planner`会调用`get_hints_from_comment`，来读取Hint，并调用`create_hintstate`进行语法分析。这里要说明的是，`create_hintstate`遇到一张表上的多个同类型Hint（包括重复的Hint），只保留最后一个，前面的会忽略。另外，还有两个函数钩子被利用：`get_relation_info_hook` 和 `join_search_hook`。这两个钩子分别被修改指向了`pg_hint_plan_get_relation_info`和`pg_hint_plan_join_search`。前者是在优化器处理基本表（非视图、非函数的表）获取表信息时被调用，调用栈如下： ~~~ query_planner -> add_base_rels_to_query -> build_simple_rel -> get_relation_info -> get_relation_info_hook(即pg_hint_plan_get_relation_info) ~~~ 这个`pg_hint_plan_get_relation_info`做了什么呢？仔细看会惊讶的发现，它是用来删除索引的！对，它在优化器获取表的基本信息后被调用，然后其从基本信息删除了那些在Hint中未使用的索引。例如，t1上有两个索引t1_i_a和t1_i_b，如果指定了IndexScan(t1 t_i_b)这个Hint，那么t1_i_a的索引信息在这里被删除，这样在后续的优化中，就永远不会考虑t1_i_a这个索引了！再看`pg_hint_plan_join_search`，其被调用的位置如下： ~~~ query_planner -> make_one_rel -> make_rel_from_joinlist ->join_search_hook(即pg_hint_plan_join_search) ~~~ 可见，它是在为一个SQL语句生成连接结果时被调用，其输入为待连接的表，输出为连接后生成的表及其最优的查询计划。它主要做了两件事： 1. 调用`rebuild_scan_path`重新生成基本表的访问路径。为什么要重新生成呢？因为在基本表的访问计划生成阶段，扫描类的Hint并未实际起作用（只是对索引做过删除处理）。例如，即使指定了IndexScan(t1 t1_i_a)，但外部的GUC变量`enable_indexscan`被设置为了off，在这里也只会看到一个表扫描（SeqScan）的查询计划。因此这里需要重新设置好GUC变量（例如如果遇到IndexScan Hint，需要把GUC变量enable_indexscan重置为on），再做一遍访问计划。由于基本表一般数量较少，访问计划也只需再生成一次，所以此步开销是可接受的； 2. 调用`pg_hint_plan_standard_join_search`生成连接的计划。这里是应用连接方法和连接顺序Hint的地方。要想改变连接方法或顺序，需要进一步修改优化器的整个逻辑，但优化器没那么多的预定义钩子可用了，采用函数钩子的方法不可行。于是，插件便“自备”了优化器的主流程代码（其实是从同版本的PG里面拷贝出来的），见插件代码中的core.c和make_join_rel.c两个文件。里面很多地方是被插件修改过的。其中核心的是修改对`add_paths_to_joinrel`的调用，使优化器实际调用`add_paths_to_joinrel_wrapper`。这个函数是用于为输入的两张表（可能是连接生成的中间表），生成一个连接计划。可以看到`add_paths_to_joinrel_wrapper`会先去查找有没有对应的Hint，如果有就直接利用，并舍弃掉不符合Hint的连接方法和顺序（这是连接顺序Hint其作用的地方）。可以看到，此插件的实现并不复杂，它巧妙利用了优化器优化流程中的关键点，来应用Hint，达到固定查询计划的目的。 ## 性能测试从内核实现可以看出，指定Hint后会带来如下开销：基本表的访问路径要生成两次；每次连接两个中间表时，要检查是否有对应的Hint；很多地方需要反复更新GUC变量来影响计划生成。当然，由于直接指定了表的连接方法、顺序等，减少了生成的中间计划，这一点又节省了很多开销。所以，对使用Hint后的编译时间是否比原来长，不能一概而论。下面我们对Hint造成的编译开销做一下粗略测试。测试用例如下： ~~~ \timing DO $$DECLARE count int; BEGIN count := 1; LOOP count := count + 1; begin EXECUTE 'explain select * from t1,t2,t3,t4,t5,t6,t7,t8 where t1.a=t2.b and t2.a=t3.b and t3.a=t4.b and t4.a=t5.b and t5.a=t6.b and t6.a=t7.b and t7.a=t8.b'; IF count > 10000 THEN EXIT; END IF; exception when others then end; END LOOP; END$$; DO $$DECLARE count int; BEGIN count := 1; LOOP count := count + 1; begin EXECUTE '/*+ IndexScan(t1 t1_i_a) IndexScan(t2 t2_i_a) IndexScan(t3 t3_i_a) IndexScan(t4 t4_i_a) IndexScan(t5 t5_i_a) IndexScan(t6 t6_i_a) IndexScan(t7 t7_i_a) IndexScan(t8 t8_i_a) Leading ( t1 t2 t3 t4 t5 t6 t7 t8 ) */ explain select * from t1,t2,t3,t4,t5,t6,t7,t8 where t1.a=t2.b and t2.a=t3.b and t3.a=t4.b and t4.a=t5.b and t5.a=t6.b and t6.a=t7.b and t7.a=t8.b' ; IF count > 10000 THEN EXIT; END IF; exception when others then end; END LOOP; END$$; ~~~ 这里我们使用了8张表，每张表都只有a、b两个int字段。用两个DO语句，每个都执行同一SQL语句10000次。一个DO语句是不带Hint的，另一个带了较复杂的Hint。测试结果，不带Hint的执行耗时17秒左右，带Hint的14秒左右。即带Hint的反而编译时间更短（注意这里只执行了explain，为真正执行SQL语句）。