調整執行計畫 - PolarDB

PolarDB PostgreSQL版（相容Oracle）提供了Outline功能，用於抓取、查看和刪除固定的執行計畫。

功能介紹

執行計畫管理SPM（SQL Plan Management）是一種預防性機制，可以使最佳化器自動管理執行計畫，確保資料庫僅使用已知或經過驗證的計劃。

Outline是SPM的子集，僅支援固定執行計畫，不支援演化執行計畫。目前PolarDB的Outline功能僅支援固定PREPARE類型語句的執行計畫。

PolarDB的Outline功能是通過polar_outline外掛程式實現的，如需使用，請前往配額中心，在配額名稱PolarDB PG 調整執行計畫的操作列，單擊申請，申請開啟。

建立外掛程式

建立外掛程式語句如下：

CREATE EXTENSION polar_outline;

配置參數

開啟Outline固定執行計畫功能，開啟語句如下：

SET polar_outline.use_stored_plan TO ON;

由於Outline定義的函數和關係（Relation）都在polar_outline這個schema下，為了方便使用，您可以通過如下語句設定路徑。

SET search_path To "$user",public,polar_outline;

準備測試資料

建立表t，並在表中插入資料。語句如下：

CREATE TABLE t(a INT,b INT);
INSERT INTO t SELECT i, i FROM generate_series(1, 1000)i;

更新表資料以確保最佳化器使用的統計資訊正確。

ANALYZE t;

執行一個PREPARE類型的語句。

PREPARE test AS SELECT * FROM t WHERE a=$1;

說明

測試資料僅適用於本文中的樣本，實際使用中請根據您的實際情況進行調整。
您也可以使用hint功能來控制您想要被outline抓取的執行計畫，從而使其他沒有使用hint的語句也會被outline固定成您想要的計劃。

抓取和固定執行計畫

目前PolarDB提供了兩種方法來抓取和固定執行計畫，分別如下：

使用polar_outline_create函數抓取執行計畫（推薦使用），語句如下：
```
SELECT polar_outline_create('EXECUTE test(100)');
```
返回資訊如下：
```
 polar_outline_create 
----------------------
 t
(1 row)
```
使用執行計畫抓取功能，該方法更適合批量抓取執行計畫，操作方式如下：
重要開啟執行計畫抓取功能會導致計畫快取（plancache）
1. 開啟抓取執行計畫功能，語句如下：
```
SET polar_outline.capture_plan TO ON;
```
2. 抓取執行計畫，語句如下：
```
EXECUTE test(100);
```
  返回資訊如下：
```
  a  |  b
-----+-----
 100 | 100
(1 row)
```
3. 關閉抓取執行計畫功能，語句如下：
```
SET polar_outline.capture_plan TO OFF;
```

參數配置

參數	說明
use_stored_plan	開啟或關閉Outline固定執行計畫。開啟命令如下： `SET polar_outline.use_stored_plan TO ON;` 關閉命令如下： `SET polar_outline.use_stored_plan TO OFF;`
capture_plan	開啟或關閉Outline計劃抓取開關。開啟命令如下： `SET polar_outline.capture_plan TO ON;` 關閉命令如下： `SET polar_outline.capture_plan TO OFF;`
log_usage	控制outline內部日誌的輸出層級。 `set polar_outline.log_usage To none; set polar_outline.log_usage To debug; set polar_outline.log_usage To debug1; set polar_outline.log_usage To debug2; set polar_outline.log_usage To debug3; set polar_outline.log_usage To debug4; set polar_outline.log_usage To debug5; set polar_outline.log_usage To log; set polar_outline.log_usage To info; set polar_outline.log_usage To notice; set polar_outline.log_usage To warning;`

通過outline實現查詢改寫

在PolarDB查詢改寫不符合預期時，您可以使用outline來實現SQL的查詢改寫。

說明錯誤的使用可能會導致PolarDB輸出錯誤的執行計畫，返回錯誤的結果集，請確保改寫計劃的等價。

如下所示，SQL1和SQL2是等價的。

SQL1: 
select t.a, t2.avg_b
from t join (select avg(b) as avg_b, a 
             from t2
             group by a) t2
on t2.a = t.a and t.c < $1 
order by t.a;

SQL2:
select t.a, t2.avg_b
from t join lateral (select avg(b) as avg_b
                     from t2
                     where t2.a = t.a) as t2
on t.c < $1
order by t.a;

通過以下步驟展示如何通過outline將SQL1查詢改寫成SQL2的。

執行以下命令，準備資料。

CREATE TABLE t(a int, b int, c int);
insert into t select i % 100000, i, i from generate_series(1, 1000000) i;
create table t2 as select * from t;
create index on t(c);
create index on t2(a);
ANALYZE t,t2;

執行以下命令，將準備改寫的原SQL和改寫後的SQL抓取進outline中。

prepare s1 as select t.a, t2.avg_b from t join (select avg(b) as avg_b, a from t2 group by a) t2
on t2.a = t.a and t.c < $1 order by t.a;
prepare s2 as select t.a, t2.avg_b from t join lateral (select avg(b) as avg_b from t2 where t2.a = t.a) as t2
on t.c < $1 order by t.a;
SELECT polar_outline.polar_outline_create('EXECUTE s1(5)');
 polar_outline_create 
----------------------
 t
(1 row)

SELECT polar_outline.polar_outline_create('EXECUTE s2(5)');
 polar_outline_create 
----------------------
 t
(1 row)

執行以下命令，使用polar_outline_switch功能交換兩個SQL的計劃。
說明輸入的參數為兩個計劃在outline中的ID，原SQL的計劃即被改寫為改寫後的SQL的計劃。
```
SELECT polar_outline.polar_outline_switch(1,2);
 polar_outline_switch 
----------------------
 t
(1 row)
```
執行以下命令，刪除outline中改寫後的SQL。
說明計劃改寫完成後，需要清除outline中SQL2的無用計劃。
```
SELECT polar_outline.polar_outline_delete(2);
```

通過outline實現查詢改寫的結果如下所示：

查詢改寫前：s1是改寫前的計劃
EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                QUERY PLAN                 
-------------------------------------------
 Sort
   Sort Key: t.a
   ->  Hash Join
         Hash Cond: (t.a = t2.a)
         ->  Index Scan using t_c_idx on t
               Index Cond: (c < $1)
         ->  Hash
               ->  HashAggregate
                     Group Key: t2.a
                     ->  Seq Scan on t2
(10 rows)

SELECT polar_outline_switch(1,2);
 polar_outline_switch 
----------------------
 t
(1 row)

查詢改寫後：s1是改寫後s2的計劃
EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                      QUERY PLAN                       
-------------------------------------------------------
 Sort
   Sort Key: t.a
   ->  Nested Loop
         ->  Index Scan using t_c_idx on t
               Index Cond: (c < $1)
         ->  Aggregate
               ->  Bitmap Heap Scan on t2
                     Recheck Cond: (a = t.a)
                     ->  Bitmap Index Scan on t2_a_idx
                           Index Cond: (a = t.a)
(10 rows)