PostgreSQL , sql plan outline , 执行计划篡改 , query rewrite , sr_plan , pg plan hint
功能较为强大的数据库，通常都有query rewrite的功能，比如JOIN时提升或下推条件，调整JOIN顺序等。
例如
create table a(id int, info text); create table b(id int, info text); create index idx_a_info on a (info); create index idx_b_id on b(id);执行以下查询，我们看看query rewrite如何工作的
postgres=# explain select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.id=1; QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop Left Join (cost=13.24..180340.52 rows=934 width=27) Join Filter: (a.id = b.id) -> Seq Scan on a (cost=0.00..179054.03 rows=1 width=15) Filter: (id = 1) -> GroupAggregate (cost=13.24..1265.48 rows=934 width=12) Group Key: b.id -> Bitmap Heap Scan on b (cost=13.24..1251.24 rows=981 width=9) Recheck Cond: (id = 1) -> Bitmap Index Scan on idx_b_id (cost=0.00..12.99 rows=981 width=0) Index Cond: (id = 1) (10 rows)这里只提供了a.id=1的条件，这个查询条件被重写，推入子查询中，所以我们看到子查询实际上也过滤了b.id=1的条件。
通过以上例子，我们见识到了QUERY REWRITE的用途，那么是不是所有场景都能rewrite 呢？
例如我们把以上QUERY换一个条件，改写为如下
postgres=# explain select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; QUERY PLAN --------------------------------------------------------------------------------- Hash Right Join (cost=204060.69..204298.22 rows=1 width=27) Hash Cond: (b.id = a.id) -> HashAggregate (cost=204057.62..204157.64 rows=10001 width=12) Group Key: b.id -> Seq Scan on b (cost=0.00..154056.75 rows=10000175 width=9) -> Hash (cost=3.05..3.05 rows=1 width=15) -> Index Scan using idx_a_info on a (cost=0.43..3.05 rows=1 width=15) Index Cond: (info = 'test1'::text) (8 rows)从以上执行计划，我们看到这个query rewrite并没有将a.info='test1'间接的推入子查询。
而实际上，PostgreSQL只是根据成本选择了一个执行计划，并不是说它不能推入a.info='test1'的条件，请继续看我在后面sr_plan中的测试，会看到PostgreSQL的CBO还是非常强大的。
另一方面，作为用户，以上QUERY可以改写为如下（或者说这是你期待的query rewrite对吧）
select * from a left join (select id,count(info) from b where exists (select 1 from a where a.id=b.id and a.info='test') -- 改写QUERY，得到同样结果，但是B的聚合量减少了 -- 或者 where id in (select id from a where a.info='test1') -- 或者 还有其他改法，拆掉子查询 group by id) b on (a.id=b.id) where a.info='test1';改写后的执行计划如下，b在聚合前，可以使用a的条件过滤掉一些记录，从而减少聚合的量
QUERY PLAN ------------------------------------------------------------------------------------------------ Hash Right Join (cost=1295.06..1318.82 rows=1 width=27) Hash Cond: (b.id = a.id) -> HashAggregate (cost=1292.00..1302.00 rows=1000 width=12) Group Key: b.id -> Nested Loop (cost=16.44..1287.00 rows=1000 width=9) -> HashAggregate (cost=3.05..3.06 rows=1 width=4) Group Key: a_1.id -> Index Scan using idx_a_info on a a_1 (cost=0.43..3.05 rows=1 width=4) Index Cond: (info = 'test1'::text) -> Bitmap Heap Scan on b (cost=13.38..1273.93 rows=1000 width=9) Recheck Cond: (id = a_1.id) -> Bitmap Index Scan on idx_b_id (cost=0.00..13.13 rows=1000 width=0) Index Cond: (id = a_1.id) -> Hash (cost=3.05..3.05 rows=1 width=15) -> Index Scan using idx_a_info on a (cost=0.43..3.05 rows=1 width=15) Index Cond: (info = 'test1'::text) (16 rows)query rewrite是一个比较智能的工作，在某些情况下，可以起到很好的性能优化作用，query rewrite也是许多数据库产品比拼的技术之一。
PostgreSQL这方面还是非常有优势的，请看我在SR_PLAN中的例子，加油。
其实除了query rewrite，PostgreSQL的社区还提供了一个非常强大的插件，sr_plan。
类似于Oracle的sql outline。
sr_plan插件，可以保存QUERY的执行计划，（支持绑定变量的QUERY），同时允许篡改执行计划，让篡改的执行计划生效。
针对每一条保存的执行计划，允许单独开启或关闭。
sr_plan实际上利用了PostgreSQL的钩子，通过post_parse_analyze_hook获取parser后的text并保存到sr_plan的query字段中，通过planner_hook保存、处理、返回保存的执行计划。
了解sr_plan的工作原理，我们来试用一下，看看以上query如何使用sr_plan来重写。
安装依赖 - python 3.2+
wget https://www.python.org/ftp/python/3.4.6/Python-3.4.6.tar.xz tar -xvf Python-3.4.6.tar.xz cd Python-3.4.6/ ./configure --prefix=/home/digoal/python3.4 --enable-shared make -j 128 make install export PS1="$USER@`/bin/hostname -s`-> " export PGPORT=1921 export PGDATA=/disk1/digoal/pgdata/pg_root1921 export LANG=en_US.utf8 export PGHOME=/home/digoal/pgsql9.6 export LD_LIBRARY_PATH=/home/digoal/python3.4/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export LD_RUN_PATH=$LD_LIBRARY_PATH export DATE=`date +"%Y%m%d%H%M"` export PATH=/home/digoal/python3.4/bin:$PGHOME/bin:$PATH:. export MANPATH=$PGHOME/share/man:$MANPATH export PGHOST=127.0.0.1 export PGUSER=postgres export PGDATABASE=postgres alias rm='rm -i' alias ll='ls -lh' unalias vi ln -s /home/digoal/python3.4/bin/python3.4 /home/digoal/python3.4/bin/python安装依赖Mako
wget https://pypi.python.org/packages/56/4b/cb75836863a6382199aefb3d3809937e21fa4cb0db15a4f4ba0ecc2e7e8e/Mako-1.0.6.tar.gz tar -zxvf Mako-1.0.6.tar.gz cd Mako-1.0.6/ python setup.py install安装依赖pycparser
wget https://pypi.python.org/packages/be/64/1bb257ffb17d01f4a38d7ce686809a736837ad4371bcc5c42ba7a715c3ac/pycparser-2.17.tar.gz tar -zxvf pycparser-2.17.tar.gz cd pycparser-2.17 python setup.py install安装sr_plan
git clone https://github.com/postgrespro/sr_plan cd sr_plan USE_PGXS=1 make distclean USE_PGXS=1 make genparser USE_PGXS=1 make USE_PGXS=1 make install修改PostgreSQL配置，让数据库启动是加载钩子
cd $PGDATA vi postgresql.conf shared_preload_libraries = 'sr_plan.so' pg_ctl stop -m fast pg_ctl start1. 在需要使用sr_plan的数据库中创建extension, 他会创建保留执行计划的表
psql postgres=# create extension sr_plan; postgres=# \d sr_plans Table "public.sr_plans" Column | Type | Modifiers ------------+-------------------+----------- query_hash | integer | not null plan_hash | integer | not null query | character varying | not null plan | jsonb | not null enable | boolean | not null valid | boolean | not null Indexes: "sr_plans_query_hash_idx" btree (query_hash)2. 创建测试表，分别插入1000万记录
create table a(id int, info text); create table b(id int, info text); insert into a select generate_series(1,10000000), 'test'||generate_series(1,10000000); -- 插入1000万数据 insert into b select * from a; -- 插入1000万数据 create index idx_a_info on a (info); create index idx_b_id on b(id);3. 开启sr_plan.write_mode, 允许sr_plan收集SQL和执行计划
postgres=# set sr_plan.write_mode = true;4. 查看QUERY 1的执行计划
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Merge Right Join (cost=2.90..506476.60 rows=1 width=27) (actual time=0.117..0.120 rows=1 loops=1) Output: a.id, a.info, b.id, (count(b.info)) Merge Cond: (b.id = a.id) Buffers: shared hit=2 read=6 -> GroupAggregate (cost=0.43..381475.09 rows=9999922 width=12) (actual time=0.060..0.063 rows=2 loops=1) Output: b.id, count(b.info) Group Key: b.id Buffers: shared hit=1 read=3 -> Index Scan using idx_b_id on public.b (cost=0.43..231476.26 rows=9999922 width=15) (actual time=0.051..0.053 rows=3 loops=1) Output: b.id, b.info Buffers: shared hit=1 read=3 -> Sort (cost=2.46..2.47 rows=1 width=15) (actual time=0.052..0.052 rows=1 loops=1) Output: a.id, a.info Sort Key: a.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 read=3 -> Index Scan using idx_a_info on public.a (cost=0.43..2.45 rows=1 width=15) (actual time=0.042..0.042 rows=1 loops=1) Output: a.id, a.info Index Cond: (a.info = 'test1'::text) Buffers: shared hit=1 read=3 Planning time: 0.819 ms Execution time: 0.200 ms (22 rows)PostgreSQL支持merge join、GroupAggregate(通过INDEX SCAN)，所以这个CASE，非常快，并不需要b对所有数据进行聚合。
但是为了演示需求，我们还是继续往下，看看人为rewrite的SQL
5. 查看QUERY 2的执行计划
explain (analyze,verbose,timing,costs,buffers) select * from a left join (select id,count(info) from b where exists (select 1 from a where a.id=b.id and a.info='test1') -- 改写QUERY，得到同样结果，但是B的聚合量减少了 -- 或者 where id in (select id from a where a.info='test1') -- 或者 还有其他改法，拆掉子查询 group by id) b on (a.id=b.id) where a.info='test1'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=5.37..7.43 rows=1 width=27) (actual time=0.036..0.037 rows=1 loops=1) Output: a.id, a.info, b.id, (count(b.info)) Join Filter: (a.id = b.id) Buffers: shared hit=7 -> Index Scan using idx_a_info on public.a (cost=0.43..2.45 rows=1 width=15) (actual time=0.017..0.018 rows=1 loops=1) Output: a.id, a.info Index Cond: (a.info = 'test1'::text) Buffers: shared hit=4 -> GroupAggregate (cost=4.94..4.96 rows=1 width=12) (actual time=0.015..0.015 rows=0 loops=1) Output: b.id, count(b.info) Group Key: b.id Buffers: shared hit=3 -> Sort (cost=4.94..4.94 rows=1 width=15) (actual time=0.013..0.013 rows=0 loops=1) Output: b.id, b.info Sort Key: b.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=3 -> Nested Loop (cost=2.89..4.93 rows=1 width=15) (actual time=0.005..0.005 rows=0 loops=1) Output: b.id, b.info Buffers: shared hit=3 -> HashAggregate (cost=2.46..2.46 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1) Output: a_1.id Group Key: a_1.id Buffers: shared hit=3 -> Index Scan using idx_a_info on public.a a_1 (cost=0.43..2.45 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1) Output: a_1.id, a_1.info Index Cond: (a_1.info = 'test'::text) Buffers: shared hit=3 -> Index Scan using idx_b_id on public.b (cost=0.43..2.45 rows=1 width=15) (never executed) Output: b.id, b.info Index Cond: (b.id = a_1.id) Planning time: 0.915 ms Execution time: 0.128 ms (33 rows)6. 执行以下QUERY后，QUERY的执行计划被保存到sr_plans中
postgres=# select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; id | info | id | count ----+-------+----+------- 1 | test1 | 1 | 1 (1 row) postgres=# select * from a left join (select id,count(info) from b where exists (select 1 from a where a.id=b.id and a.info='test1') -- 改写QUERY，得到同样结果，但是B的聚合量减少了 -- 或者 where id in (select id from a where a.info='test1') -- 或者 还有其他改法，拆掉子查询 group by id) b on (a.id=b.id) where a.info='test1'; id | info | id | count ----+-------+----+------- 1 | test1 | 1 | 1 (1 row)7. 禁止sr_plan收集SQL与执行计划
postgres=# set sr_plan.write_mode = false;8. 查看保存的执行计划
postgres=# select query_hash,query,explain_jsonb_plan(plan) from sr_plans ; -[ RECORD 1 ]------+------------------------------------------------------------------------------------------------------------ query_hash | 1668453880 query | select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; explain_jsonb_plan | Merge Right Join + | Merge Cond: (b.id = a.id) + | -> GroupAggregate + | Group Key: b.id + | -> Index Scan using idx_b_id on b + | -> Sort + | Sort Key: a.id + | -> Index Scan using idx_a_info on a + | Index Cond: (info = 'test1'::text) + -[ RECORD 3 ]------+------------------------------------------------------------------------------------------------------------ query_hash | 1956817209 query | select * from a left join (select id,count(info) from b + | where exists (select 1 from a where a.id=b.id and a.info='test1') + | + | + | group by id) b on (a.id=b.id) where a.info='test1'; explain_jsonb_plan | Nested Loop Left Join + | Join Filter: (a.id = b.id) + | -> Index Scan using idx_a_info on a + | Index Cond: (info = 'test1'::text) + | -> GroupAggregate + | Group Key: b.id + | -> Sort + | Sort Key: b.id + | -> Nested Loop + | -> HashAggregate + | Group Key: a_1.id + | -> Index Scan using idx_a_info on a a_1 + | Index Cond: (info = 'test1'::text) + | -> Index Scan using idx_b_id on b + | Index Cond: (id = a_1.id) + |9. 替换（篡改）执行计划
将query_hash=1668453880的执行计划替换为1956817209的执行计划
达到query rewrite的目的
update sr_plans set plan=(select plan from sr_plans where query_hash=1956817209) where query_hash=1668453880; -[ RECORD 3 ]------+------------------------------------------------------------------------------------------------------------ query_hash | 1668453880 query | select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; explain_jsonb_plan | Nested Loop Left Join + | Join Filter: (a.id = b.id) + | -> Index Scan using idx_a_info on a + | Index Cond: (info = 'test1'::text) + | -> GroupAggregate + | Group Key: b.id + | -> Sort + | Sort Key: b.id + | -> Nested Loop + | -> HashAggregate + | Group Key: a_1.id + | -> Index Scan using idx_a_info on a a_1 + | Index Cond: (info = 'test1'::text) + | -> Index Scan using idx_b_id on b + | Index Cond: (id = a_1.id) + |10. 允许QUERY使用sr_plan保存的执行计划
update sr_plans set enable=true where query_hash=1668453880;11. 验证QUERY是否已使用sr_plan保存的执行计划
postgres=# \set VERBOSITY verbose postgres=# explain select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; WARNING: 01000: Ok we find saved plan. LOCATION: sr_planner, sr_plan.c:145 QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop Left Join (cost=5.37..7.43 rows=1 width=27) Join Filter: (a.id = b.id) -> Index Scan using idx_a_info on a (cost=0.43..2.45 rows=1 width=15) Index Cond: (info = 'test1'::text) -> GroupAggregate (cost=4.94..4.96 rows=1 width=12) Group Key: b.id -> Sort (cost=4.94..4.94 rows=1 width=15) Sort Key: b.id -> Nested Loop (cost=2.89..4.93 rows=1 width=15) -> HashAggregate (cost=2.46..2.46 rows=1 width=4) Group Key: a_1.id -> Index Scan using idx_a_info on a a_1 (cost=0.43..2.45 rows=1 width=4) Index Cond: (info = 'test1'::text) -> Index Scan using idx_b_id on b (cost=0.43..2.45 rows=1 width=15) Index Cond: (id = a_1.id) (15 rows) postgres=# select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1'; WARNING: Ok we find saved plan. id | info | id | count ----+-------+----+------- 1 | test1 | 1 | 1 (1 row)1. PostgreSQL 本身支持的聚合、JOIN、访问方法、query rewrite等非常丰富，通过 ExplainNode@src/backend/commands/explain.c 代码可以看到，支持非常的丰富。
switch (nodeTag(plan)) case T_Result: case T_ModifyTable: switch (((ModifyTable *) plan)->operation) case CMD_INSERT: case CMD_UPDATE: case CMD_DELETE: case T_Append: case T_MergeAppend: case T_RecursiveUnion: case T_BitmapAnd: case T_BitmapOr: case T_NestLoop: case T_MergeJoin: pname = "Merge"; /* "Join" gets added by jointype switch */ case T_HashJoin: pname = "Hash"; /* "Join" gets added by jointype switch */ case T_SeqScan: case T_SampleScan: case T_Gather: case T_IndexScan: case T_IndexOnlyScan: case T_BitmapIndexScan: case T_BitmapHeapScan: case T_TidScan: case T_SubqueryScan: case T_FunctionScan: case T_ValuesScan: case T_CteScan: case T_WorkTableScan: case T_ForeignScan: switch (((ForeignScan *) plan)->operation) case CMD_SELECT: case CMD_INSERT: case CMD_UPDATE: case CMD_DELETE: case T_CustomScan: case T_Material: case T_Sort: case T_Group: case T_Agg: switch (agg->aggstrategy) case AGG_PLAIN: case AGG_SORTED: case AGG_HASHED: case T_WindowAgg: case T_Unique: case T_SetOp: switch (((SetOp *) plan)->strategy) case SETOP_SORTED: case SETOP_HASHED: case T_LockRows: case T_Limit: case T_Hash: switch (nodeTag(plan)) case T_SeqScan: case T_SampleScan: case T_BitmapHeapScan: case T_TidScan: case T_SubqueryScan: case T_FunctionScan: case T_ValuesScan: case T_CteScan: case T_WorkTableScan: case T_ForeignScan: case T_CustomScan: case T_IndexScan: case T_IndexOnlyScan: case T_BitmapIndexScan: case T_ModifyTable: case T_NestLoop: case T_MergeJoin: case T_HashJoin: switch (((Join *) plan)->jointype) case JOIN_INNER: case JOIN_LEFT: case JOIN_FULL: case JOIN_RIGHT: case JOIN_SEMI: case JOIN_ANTI: case T_SetOp: switch (((SetOp *) plan)->cmd) case SETOPCMD_INTERSECT: case SETOPCMD_INTERSECT_ALL: case SETOPCMD_EXCEPT: case SETOPCMD_EXCEPT_ALL: switch (nodeTag(plan)) case T_IndexScan: case T_IndexOnlyScan: case T_BitmapIndexScan: case T_BitmapHeapScan: case T_SampleScan: case T_SeqScan: case T_ValuesScan: case T_CteScan: case T_WorkTableScan: case T_SubqueryScan: case T_Gather: case T_FunctionScan: case T_TidScan: case T_ForeignScan: case T_CustomScan: case T_NestLoop: case T_MergeJoin: case T_HashJoin: case T_Agg: case T_Group: case T_Sort: case T_MergeAppend: case T_Result: case T_ModifyTable: case T_Hash: switch (nodeTag(plan)) case T_ModifyTable: case T_Append: case T_MergeAppend: case T_BitmapAnd: case T_BitmapOr: case T_SubqueryScan: case T_CustomScan:2. 通过sr_plan插件，我们可以保存、篡改、固定QUERY的执行计划，达到与oracle outline system同样的效果。
3. 只要parser后的QUERY不变，执行计划就不会变化。
postgres=# explain /*+ */ select * from a left join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info='test1' ; WARNING: Ok we find saved plan. QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop Left Join (cost=5.37..7.43 rows=1 width=27) Join Filter: (a.id = b.id) -> Index Scan using idx_a_info on a (cost=0.43..2.45 rows=1 width=15) Index Cond: (info = 'test1'::text) -> GroupAggregate (cost=4.94..4.96 rows=1 width=12) Group Key: b.id -> Sort (cost=4.94..4.94 rows=1 width=15) Sort Key: b.id -> Nested Loop (cost=2.89..4.93 rows=1 width=15) -> HashAggregate (cost=2.46..2.46 rows=1 width=4) Group Key: a_1.id -> Index Scan using idx_a_info on a a_1 (cost=0.43..2.45 rows=1 width=4) Index Cond: (info = 'test1'::text) -> Index Scan using idx_b_id on b (cost=0.43..2.45 rows=1 width=15) Index Cond: (id = a_1.id) (15 rows)4. 除了sr_plan插件，PostgreSQL还有一个PLAN HINT插件，可以强行指定执行计划，减少PLAN的时间，同时也可以避免PLAN不稳定的问题。
当然了，PostgreSQL本身在执行计划，统计信息的更新方面都是非常给力的，需要使用以上插件的地方相对较少。
5. sr_plan支持绑定变量的SQL,使用_p函数表示绑定参数
In addition sr plan allows you to save a parameterized query plan. In this case, we have some constants in the query are not essential. For the parameters we use a special function _p (anyelement) example: select query_hash from sr_plans where query_hash=1000+_p(10); if we keep the plan for the query and enable it to be used also for the following queries: select query_hash from sr_plans where query_hash=1000+_p(11); select query_hash from sr_plans where query_hash=1000+_p(-5);6. 你甚至可以改写QUERY，连接收对象都改掉。
postgres=# create table d(id int, info text); CREATE TABLE postgres=# create table e(id int, info text,crt_time timestamp); CREATE TABLE postgres=# set sr_plan.write_mode = true; SET postgres=# select * from d join e on (d.id=e.id) where e.info='a'; id | info | id | info | crt_time ----+------+----+------+---------- (0 rows) postgres=# select * from d where id=1; id | info ----+------ (0 rows) postgres=# set sr_plan.write_mode = false; SET postgres=# select query_hash,query,explain_jsonb_plan(plan) from sr_plans where query ~ 'from d'; query_hash | query | explain_jsonb_plan -------------+---------------------------------------------------------+------------------------------------------ -266039606 | select * from d join e on (d.id=e.id) where e.info='a'; | Hash Join + | | Hash Cond: (d.id = e.id) + | | -> Seq Scan on d + | | -> Hash + | | -> Seq Scan on e + | | Filter: (info = 'a'::text)+ | | -1283869506 | select * from d where id=1; | Seq Scan on d + | | Filter: (id = 1) + | | (2 rows) postgres=# update sr_plans set enable =true,plan=(select plan from sr_plans where query_hash=-266039606) where query_hash=-1283869506; UPDATE 1 postgres=# select * from d where id=1; WARNING: Ok we find saved plan. id | info | id | info | crt_time ----+------+----+------+---------- (0 rows) postgres=# explain select * from d where id=1; WARNING: Ok we find saved plan. QUERY PLAN --------------------------------------------------------------- Hash Join (cost=24.20..52.04 rows=38 width=80) Hash Cond: (d.id = e.id) -> Seq Scan on d (cost=0.00..22.70 rows=1270 width=36) -> Hash (cost=24.12..24.12 rows=6 width=44) -> Seq Scan on e (cost=0.00..24.12 rows=6 width=44) Filter: (info = 'a'::text) (6 rows)《关键时刻HINT出彩 - PG优化器的参数优化、执行计划固化CASE》
《PostgreSQL 特性分析 Plan Hint》
《阿里云 PostgreSQL pg_hint_plan插件的用法》
《PostgreSQL SQL HINT的使用(pg_hint_plan)》
https://github.com/postgrespro/sr_plan
