PostgreSQLSQLOUTLINE外掛sr_plan(儲存、篡改、固定執行計劃)
標籤
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 pg_hint_plan外掛的用法》
《PostgreSQL SQL HINT的使用(pg_hint_plan)》
https://github.com/postgrespro/sr_plan
相關文章
- oracle 固定執行計劃Oracle
- oracle固定執行計劃--sqlprofileOracleSQL
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 【SPM】Oracle如何固定執行計劃Oracle
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- baseline固定SQL執行計劃SQL
- 用sql profile來固定執行計劃SQL
- 使用OUTLINE固定執行計劃
- Oracle手動固定SQL執行計劃OracleSQL
- 使用SPM和STA進行固定執行計劃
- Oracle緊急固定執行計劃之手段Oracle
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- SQL BASELINE修改固定執行計劃SQL
- 用outline修改固定執行計劃
- 【usb儲存掛載】android6.0固定usb儲存掛載路徑Android
- 儲存過程中檢視sql執行計劃的方法儲存過程SQL
- oracle使用outline固定執行計劃事例Oracle
- 使用coe_xfr_sql_profile固定執行計劃SQL
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 各大OJ的外掛（篡改猴）
- [外掛擴充套件]計劃任務外掛套件
- sql最佳化：使用儲存提綱穩定sql執行計劃SQL
- Props 混入 外掛 插槽 本地儲存
- 執行計劃-1：獲取執行計劃
- tampermonkey外掛指令碼油猴外掛下載 - 篡改猴瀏覽器外掛指令碼瀏覽器
- 透過空間佔用和執行計劃瞭解SQL Server的行儲存索引SQLServer索引
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- 執行計劃
- 實現Dart版本物件儲存(COS)外掛Dart物件
- K8S儲存外掛-FlexVolume && CSIK8SFlex
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【sql調優之執行計劃】獲取執行計劃SQL
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- sql 執行計劃SQL
- ORACLE執行計劃Oracle