使用 AI 調整 SQL 查詢

如要最佳化 AlloyDB for PostgreSQL 資料庫查詢效能，您可以使用 AI 輔助洞察資料調整 SQL 查詢。ai.tune_sql 函式採用大型語言模型 (LLM)，可分析查詢執行計畫，找出複雜的瓶頸，並提供可行的最佳化建議。

叫用 ai.tune_sql 函式時，函式會分析查詢，並提供下列領域的查詢洞察和建議：

  • 查詢摘要：分析查詢效能瓶頸。
  • 查詢重寫：建議最佳化技術，將原始查詢重寫為等效且更有效率的查詢。
  • 索引建議：建議索引類型，例如運算式索引、一般化反向索引 (GIN) 和可擴充最鄰近項目 (ScaNN) 索引。
  • 統計資料收集建議：根據查詢執行計畫，建議ANALYZE資料表和多個資料欄的統計資料收集。
  • Grand Unified Configuration (GUC) 旗標調整：分析執行計畫和系統資訊 (例如記憶體和緩衝區用量和大小)，建議新的 GUC 值。
  • 分析和清理：根據分析和清理記錄，以及有效和無效元組數量，建議對資料表執行 ANALYZE 和 VACUUM。

限制

SQL 查詢調整功能有下列限制：

  • 預先準備和參數化陳述式：對於預先準備和參數化陳述式，ai.tune_sql 不會提供查詢重寫建議，但仍會提供其他調整建議。如要針對含有參數預留位置 (例如從查詢洞察或 pg_stat_statements 複製的 $1 或 $2) 的查詢取得查詢重寫建議，請先將預留位置替換為代表性常值，再執行 ai.tune_sql。
  • 只有在 explain_analyze 為 TRUE 時，才支援唯讀 SELECT 陳述式：如果 explain_analyze 設為 TRUE，則僅支援單一唯讀 SELECT 陳述式。在其他陳述式 (例如 INSERT、UPDATE、DELETE、SELECT INTO 或 SELECT FOR UPDATE) 上執行 ai.tune_sql，並將 explain_analyze 設為 TRUE，會傳回錯誤訊息。

事前準備

請確認您已備妥下列項目：

啟用 SQL 調整功能

註冊 LLM 並啟用必要的資料庫旗標。

  1. 在執行個體區域註冊全域或區域模型端點，為 ai.tune_sql 函式建立 Gemini 模型端點：

    • 如果模型在執行個體區域中無法使用 (例如 locations/global 上的 gemini-3.5-flash)，請執行下列陳述式，註冊全域模型端點：

      CALL google_ml.create_model(
  model_id => 'gemini-3.5-flash-global',
  model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
  model_qualified_name => 'gemini-3.5-flash',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      將 PROJECT_ID 替換為模型所在專案的 ID。

    • 如要在執行個體區域 (例如 locations/REGION_ID 上的 gemini-2.5-pro) 註冊區域模型端點，請執行下列陳述式：

      CALL google_ml.create_model(
  model_id => 'gemini-2.5-pro',
  model_request_url => 'https://REGION_ID-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/REGION_ID/publishers/google/models/gemini-2.5-pro:generateContent',
  model_qualified_name => 'gemini-2.5-pro',
  model_provider => 'google',
  model_type => 'llm',
  model_auth_type => 'alloydb_service_agent_iam');

      更改下列內容：

      • REGION_ID：AlloyDB 執行個體所在的區域，例如 us-central1。
      • PROJECT_ID：模型所在專案的 ID。

  2. 在目前的資料庫工作階段中，設定 SQL 查詢調整建議的模型 ID。指定您在上一個步驟中註冊的 model_id，例如 'gemini-3.5-flash-global' 或 'gemini-2.5-pro'：

    SET google_db_advisor.llm_model_id = 'gemini-3.5-flash-global';

  3. 設定 google_db_advisor.enable_ai_tune_sql 旗標，為目前的資料庫工作階段啟用 SQL 查詢調整功能：

    SET google_db_advisor.enable_ai_tune_sql = on;

    由於 SET 僅適用於目前的資料庫工作階段，您可以對這兩個旗標執行 ALTER DATABASE DATABASE_NAME SET FLAG_NAME = VALUE;，或設定執行個體的資料庫旗標，在不同工作階段中保留這些設定。

手動調整 SQL 查詢

如要手動調整 SQL 查詢，請使用 SELECT 陳述式執行 ai.tune_sql 函式。

  1. 將 psql 用戶端連線至 AlloyDB 執行個體，或開啟 AlloyDB Studio。

    如果您使用 SET 設定 google_db_advisor.llm_model_id 和 google_db_advisor.enable_ai_tune_sql，請在呼叫 ai.tune_sql 之前，在同一個資料庫工作階段中執行這些 SET 陳述式。

  2. 如要調整 SQL 查詢，請執行 ai.tune_sql 函式。這個函式會擷取與查詢相關的結構定義，並將其與查詢一併傳送至 LLM，以便進行效能分析：

    SELECT ai.tune_sql(query => 'SQL_QUERY', explain_analyze => TRUE);

    將 SQL_QUERY 替換為要分析的 SQL 查詢。

    根據預設，執行參數 (explain_analyze) 會設為 TRUE。設為 TRUE 時，函式會執行 EXPLAIN ANALYZE 來執行查詢並擷取執行計畫。這種做法可提供更準確、實用的查詢調整建議。如果將 explain_analyze 設為 FALSE，函式只會執行 EXPLAIN 來產生計畫，不會執行查詢。

查看查詢計畫和效能建議

輸出內容會先顯示標準查詢計畫，接著顯示 AI 生成的詳細建議。建議包括查詢摘要、建議的重寫內容，以及統計資料收集、GUC 旗標調整和清理建議。ai.tune_sql 函式可能需要兩到四分鐘才能產生建議。

以下範例顯示反聯結查詢的 ai.tune_sql 輸出內容：

SELECT ai.tune_sql(
  'SELECT *
   FROM store_sales
   WHERE ss_customer_sk NOT IN (
     SELECT sr_customer_sk
     FROM store_returns
     WHERE sr_item_sk = ss_item_sk)');

                     tune_sql
--------------------------------------------------
 NOTE
 --------------------------------------------------
 Advices generated with the assistance of an LLM. Independent verification of these advices is required due to the potential for inaccuracies.
 --------------------------------------------------
 ORIGINAL QUERY PLAN
 --------------------------------------------------
 Seq Scan on store_sales  (cost=0.00..111292742.52 rows=1440384 width=115) (actual time=0.092..103093.207 rows=1306312.00 loops=1)
   Filter: (NOT (ANY (ss_customer_sk = (SubPlan 1).col1)))
   Rows Removed by Filter: 1574198
   Buffers: shared hit=72987539
   SubPlan 1
     ->  Index Scan using idx_sr_item_sk on store_returns  (cost=0.42..76.74 rows=18 width=4) (actual time=0.005..0.034 rows=19.38 loops=2880510)
           Index Cond: (sr_item_sk = store_sales.ss_item_sk)
           Index Searches: 2880510
           Buffers: shared hit=72933670
 Planning Time: 0.257 ms
 --------------------------------------------------
 QUERY SUMMARY
 --------------------------------------------------
 The original query plan shows a Sequential Scan on store_sales with a correlated SubPlan for the NOT IN condition. For each of the 1.44 million rows in store_sales, it executes an Index Scan on store_returns, resulting in over 2.88 million index searches. This nested loop execution is the primary bottleneck, taking over 103 seconds. Converting the correlated NOT IN subquery to an anti-join (NOT EXISTS) will allow the optimizer to use a Hash Anti Join, processing both tables in bulk and significantly reducing execution time.
 --------------------------------------------------
 AI SUGGESTED QUERY 1
 --------------------------------------------------
 SELECT * FROM store_sales WHERE NOT EXISTS (SELECT 1 FROM store_returns WHERE store_returns.sr_item_sk = store_sales.ss_item_sk AND (store_returns.sr_customer_sk = store_sales.ss_customer_sk OR store_returns.sr_customer_sk IS NULL OR store_sales.ss_customer_sk IS NULL));
 Reason: The original query uses a NOT IN subquery which is executed as a correlated subquery (SubPlan), leading to millions of index scans. Rewriting to NOT EXISTS allows the optimizer to use a Hash Anti Join, which is much more efficient for large datasets. The OR IS NULL conditions ensure strict semantic equivalence with NOT IN when dealing with NULL values.
 --------------------------------------------------
 REWRITTEN QUERY PLAN
 --------------------------------------------------
 Gather  (cost=8541.96..139088.40 rows=154194 width=115)
   Workers Planned: 2
   ->  Parallel Hash Anti Join  (cost=7541.96..122669.00 rows=64248 width=115)
         Hash Cond: (store_sales.ss_item_sk = store_returns.sr_item_sk)
         Join Filter: ((store_returns.sr_customer_sk = store_sales.ss_customer_sk) OR (store_returns.sr_customer_sk IS NULL) OR (store_sales.ss_customer_sk IS NULL))
         ->  Parallel Seq Scan on store_sales  (cost=0.00..65872.20 rows=1200320 width=115)
         ->  Parallel Hash  (cost=6044.76..6044.76 rows=119776 width=8)
               ->  Parallel Seq Scan on store_returns  (cost=0.00..6044.76 rows=119776 width=8)
 --------------------------------------------------
 AI SUGGESTED INDEX 1
 --------------------------------------------------
 CREATE INDEX idx_sr_item_customer ON store_returns (sr_item_sk, sr_customer_sk);
 Reason: A composite index on (sr_item_sk, sr_customer_sk) allows index-only scans for the subquery or early aggregation, reducing table access and speeding up the anti-join condition evaluation.
 Note: Indexes are suggested solely based on the given query, but they don't consider the overall system workload.
           The suggested indexes improve the performance of the original query.
 --------------------------------------------------
 GUC FLAGS TUNING
 --------------------------------------------------
 SET work_mem = '64MB'; The current work_mem of 4MB is too small for a Hash Anti Join on tables with hundreds of thousands of rows. Increasing it to 64MB allows the hash table for store_returns to fit entirely in memory, preventing costly disk spills during the join.

 Execution Time: 103167.475 ms
(50 rows)

後續步驟