AI を使用して SQL クエリをチューニングする

AlloyDB for PostgreSQL データベースのクエリ パフォーマンスを最適化するには、AI を活用した分析情報を使用して SQL クエリをチューニングします。ai.tune_sql 関数は、大規模言語モデル（LLM）を活用してクエリ実行プランを分析することで、複雑なボトルネックを特定し、最適化のために実行可能な推奨事項を提供します。

ai.tune_sql 関数を呼び出すと、クエリが分析され、次の領域でクエリの分析情報と推奨事項が提供されます。

  • クエリの概要: クエリのパフォーマンスのボトルネックを分析します。
  • クエリの書き換え: 元のクエリを同等でより効率的なクエリに書き換える最適化手法を提案します。
  • インデックスの推奨事項: 式のインデックス、一般化逆インデックス（GIN）インデックス、Scalable Nearest Neighbors（ScaNN）インデックスなどのインデックス タイプを推奨します。
  • 統計情報の収集に関する推奨事項: クエリ実行プランに基づいて、テーブル ANALYZE と複数の列の統計情報の収集を推奨します。
  • Grand Unified Configuration（GUC）フラグのチューニング: 実行プランと、メモリやバッファの使用量やサイズなどのシステム情報を分析して、新しい GUC 値を推奨します。
  • 分析とバキューム: 分析とバキュームの履歴、ライブタプルとデッドタプルの数に基づいて、テーブルで ANALYZE と VACUUM を実行することをおすすめします。

制限事項

SQL クエリのチューニングには次の制限があります。

  • 準備済みステートメントとパラメータ化されたステートメント: 準備済みステートメントとパラメータ化されたステートメントの場合、ai.tune_sql はクエリの書き換えに関する推奨事項を提供しませんが、他のチューニングに関する推奨事項は提供します。パラメータ プレースホルダ（Query Insights からコピーされた $1 や $2、pg_stat_statements など）を含むクエリのクエリ書き換えの推奨事項を受け取るには、ai.tune_sql を実行する前に、プレースホルダを代表的なリテラル値に置き換えます。
  • explain_analyze が TRUE の場合の読み取り専用 SELECT ステートメントのみ: explain_analyze が TRUE に設定されている場合、単一の読み取り専用 SELECT ステートメントのみがサポートされます。explain_analyze を TRUE に設定して ai.tune_sql を他のステートメント（INSERT、UPDATE、DELETE、SELECT INTO、SELECT FOR UPDATE など）で実行すると、エラー メッセージが返されます。

始める前に

以下があることを確認します。

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 関数で推奨事項が生成されるまでに 2 ～ 4 分かかることがあります。

次の例は、アンチ結合クエリの 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)

次のステップ