支援的 SQL 陳述式限制術語可用版本開啟查詢計劃管理升級查詢計劃管理關閉 Aurora PostgreSQL 查詢計劃管理

Aurora PostgreSQL 查詢計劃管理的概觀

Aurora PostgreSQL 查詢計劃管理旨在確保計劃穩定性，無論資料庫的變更是否可能導致查詢計劃迴歸。當最佳化工具在系統或資料庫變更之後，針對指定的 SQL 陳述式選擇次佳計劃時，就會發生「查詢計劃迴歸」。統計資料、限制條件、環境設定、查詢參數繫結的變更，以及 PostgreSQL 資料庫引擎的升級都可能造成計劃迴歸。

使用 Aurora PostgreSQL 查詢計劃管理時，您可以控制查詢執行計劃如何和何時變更。Aurora PostgreSQL 查詢計劃管理包括下列好處：

強制最佳化工具從少數已知的良好計劃中選擇，以提高計劃穩定性。
將計劃集中最佳化，然後整體分發最佳計劃。
識別未使用的索引，並評估建立或捨棄索引所造成的影響。
自動偵測最佳化工具新發現的最低成本計劃。
以較低的風險來嘗試新的最佳化工具功能，因為您可以選擇只核准可提高效能的計劃變更。

您可以主動使用查詢計劃管理提供的工具，為某些查詢指定最佳計劃。或者，您可以使用查詢計劃管理來反應不斷變化的情況，並避免計劃回歸。如需詳細資訊，請參閱Aurora PostgreSQL 查詢計劃管理的最佳實務。

支援的 SQL 陳述式

查詢計劃管理支援下列類型的 SQL 陳述式。

任何 SELECT、INSERT、UPDATE 或 DELETE 陳述式，而不論複雜性為何。
預備陳述式。如需詳細資訊，請參閱 PostgreSQL 文件中的 PREPARE。
動態陳述式，包括以立即模式執行的陳述式。如需詳細資訊，請參閱 PostgreSQL 文件中的 Dynamic SQL 和 EXECUTE IMMEDIATE。
嵌入式 SQL 命令和陳述式。如需詳細資訊，請參閱 PostgreSQL 文件中的嵌入式 SQL 命令。
具名函數內的陳述式。如需詳細資訊，請參閱 PostgreSQL 文件中的 CREATE FUNCTION。
包含暫存資料表的陳述式。
程序和 DO 區塊內的陳述式。

您可以在手動模式下搭配 EXPLAIN 使用查詢計畫管理來擷取計劃，而不需實際執行它。如需詳細資訊，請參閱分析最佳化工具的所選擇計劃。若要深入了解查詢計劃管理模式 (手動、自動)，請參閱擷取 Aurora PostgreSQL 執行計畫。

Aurora PostgreSQL 查詢計劃管理支援所有 PostgreSQL 語言功能，包括分割資料表、繼承、列層級安全性和遞迴一般資料表表達式 (CTE)。若要深入了解這些 PostgreSQL 語言功能，請參閱 PostgreSQL 文件中的資料表分割、資料列安全政策和 WITH 查詢 (通用資料表運算式)，以及其他主題。

如需不同版本之 Aurora PostgreSQL 查詢計畫管理功能的相關資訊，請參閱《Aurora PostgreSQL 版本資訊》中的 Aurora PostgreSQL apg_plan_mgmt 延伸模組版本。

查詢計劃管理限制

現行版本的 Aurora PostgreSQL 查詢計畫管理具有下列限制。

不會針對參考系統關係的陳述式擷取計畫 – 不會擷取參考系統關係的陳述式 (例如 pg_class)。這是設計的，以防止擷取內部使用的大量系統產生計劃。這也適用於檢視內的系統資料表。
Aurora PostgreSQL 資料庫叢集可能需要較大的資料庫執行個體類別 – 根據工作負載，查詢計畫管理可能需要具有 2 個以上 vCPU 的資料庫執行個體類別。max_worker_processes 的數目受資料庫執行個體類別大小限制。2-vCPU 資料庫執行個體類別 (例如 db.t3.medium) 提供的 max_worker_processes 數目可能不夠指定的工作負載使用。建議若您使用查詢計畫管理，請為 Aurora PostgreSQL 資料庫叢集選擇具有 2 個以上 vCPU 的資料庫執行個體類別。

當資料庫執行個體類別不支援工作負載時，查詢計畫管理會引發如下錯誤訊息。
```
WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.
```
在這種情況下，您應該將 Aurora PostgreSQL 資料庫叢集縱向擴展到具有更多記憶體的資料庫執行個體類別大小。如需詳細資訊，請參閱資料庫執行個體類別的支援資料庫引擎。
已存放在工作階段中的計畫不會受到影響 - 查詢計畫管理提供了一種方式，可在不變更應用程式碼的情況下影響查詢計畫。不過，當一般計畫已存放在現有的工作階段時，而且如果您想要變更其查詢計畫，則必須先在資料庫叢集參數群組中將 plan_cache_mode 設為 force_custom_plan。
出現下列情況時，apg_plan_mgmt.dba_plans 和 pg_stat_statements 中的 queryid 可能會出現差異：
- 物件在存放於 apg_plan_mgmt.dba_plans 之後遭到刪除並重新建立。
- apg_plan_mgmt.plans 表格是從另一個叢集匯入的。

如需不同版本之 Aurora PostgreSQL 查詢計畫管理功能的相關資訊，請參閱《Aurora PostgreSQL 版本資訊》中的 Aurora PostgreSQL apg_plan_mgmt 延伸模組版本。

查詢計劃管理術語

本主題使用下列術語。

受管陳述式: 最佳化工具在查詢計劃管理之下擷取的 SQL 陳述式。受管陳述式會將一或多個查詢執行計劃存放在 apg_plan_mgmt.dba_plans 檢視中。
計劃基準: 所指定受管陳述式的核准計劃集。亦即，受管陳述式的所有其 status 資料欄在 dba_plan 檢視中具有「已核准」的計劃。
計劃歷史記錄: 所指定受管陳述式的所有擷取計劃集。計劃歷史記錄包含針對陳述式擷取的所有計劃，不論狀態為何。
查詢計劃迴歸: 此情況是最佳化工具在對資料庫環境進行指定的變更 (例如新的 PostgreSQL 版本或統計資料的變更) 之前選擇較不理想的計劃。

Aurora PostgreSQL 查詢計劃管理版本

所有目前可用的 Aurora PostgreSQL 版本都支援查詢計劃管理。如需詳細資訊，請參閱《Aurora PostgreSQL 版本資訊》中的 Amazon Aurora PostgreSQL 更新。

當您安裝 apg_plan_mgmt 擴充功能時，查詢計劃管理功能會新增至您的 Aurora PostgreSQL 資料庫叢集。不同版本的 Aurora PostgreSQL 支援不同版本的 apg_plan_mgmt 擴充功能。建議您將查詢計劃管理擴充功能升級至 Aurora PostgreSQL 的最新版本。

注意

如需每個 apg_plan_mgmt 擴充功能版本的版本備註，請參閱《Aurora PostgreSQL 版本資訊》中的 Aurora PostgreSQL apg_plan_mgmt 擴充功能版本。

您可以使用 psql 和使用中繼命令 \dx 列出擴充功能來連線至執行個體，以識別叢集上執行的版本，如下所示。


labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

輸出顯示此叢集使用的是 1.0 版的擴充功能。只有特定 apg_plan_mgmt 版本適用於指定的 Aurora PostgreSQL 版本。在某些情況下，您可能需要將 Aurora PostgreSQL 資料庫叢集升級至新的次要版本，或套用修補程式，以便您可以升級至最新版的查詢計劃管理。輸出中顯示的 apg_plan_mgmt 1.0 版來自 Aurora PostgreSQL 10.17 版資料庫叢集，該叢集沒有更新版本的 apg_plan_mgmt 可用。在此情況下，Aurora PostgreSQL 資料庫叢集應升級至較新版的 PostgreSQL。

如需將 Aurora PostgreSQL 資料庫叢集升級至新版 PostgreSQL 的詳細資訊，請參閱 Amazon Aurora Postgre 的資料庫引擎更新SQL。

若要了解如何升級 apg_plan_mgmt 擴充功能，請參閱升級 Aurora PostgreSQL 查詢計劃管理。

開啟 Aurora PostgreSQL 查詢計劃管理

為 Aurora PostgreSQL 資料庫叢集設定查詢計劃管理魙涉及安裝擴充功能，以及變更數個資料庫叢集參數設定。您需要 rds_superuser 許可，才能安裝 apg_plan_mgmt 擴充功能和開啟 Aurora PostgreSQL 資料庫叢集的功能。

安裝擴充功能會建立新角色 apg_plan_mgmt。此角色可讓資料庫使用者檢視、管理及維護查詢計劃。身為具有 rds_superuser 許可的管理員，請務必視需要將 apg_plan_mgmt 角色授與資料庫使用者。

只有具有 rds_superuser 角色的使用者才能完成下列程序。建立 rds_superuser 延伸及其 apg_plan_mgmt 角色需要 apg_plan_mgmt。使用者必須獲授予 apg_plan_mgmt 角色，才能管理 apg_plan_mgmt 延伸。

開啟 Aurora PostgreSQL 資料庫叢集的查詢計劃管理

下列步驟會針對提交至 Aurora PostgreSQL 資料庫叢集的所有 SQL 陳述式開啟查詢計劃管理。這就是所謂的「自動」模式。若要進一步了解模式之間的差異，請參閱擷取 Aurora PostgreSQL 執行計畫。

前往 https://console.aws.amazon.com/rds/，開啟 Amazon RDS 主控台。
針對您的 Aurora PostgreSQL 資料庫叢集建立自訂資料庫叢集參數群組。您需要變更某些參數，才能啟動查詢計劃管理並設定其行為。如需詳細資訊，請參閱在 Aurora 中創建數據庫參數組。
開啟自訂資料庫叢集參數群組，並將 rds.enable_plan_management 參數設定為 1，如下圖所示。

如需詳細資訊，請參閱在 Amazon Aurora 中修改資料庫叢集參數群組中的參數。
建立您可以用來在執行個體層級設定查詢計劃參數的自訂資料庫參數群組。如需詳細資訊，請參閱在 Amazon Aurora 中建立資料庫叢集參數群組。
修改 Aurora PostgreSQL 資料庫叢集的寫入器執行個體，來使用自訂資料庫參數群組。如需詳細資訊，請參閱修改資料庫叢集中的資料庫執行個體。
修改 Aurora PostgreSQL 資料庫叢集，來使用自訂資料庫叢集參數群組。如需詳細資訊，請參閱使用主控台、CLI 和 API 修改資料庫叢集。
重新啟動資料庫執行個體來啟用自訂參數群組設定。
使用 psql 或 pgAdmin 連線至 Aurora PostgreSQL 資料庫叢集的資料庫執行個體端點。下列範例使用 rds_superuser 角色的預設 postgres 帳戶。
```
psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db
```
為資料庫執行個體建立 apg_plan_mgmt 擴充功能，如下所示。
```
labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
```
提示
在應用程式的範本資料庫中安裝 apg_plan_mgmt 延伸模組。預設範本資料庫命名為 template1。若要進一步了解，請參閱 PostgreSQL 文件中的範本資料庫。
將 apg_plan_mgmt.capture_plan_baselines 參數變更為 automatic。此設定會導致最佳化工具針對每個已計劃或執行兩次或多次的 SQL 陳述式產生計畫。

注意
查詢計劃管理也有您可以用於特定 SQL 陳述式的「手動」模式。若要進一步了解，請參閱擷取 Aurora PostgreSQL 執行計畫。
將 apg_plan_mgmt.use_plan_baselines 參數的值變更為 "on"。此參數會導致最佳化工具從其計劃基準中選擇陳述式的計劃。如需詳細資訊，請參閱使用 Aurora PostgreSQL 受管計劃。

注意
您可以修改工作階段的其中任一動態參數值，而不需要重新啟動執行個體。

當您的查詢計劃管理設定完成時，請務必將 apg_plan_mgmt 角色授與任何需要檢視、管理或維護查詢計劃的資料庫使用者。

升級 Aurora PostgreSQL 查詢計劃管理

建議您將查詢計劃管理擴充功能升級至 Aurora PostgreSQL 的最新版本。

以擁有 rds_superuser 權限的使用者身分，連線至 Aurora PostgreSQL 資料庫叢集的寫入器執行個體。如果您在設定執行個體時保留了預設名稱，則以 postgres 連線。這個範例展示如何使用 psql，但您也可以視需要使用 pgAdmin。
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```

執行以下查詢來升級擴充功能。


ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

使用 apg_plan_mgmt.validate_plans 函數更新所有計劃的雜湊。最佳化工具會驗證所有「已核准」、「未核准」和「已拒絕」計劃，以確保它們仍然是新版擴充功能的可行計劃。
```
SELECT apg_plan_mgmt.validate_plans('update_plan_hash');
```
若要進一步了此函數，請參閱驗證計劃。
使用 apg_plan_mgmt.reload 函數，搭配來自 dba_plan 檢視的已驗證計劃，重新整理共用記憶體中的任何計劃。
```
SELECT apg_plan_mgmt.reload();
```

若要深入了解可用於查詢計劃管理的所有功能，請參閱 Aurora PostgreSQL 查詢計劃管理的函數參考。

關閉 Aurora PostgreSQL 查詢計劃管理

您可以隨時關閉 apg_plan_mgmt.use_plan_baselines 和 apg_plan_mgmt.capture_plan_baselines 來停用查詢計劃管理。


labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;

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

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

文件慣用形式

管理 Aurora PostgreSQL 的查詢執行計劃

Aurora PostgreSQL 查詢計劃管理的最佳實務