PostgreSQL Oracle 兼容性之 - SQL OUTLINE插件sr_plan (保存、篡改、固定 执行计划)

TAG 21

作者

digoal

日期

2017-02-28

标签

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插件介绍

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 特性分析 Plan Hint》

《阿里云 PostgreSQL pg_hint_plan插件的用法》

《PostgreSQL SQL HINT的使用(pg_hint_plan)》

https://github.com/postgrespro/sr_plan

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.