PostgreSQL Oracle 兼容性之 - SQL OUTLINE插件sr_plan (保存、篡改、固定 执行计划)
背景
功能较为强大的数据库，通常都有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插件介绍
sr_plan插件，可以保存QUERY的执行计划，（支持绑定变量的QUERY），同时允许篡改执行计划，让篡改的执行计划生效。
针对每一条保存的执行计划，允许单独开启或关闭。
sr_plan实际上利用了PostgreSQL的钩子，通过post_parse_analyze_hook获取parser后的text并保存到sr_plan的query字段中，通过planner_hook保存、处理、返回保存的执行计划。
了解sr_plan的工作原理，我们来试用一下，看看以上query如何使用sr_plan来重写。
安装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 start
测试sr_plan
1. 在需要使用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 pg_hint_plan插件的用法》
《PostgreSQL SQL HINT的使用(pg_hint_plan)》
https://github.com/postgrespro/sr_plan