管理查詢計畫

在 AlloyDB for PostgreSQL 中，您可以透過查詢計畫管理功能，控管查詢最佳化工具用於查詢的執行計畫，進而提升及穩定查詢效能。查詢計畫管理功能會持續追蹤資料庫中的所有查詢計畫和執行統計資料。查看計畫和費用後，您可以核准要讓 AlloyDB 用於查詢的計畫。這有助於防止最佳化工具切換至成本效益較低的方案時，發生速度變慢的情況。

運作方式

在 PostgreSQL 中，查詢最佳化工具會根據預估成本，為每項查詢選取執行計畫。許多因素都會影響查詢成本，例如查詢參數、查詢複雜度、資料表大小、可用索引和系統資源。

由於查詢參數可能會隨著每次查詢執行而變更，動態選取查詢計畫不一定能產生最佳結果。處理查詢時，最佳化工具會評估各種執行計畫，並嘗試選擇最具成本效益的計畫。

參數變更可能會導致方案變更。雖然所選計畫通常是最具成本效益的選項，但有時可能會選擇成本效益較低的計畫，導致查詢效能不佳。查詢計畫管理功能可協助您瞭解最佳化工具產生的模式和計畫，並查看各項計畫，做出明智決策。

查詢計畫管理有兩個主要元件：

查詢計畫存放區
為資料庫啟用查詢計畫管理功能後，計畫存放區就會開始追蹤資料庫的歷來計畫和執行統計資料。查詢計畫存放區可讓您觀測查詢計畫的效能。
管理方案
查看可用方案後，您可以使用方案管理元件，核准特定查詢範本的一或多個方案。查詢計畫管理功能會追蹤這些已核准的計畫，並確保後續執行查詢時，查詢最佳化工具只會使用其中一項已核准的計畫。如果有多項計畫獲得核准，AlloyDB 會選取並執行預估費用最低的計畫。

事前準備

  • 將 google_plan_management.enabled 資料庫旗標設為 on。詳情請參閱「設定資料庫旗標」。
  • 在資料庫上建立 google_plan_management 擴充功能。詳情請參閱「啟用擴充功能」。

  • 將 google_plan_management_role 授予想使用查詢計畫管理功能及管理查詢計畫的資料庫使用者。

    1. 前往 Google Cloud 控制台的 AlloyDB「Clusters」(叢集) 頁面。

      前往「Clusters」(叢集) 頁面

    2. 按一下所需執行個體。

    3. 依序點選「AlloyDB Studio」和「編輯器 1」分頁標籤。

    4. 輸入下列查詢：

      GRANT google_plan_management_role TO DATABASE_USER;

      將 DATABASE_USER 替換為要授予角色的使用者。

    5. 按一下「執行」。

查看追蹤的查詢計畫

查詢計畫管理功能提供查詢計畫檢視畫面，可顯示所有追蹤的查詢計畫、執行時間和其他資訊。追蹤的查詢計畫是由最佳化工具產生，並儲存在計畫存放區中的查詢計畫。

如要查看歷來追蹤的方案，請執行下列查詢：

SELECT * FROM google_plan_management.tracked_plans_view;

查詢回應大致如下：

db_id                | 5
db_name              | postgres
user_id              | 16392
user_name            | postgres
logical_query_id     | 15480571796188147798
plan_id              | 4740866759615354783
query                | SELECT c1, c2, c3 FROM t1 WHERE c1 = 1;
plan                 | Seq Scan on public.t1                                                                                                                                                            +
                     |   Output: c1, c2, c3                                                                                                                                                             +
                     |   Filter: (t1.c1 = ?)
total_execution_time | 0.003937501
num_executions       | 1
creation_time        | 2024-11-06 16:52:25.200737+00
last_used_time       | 2024-11-06 16:52:25.200737+00

停用查詢計畫追蹤

如果不希望查詢計畫管理追蹤最佳化工具產生的查詢計畫，請務必停用 google_plan_management.enable_track_plans 資料庫旗標。這項旗標預設為啟用，建議保持開啟。詳情請參閱「設定資料庫旗標」。

查看受管理的方案

您可以查看由查詢計畫管理功能管理的所有查詢和計畫，包括已核准和遭拒的計畫。

如要查看代管方案，請執行下列查詢：

SELECT * FROM google_plan_management.managed_plans_view;

查詢回應大致如下：

db_id            | 5
db_name          | postgres
user_id          | 16392
user_name        | postgres
logical_query_id | 15480571796188147798
plan_id          | 4740866759615354783
status           | approved

核准企劃書

最佳化工具會動態選取查詢計畫，也就是說，在不同時間，可能會為同一查詢選擇不同的查詢計畫。如要強制執行一致的計畫選取，您可以使用查詢計畫管理，核准特定查詢的一或多項查詢計畫。

如果您核准多項計畫，查詢計畫管理功能會比較所有核准的計畫，並選取最符合成本效益的計畫來執行查詢。

如要評估及核准查詢計畫，請按照下列步驟操作：

  1. 查看最佳化工具產生的追蹤計畫，並找出回應中的 logical_query_id。

  2. 查看為logical_query_id產生的所有企劃書。您可以使用 total_execution_time 和 num_executions 值計算每個計畫的平均執行時間，然後決定最適合查詢的計畫。

    plan 欄包含其他詳細資料，例如使用的索引或排序方法，可協助您決定查詢計畫。

  3. 執行下列查詢，核准要套用至查詢的計畫：

      SELECT google_plan_management.approve_plan(QUERY_ID, PLAN_ID);

    更改下列內容：

    • QUERY_ID：查詢的專屬 logical_query_id。每個查詢 ID 可能與多個方案 ID 相關聯。
    • PLAN_ID：查詢的專屬 plan_id。

拒絕企劃書

您可以拒絕任何已核准的查詢計畫，並停止查詢計畫管理功能將該計畫套用至查詢。遭拒的計畫不會刪除，而是會顯示在追蹤計畫清單中。

如要拒絕已核准的企劃書，請執行下列查詢：

    SELECT google_plan_management.deny_plan(QUERY_ID, PLAN_ID);

刪除已核准的企劃書

您可以從方案存放區刪除已核准的方案。刪除已核准的企劃書後，該企劃書就不會再顯示在受管理企劃書清單中。

如要刪除已核准的企劃書，請執行下列查詢：

    SELECT google_plan_management.update_plan_status(QUERY_ID, PLAN_ID, 'delete');

暫時停用已核准的企劃書

如要暫時停止使用已核准的查詢計畫，請停用 google_plan_management.enable_steer_plans 資料庫旗標。這個標記預設為啟用。詳情請參閱「設定資料庫旗標」。

限制

  • 您無法在分區資料表或分組集使用查詢計畫管理功能。
  • 查詢計畫管理功能僅適用於主要執行個體。
  • 查詢計畫存放區最多可儲存 10 萬個不重複的計畫，且不提供保留政策。

後續步驟