維護 Aurora PostgreSQL 執行計劃 - Amazon Aurora
評估計劃效能驗證計劃使用 pg_hint_plan 修正計劃刪除計劃匯出和匯入計劃

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

維護 Aurora PostgreSQL 執行計劃

查詢計劃管理提供技術和函數，以新增、維護和改善執行計劃。

評估計劃效能

當最佳化工具將計劃擷取為未核准之後，請使用 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 函數來刪除或停用無效的計劃。

當計劃所依賴的物件 (例如索引或資料表) 移除時，計劃會變成無效或過時。不過，如果重建已移除的物件，則計劃可能只是暫時無效。如果無效的計劃後來變成有效，您可能會選擇停用無效的計劃，或什麼都不做，而非刪除它。

若要尋找並刪除所有無效且在上週未使用的計劃，請如下使用 apg_plan_mgmt.validate_plans 函數。

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') 
FROM apg_plan_mgmt.dba_plans
WHERE last_used < (current_date - interval '7 days');

若要直接啟用或停用計劃，請使用 apg_plan_mgmt.set_plan_enabled 函數。

使用 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 修改最佳化工具所產生的計劃並擷取計劃

  1. 啟用手動擷取模式。

    SET apg_plan_mgmt.capture_plan_baselines = manual;

  2. 針對所關注的 SQL 陳述式來指定提示。

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

    執行之後，最佳化工具會擷取 apg_plan_mgmt.dba_plans 檢視中的計劃。擷取的計劃不含特殊的 pg_hint_plan 註解語法，因為查詢計劃管理會移除開頭註解而將陳述式標準化。

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

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

  4. 將計劃的狀態設定為 Preferred。當最低成本的計劃還不是 Approved 或 Preferred 時，這麼做可以保證最佳化工具選擇執行該計劃，而非從已核准的一組計劃中選擇。

    SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );

  5. 關閉手動計劃擷取並強制使用受管計劃。

    SET apg_plan_mgmt.capture_plan_baselines = false;
SET apg_plan_mgmt.use_plan_baselines = true;

    現在，當原始 SQL 陳述式執行時，最佳化工具會選擇 Approved 或 Preferred 計劃。如果最低成本計劃不是 Approved 也不是 Preferred，最佳化工具會選擇 Preferred 計劃。

刪除計劃

如果計劃已經超過一個月 (特別是 32 天) 未使用，系統會自動刪除這些計劃。此為 apg_plan_mgmt.plan_retention_period 參數的預設設定。您可以延長計劃保留期間，或從值 1 開始的較短期間。判斷上次使用計畫後經過的天數，方法是從目前日期減去 last_used 日期。last_used 日期是最佳化工具選擇計劃做為最低成本計劃或執行計劃的最近日期。日期是針對 apg_plan_mgmt.dba_plans 檢視中的計劃存放的。

建議您刪除長時間未使用或沒有用處的計劃。每個計劃都有 last_used 日期，最佳化工具每次執行計劃，或選擇計劃當作陳述式的最低成本計劃時，就會更新此日期。檢查最後 last_used 日期，以識別您可以安全刪除的計劃。

下列查詢會傳回三欄表格，其中包含計劃總數、無法刪除的計劃數，以及已順利刪除的計劃數。它具有巢狀查詢，這是一個範例，展示如何使用 apg_plan_mgmt.delete_plan 函數，來刪除過去 31 天未被選為最低成本計劃且其狀態為 Rejected 的所有計劃。

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   


 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

如需更多詳細資訊，請參閱 apg_plan_mgmt.delete_plan。

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

重要

如果您未刪除無關的計劃，最後可能會耗盡已保留給查詢計劃管理的共用記憶體。若要控制受管計劃可用的記憶體，請使用 apg_plan_mgmt.max_plans 參數。在自訂資料庫參數群組中設定此參數，然後重新啟動資料庫執行個體，讓變更生效。如需更多詳細資訊，請參閱 apg_plan_mgmt.max_plans 參數。

匯出和匯入計劃

您可以匯出受管計劃，再匯入另一個資料庫執行個體中。

匯出受管計劃

獲授權使用者可以將 apg_plan_mgmt.plans 資料表的任何子集複製到另一個資料表，然後使用 pg_dump 命令儲存它。以下是範例。

CREATE TABLE plans_copy AS SELECT * 
FROM apg_plan_mgmt.plans [ WHERE predicates ] ;
% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar
DROP TABLE apg_plan_mgmt.plans_copy;
匯入受管計劃

  1. 將所匯出受管計劃的 .tar 檔案複製到將還原計劃的系統上。

  2. 使用 pg_restore 命令將 tar 檔案複製到新的資料表。

    % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar

  3. 合併 plans_copy 資料表與 apg_plan_mgmt.plans 資料表，如下列範例所示。

    注意

    在某些情況下，您可能會從 apg_plan_mgmt 延伸套件的某個版本傾印，然後還原成不同的版本。在這種情況下，計劃資料表中的資料欄可能不同。如果是這樣的話，請明確命名各欄，而非使用 SELECT *。

    INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy
 ON CONFLICT ON CONSTRAINT plans_pkey
 DO UPDATE SET
 status = EXCLUDED.status,
 enabled = EXCLUDED.enabled,
 -- Save the most recent last_used date 
 --
 last_used = CASE WHEN EXCLUDED.last_used > plans.last_used 
 THEN EXCLUDED.last_used ELSE plans.last_used END, 
 -- Save statistics gathered by evolve_plan_baselines, if it ran:
 --
 estimated_startup_cost = EXCLUDED.estimated_startup_cost,
 estimated_total_cost = EXCLUDED.estimated_total_cost,
 planning_time_ms = EXCLUDED.planning_time_ms,
 execution_time_ms = EXCLUDED.execution_time_ms,
 total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, 
 execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;

  4. 將受管計劃重新載入共用記憶體，並移除暫時計劃資料表。

    SELECT apg_plan_mgmt.reload(); -- refresh shared memory
DROP TABLE plans_copy;