改善 Aurora PostgreSQL 查詢計劃

透過評估計劃效能和修正計劃來改善查詢計劃管理。如需改善查詢計劃的詳細資訊，請參閱下列主題。

主題

評估計劃效能
使用 pg_hint_plan 修正計劃

評估計劃效能

當最佳化工具將計劃擷取為未核准之後，請使用 apg_plan_mgmt.evolve_plan_baselines 函數，根據實際效能來比較計劃。根據效能試驗的結果而定，您可以將計劃的狀態從未核准變更為已核准或已拒絕。如果計劃不符合您的需要，您可以改為決定使用 apg_plan_mgmt.evolve_plan_baselines 函數來暫時停用計劃。

核准較佳計劃

以下範例示範如何使用 apg_plan_mgmt.evolve_plan_baselines 函數，將受管計劃的狀態變更為已核准。


SELECT apg_plan_mgmt.evolve_plan_baselines (
   sql_hash, 
   plan_hash, 
   min_speedup_factor := 1.0, 
   action := 'approve'
) 
FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';


NOTICE:     rangequery (1,10000)
NOTICE:     Baseline   [ Planning time 0.761 ms, Execution time 13.261 ms]
NOTICE:     Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms]
NOTICE:     Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms
NOTICE:     Unapproved -> Approved
evolve_plan_baselines 
-----------------------
0
(1 row)

輸出顯示 rangequery 陳述式的效能報告，參數繫結為 1 和 10,000。新的未核准計劃 (Baseline+1) 比先前已核准的最佳計劃 (Baseline) 更好。若要確認新計劃現在 Approved，請查看 apg_plan_mgmt.dba_plans 檢視。


SELECT sql_hash, plan_hash, status, enabled, stmt_name 
FROM apg_plan_mgmt.dba_plans;


sql_hash  | plan_hash |  status  | enabled | stmt_name  
------------+-----------+----------+---------+------------
1984047223 | 512153379 | Approved | t       | rangequery
1984047223 | 512284451 | Approved | t       | rangequery
(2 rows)

受管計劃現在包含兩個已核准的計劃，當作陳述式的計劃基線。您也可以呼叫 apg_plan_mgmt.set_plan_status 函數，直接將計劃的狀態欄位設為 'Approved'、'Rejected'、'Unapproved' 或 'Preferred'。

拒絕或停用較慢的計劃

若要拒絕或停用計劃，請將 'reject' 或 'disable'當作動作參數傳給 apg_plan_mgmt.evolve_plan_baselines 函數。此範例停用任何已擷取的 Unapproved 計劃，該計劃比陳述式的最佳 Approved 計劃還慢至少 10%。


SELECT apg_plan_mgmt.evolve_plan_baselines(
sql_hash,  -- The managed statement ID
plan_hash, -- The plan ID
1.1,       -- number of times faster the plan must be 
'disable'  -- The action to take. This sets the enabled field to false.
)
FROM apg_plan_mgmt.dba_plans
WHERE status = 'Unapproved' AND   -- plan is Unapproved
origin = 'Automatic';       -- plan was auto-captured

您也可以直接將計劃設為已拒絕或已停用。若要直接將計劃的已啟用欄位設為 true 或 false，請呼叫 apg_plan_mgmt.set_plan_enabled 函數。若要直接將計劃的狀態欄位設為 'Approved'、'Rejected'、'Unapproved' 或 'Preferred'，請呼叫 apg_plan_mgmt.set_plan_status 函數。

若要刪除無效和您預期仍無效的計劃，請使用 apg_plan_mgmt.validate_plans 函數。此函數可讓您刪除或停用無效的計劃。如需詳細資訊，請參閱驗證計劃。

使用 pg_hint_plan 修正計劃

查詢最佳化工具精心設計來為所有陳述式尋找最佳計劃，且最佳化工具在大多數情況下會找到好的計劃。不過，有時您可能知道有一個比最佳化工具所產生的計劃好得多的計劃。有兩個建議方法可讓最佳化工具產生理想的計劃，包括使用 pg_hint_plan 延伸，或在 PostgreSQL 中設定 Grand Unified Configuration (GUC) 變數：

pg_hint_plan 延伸 – 使用 PostgreSQL 的 pg_hint_plan 延伸來指定「提示」，以修改規劃器的運作方式。若要安裝和進一步了解如何使用 pg_hint_plan 延伸，請參閱 pg_hint_plan 文件。
GUC 變數 – 覆寫一個或多個成本模型參數或其他最佳化工具參數，例如 from_collapse_limit 或 GEQO_threshold。

當您使用以上其中一項技巧來強制查詢最佳化工具使用計劃時，您也可以使用查詢計劃管理來擷取和強制使用新的計劃。

您可以使用 pg_hint_plan 延伸來變更 SQL 陳述式的聯結順序、聯結方法或存取路徑。您使用 SQL 註解搭配特殊的 pg_hint_plan 語法，以修改最佳化工具建立計劃的方式。例如，假設有問題的 SQL 陳述式具有雙向聯結。


SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

再假設最佳化工具選擇聯結順序 (t1, t2)，但您知道聯結順序 (t2, t1) 更快。下列提示強制最佳化工具使用較快的聯結順序 (t2, t1)。包含 EXPLAIN 會讓最佳化工具為 SQL 陳述式產生計劃，但不需執行陳述式。(未顯示輸出。)


/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id;

下列步驟示範如何使用 pg_hint_plan。

使用 pg_hint_plan 修改最佳化工具所產生的計劃並擷取計劃

啟用手動擷取模式。


SET apg_plan_mgmt.capture_plan_baselines = manual;

針對所關注的 SQL 陳述式來指定提示。
```
/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * 
FROM t1, t2 
WHERE t1.id = t2.id; 
```
執行之後，最佳化工具會擷取 apg_plan_mgmt.dba_plans 檢視中的計劃。擷取的計劃不含特殊的 pg_hint_plan 註解語法，因為查詢計劃管理會移除開頭註解而將陳述式標準化。

使用 apg_plan_mgmt.dba_plans 檢視來檢視受管計劃。


SELECT sql_hash, plan_hash, status, sql_text, plan_outline 
FROM apg_plan_mgmt.dba_plans;

將計劃的狀態設定為 Preferred。當最低成本的計劃還不是 Approved 或 Preferred 時，這麼做可以保證最佳化工具選擇執行該計劃，而非從已核准的一組計劃中選擇。
```
SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' ); 
```
關閉手動計劃擷取並強制使用受管計劃。
```
SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;
```
現在，當原始 SQL 陳述式執行時，最佳化工具會選擇 Approved 或 Preferred 計劃。如果最低成本計劃不是 Approved 也不是 Preferred，最佳化工具會選擇 Preferred 計劃。

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

在 dba_plans 檢視中檢查 Aurora PostgreSQL 查詢計劃

刪除計劃