sr_plan
概述
sr_plan类似Oracle Outline系统。它可以用来锁定执行计划。如果您不信任规划器或能够制定更好的计划，那本插件是必需的。
sr_plan插件可以保存QUERY的执行计划，（支持绑定变量的QUERY），同时允许篡改执行计划，让篡改的执行计划生效。
针对每一条保存的执行计划，允许单独开启或关闭。
sr_plan实际上通过post_parse_analyze_hook获取parser后的text并保存到sr_plan的query字段中，通过planner_hook保存、处理、返回保存的执行计划。
验证安装
testdb=# create extension sr_plan;
CREATE EXTENSION
用法示例
在需要使用sr_plan的数据库中创建extension, 会创建保留执行计划的表
testdb=# create extension sr_plan;
CREATE EXTENSION
testdb=# \d sr_plans
Table "public.sr_plans"
Column | Type | Collation | Nullable | Default
---------------+-------------------+-----------+----------+---------
query_hash | integer | | not null |
query_id | bigint | | not null |
plan_hash | integer | | not null |
enable | boolean | | not null |
query | character varying | | not null |
plan | text | | not null |
reloids | oid[] | | |
index_reloids | oid[] | | |
Indexes:
"sr_plans_query_hash_idx" btree (query_hash)
"sr_plans_query_index_oids" gin (index_reloids)
"sr_plans_query_oids" gin (reloids)
创建测试表，分别插入100万条记录
create table a(id int, info text);
create table b(id int, info text);
insert into a select generate_series(1,1000000), 'test'||generate_series(1,1000000); -- 插入100万数据
insert into b select * from a; -- 插入100万数据
create index idx_a_info on a (info);
create index idx_b_id on b(id);
开启sr_plan.write_mode, 允许sr_plan收集SQL和执行计划
testdb=# set sr_plan.write_mode = true;
SET
查看QUERY 1的执行计划
testdb=# 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 Left Join (cost=8.88..58897.90 rows=1 width=26) (actual time=2.412..2.83
2 rows=1 loops=1)
Output: a.id, a.info, b.id, (count(b.info))
Inner Unique: true
Merge Cond: (a.id = b.id)
Buffers: shared hit=2 read=6
-> Sort (cost=8.45..8.46 rows=1 width=14) (actual time=1.236..1.376 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.42..8.44 rows=1 wi
dth=14) (actual time=1.075..1.148 rows=1 loops=1)
Output: a.id, a.info
Index Cond: (a.info = 'test1'::text)
Buffers: shared hit=1 read=3
-> GroupAggregate (cost=0.42..46389.43 rows=1000000 width=12) (actual time=
0.986..1.091 rows=1 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.42..31389.42 rows=10
00000 width=14) (actual time=0.695..0.770 rows=2 loops=1)
Output: b.id, b.info
Buffers: shared hit=1 read=3
Planning:
Buffers: shared hit=98 read=3 dirtied=15 written=6
Planning Time: 9.499 ms
Execution Time: 3.330 ms
(25 rows)
查看QUERY 2的执行计划
testdb=# 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的聚合量减少了
group by id) b on (a.id=b.id) where a.info='test1';
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
Nested Loop Left Join (cost=17.34..25.40 rows=1 width=26) (actual time=1.337..
1.961 rows=1 loops=1)
Output: a.id, a.info, b.id, (count(b.info))
Inner Unique: true
Join Filter: (a.id = b.id)
Buffers: shared hit=12
-> Index Scan using idx_a_info on public.a (cost=0.42..8.44 rows=1 width=14
) (actual time=0.075..0.148 rows=1 loops=1)
Output: a.id, a.info
Index Cond: (a.info = 'test1'::text)
Buffers: shared hit=4
-> GroupAggregate (cost=16.92..16.94 rows=1 width=12) (actual time=1.144..1
.522 rows=1 loops=1)
Output: b.id, count(b.info)
Group Key: b.id
Buffers: shared hit=8
-> Sort (cost=16.92..16.92 rows=1 width=14) (actual time=0.988..1.333
rows=1 loops=1)
Output: b.id, b.info
Sort Key: b.id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=8
-> Nested Loop (cost=8.87..16.91 rows=1 width=14) (actual time=
0.389..1.106 rows=1 loops=1)
Output: b.id, b.info
Buffers: shared hit=8
-> HashAggregate (cost=8.45..8.46 rows=1 width=4) (actual
time=0.220..0.359 rows=1 loops=1)
Output: a_1.id
Group Key: a_1.id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=4
-> Index Scan using idx_a_info on public.a a_1 (cos
t=0.42..8.44 rows=1 width=4) (actual time=0.063..0.135 rows=1 loops=1)
Output: a_1.id, a_1.info
Index Cond: (a_1.info = 'test1'::text)
Buffers: shared hit=4
-> Index Scan using idx_b_id on public.b (cost=0.42..8.44
rows=1 width=14) (actual time=0.055..0.424 rows=1 loops=1)
Output: b.id, b.info
Index Cond: (b.id = a_1.id)
Buffers: shared hit=4
Planning:
Buffers: shared hit=27 read=2
Planning Time: 3.250 ms
Execution Time: 2.244 ms
(38 rows)
执行以下QUERY后，QUERY的执行计划被保存到sr_plans中
testdb=# 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)
testdb=# 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的聚合量减少了
group by id) b on (a.id=b.id) where a.info='test1';
id | info | id | count
----+-------+----+-------
1 | test1 | 1 | 1
(1 row)
禁止sr_plan收集SQL与执行计划
testdb=# set sr_plan.write_mode = false;
SET
查看保存的执行计划
testdb=# select query_hash,query from sr_plans ;
query_hash |
query
-------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------
1208984763 | explain (analyze,verbose,timing,costs,buffers) select * from a le
ft join (select id,count(info) from b group by id) b on (a.id=b.id) where a.info
='test1';
-2097954535 | explain (analyze,verbose,timing,costs,buffers) select * from a le
ft 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';
替换（篡改）执行计划将query_hash=1208984763的执行计划替换为-2097954535的执行计划达到query rewrite的目的
testdb=# update sr_plans set plan=(select plan from sr_plans where query_hash=-2097954535) where query_hash=1208984763;
UPDATE 1
允许QUERY使用sr_plan保存的执行计划
testdb=# update sr_plans set enable=true where query_hash=1208984763;
UPDATE 1
验证QUERY是否已使用sr_plan保存的执行计划
testdb=# \set VERBOSITY verbose
testdb=# 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
--------------------------------------------------------------------------------
---------
Merge Left Join (cost=8.88..58897.90 rows=1 width=26)
Merge Cond: (a.id = b.id)
-> Sort (cost=8.45..8.46 rows=1 width=14)
Sort Key: a.id
-> Index Scan using idx_a_info on a (cost=0.42..8.44 rows=1 width=14)
Index Cond: (info = 'test1'::text)
-> GroupAggregate (cost=0.42..46389.43 rows=1000000 width=12)
Group Key: b.id
-> Index Scan using idx_b_id on b (cost=0.42..31389.42 rows=1000000 w
idth=14)
(9 rows)
testdb=# 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)