名前付きヒントを作成、管理する

このページでは、AlloyDB for PostgreSQL で名前付きヒントを作成して管理する方法について説明します。

名前付きヒントは、クエリと、クエリプランの詳細を指定できる一連のヒントとの関連付けです。ヒントは、クエリに対して優先される最終実行プランに関する追加情報を指定します。たとえば、クエリでテーブルをスキャンする場合は、他のタイプのスキャン（シーケンシャル スキャンなど）の代わりにインデックス スキャンを使用します。

ヒントの仕様内で最終的なプランの選択を制限するために、クエリ プランナーは実行プランの生成中に、まずヒントをクエリに適用します。ヒントは、その後クエリが発行されるたびに自動的に適用されます。このアプローチでは、プランナーからさまざまなクエリプランを強制的に適用できます。たとえば、ヒントを使用して特定のテーブルでインデックス スキャンを強制することや、複数のテーブル間で特定の結合順序を強制することが可能です。

AlloyDB の名前付きヒントは、オープンソースの pg_hint_plan 拡張機能のすべてのヒントをサポートしています。

また、AlloyDB はカラム型エンジンに対して次のヒントをサポートしています。

  • ColumnarScan(table): テーブルでカラム型スキャンを強制します。
  • NoColumnarScan(table): テーブルでカラム型スキャンを無効にします。

AlloyDB では、パラメータ化されたクエリとパラメータ化されていないクエリの両方に対して名前付きヒントを作成できます。このページでは、パラメータ化されていないクエリを「パラメータ センシティブ クエリ」と呼びます。

ワークフロー

名前付きヒントを使用する手順は次のとおりです。

  1. 名前付きヒントを作成するクエリを特定します。
  2. クエリが次回実行されるときに適用されるヒントを含む名前付きヒントを作成します。
  3. 名前付きヒントの適用を確認します。

このページでは、次のテーブルとインデックスを例として使用します。

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

以前のバージョンで作成した名前付きヒントを引き続き使用するには、このページの手順に沿って再作成します。

始める前に

  • インスタンスで名前付きヒント機能を有効にします。alloydb.enable_named_hints フラグを on に設定します。このフラグは、サーバー全体レベルまたはセッション レベルで有効にできます。この機能の使用によって発生する可能性があるオーバーヘッドを最小限に抑えるため、このフラグはセッション レベルでのみ有効にしてください。

    詳細については、インスタンスのデータベース フラグを構成するをご覧ください。

    フラグが有効になっていることを確認するには、show alloydb.enable_named_hints; コマンドを実行します。フラグが有効になっている場合、出力は「on」を返します。

  • 名前付きヒントを使用するデータベースごとに、AlloyDB プライマリ インスタンスから alloydbsuperuser ユーザーまたは postgres ユーザーとしてデータベースに拡張機能を作成します。

    CREATE EXTENSION google_auto_hints CASCADE;

必要なロール

名前付きヒントの作成と管理に必要な権限を取得するには、次の Identity and Access Management（IAM）ロールを付与するよう管理者に依頼してください。

デフォルトの権限では、alloydbsuperuser ロールを持つユーザーのみが名前付きヒントを作成できますが、必要に応じて、データベースの他のユーザーまたはロールに書き込み権限を付与して、名前付きヒントを作成できるようにすることもできます。

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

クエリを特定する

クエリ ID を使用して、デフォルト プランの調整が必要なクエリを特定できます。クエリ ID は、クエリが少なくとも 1 回実行された後に使用可能になります。

クエリ ID を特定するには、次の方法を使用します。

  • 次の例のように EXPLAIN (VERBOSE) コマンドを実行します。

    EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

    出力では、クエリ ID は -6875839275481643436 です。

  • pg_stat_statements ビューをクエリします。

    pg_stat_statements 拡張機能を有効にしている場合は、次の例に示すように pg_stat_statements ビューをクエリしてクエリ ID を確認できます。

    select query, queryid from pg_stat_statements;

名前付きヒントを作成する

名前付きヒントを作成するには、google_create_named_hints() 関数を使用します。この関数では、クエリとデータベース内のヒントの関連付けを作成します。

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

次のように置き換えます。

  • HINTS_NAME: 名前付きヒントの名前。これは、データベース内で一意である必要があります。

  • SQL_ID（省略可）: 名前付きヒントを作成するクエリのクエリ ID。

    クエリ ID またはクエリテキスト（SQL_TEXT パラメータ）を使用して、名前付きヒントを作成できます。ただし、AlloyDB はクエリ ID に基づいて正規化されたクエリテキストを自動的に特定するため、クエリ ID を使用して名前付きヒントを作成することをおすすめします。

  • SQL_TEXT（省略可）: 名前付きヒントを作成するクエリのクエリテキスト。

    クエリテキストを使用する場合、クエリ内のリテラル値と定数値を除き、テキストが目的のクエリと同じである必要があります。大文字と小文字の違いなどの不一致があると、名前付きヒントが適用されないことがあります。リテラルと定数を含むクエリの名前付きヒントを作成する方法については、パラメータ依存の名前付きヒントを作成するをご覧ください。

  • APPLICATION_NAME（省略可）: 名前付きヒントを使用するセッション クライアント アプリケーションの名前。空の文字列を使用すると、クエリを発行するクライアント アプリケーションに関係なく、名前付きヒントをクエリに適用できます。

  • HINTS: クエリのヒントのスペース区切りリスト。

  • DISABLED（省略可）: BOOL。TRUE の場合、最初に無効として名前付きヒントを作成します。

例:

SELECT google_create_named_hints(
HINTS_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

このクエリでは、my_hint1 という名前の命名ヒントを作成します。ヒント IndexScan(t) はプランナーによって適用され、このクエリの例の次回の実行でテーブル t のインデックス スキャンを強制します。

名前付きヒントを作成したら、次の例に示すように google_named_hints_view を使用して、名前付きヒントが作成されたかどうかを確認できます。

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

プライマリ インスタンスで名前付きヒントが作成された後、読み取りプール インスタンスで名前付きヒント機能も有効になっている場合、読み取りプール インスタンスの関連するクエリに自動的に適用されます。

パラメータ依存の名前付きヒントを作成する

デフォルトでは、クエリに名前付きヒントが作成されると、関連付けられたクエリテキスト内のリテラル値と定数値がパラメータ マーカー（? など）に置き換えられ、クエリテキストが正規化されます。名前付きヒントは、パラメータ マーカーの値が異なる場合でも、正規化されたクエリに使用されます。

たとえば、次のクエリを実行すると、SELECT * FROM t WHERE a = 99; などの別のクエリで、デフォルトで名前付きヒント my_hint2 を使用できるようになります。

SELECT google_create_named_hints(
  HINTS_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

これにより、SELECT * FROM t WHERE a = 99; などのクエリは、デフォルトで名前付きヒント my_hint2 を使用できます。

AlloyDB では、パラメータ化されていないクエリテキストの名前付きヒントを作成することもできます。この場合、クエリの照合時にクエリテキスト内の各リテラル値と定数値が重要になります。

パラメータ センシティブな名前付きヒントを適用すると、対応するリテラル値または定数値のみが異なる 2 つのクエリが、別々のクエリと見なされます。両方のクエリプランを強制適用する場合は、クエリごとに個別の名前付きヒントを作成する必要があります。ただし、2 つの名前付きヒントに異なるヒントを使用できます。

パラメータ センシティブ名前付きヒントを作成するには、次の例に示すように、google_create_named_hints() 関数の SENSITIVE_TO_PARAM パラメータを TRUE に設定します。

SELECT google_create_named_hints(
HINTS_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

リテラル値「99」が「88」と一致しないため、クエリ SELECT * FROM t WHERE a = 99; は名前付きヒント my_hint3 を使用できません。

パラメータ センシティブな名前付きヒントを使用する場合は、次の点を考慮してください。

  • パラメータ センシティブな名前付きヒントでは、クエリテキスト内のリテラル値、定数値、パラメータ マーカーの混在がサポートされていません。
  • 同じクエリに対してパラメータ センシティブ名前付きヒントとデフォルトの名前付きヒントを作成すると、デフォルトの名前付きヒントよりもパラメータ センシティブ名前付きヒントが優先されます。
  • クエリ ID を使用してパラメータ センシティブな名前付きヒントを作成する場合は、現在のセッションでクエリが実行されていることを確認してください。最新の実行（現在のセッション）のパラメータ値を使用して、名前付きヒントが作成されます。

名前付きヒントの適用を確認する

名前付きヒントを作成したら、次の方法でクエリプランが強制的に適用されていることを確認します。

  • EXPLAIN コマンドまたは EXPLAIN (ANALYZE) コマンドを使用します。

    プランナーが適用しようとしているヒントを表示するには、EXPLAIN コマンドを実行する前に、セッション レベルで次のフラグを設定します。

    SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;

  • auto_explain 拡張機能を使用します。

名前付きヒントを管理する

AlloyDB では、名前付きヒントの表示、有効化と無効化、削除を行えます。

名前付きヒントを表示する

既存の名前付きヒントを表示するには、次の例に示すように google_named_hints_view 関数を使用します。

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

名前付きヒントを有効にする

既存の名前付きヒントを有効にするには、google_enable_named_hints(HINTS_NAME) 関数を使用します。デフォルトでは、名前付きヒントは作成時に有効になります。

たとえば、データベースから以前に無効にした名前付きヒント my_hint1 を再度有効にするには、次の関数を実行します。

SELECT google_enable_named_hints('my_hint1');

名前付きヒントを無効にする

既存の名前付きヒントを無効にするには、google_disable_named_hints(HINTS_NAME) 関数を使用します。

たとえば、データベースから my_hint1 という名前のヒントの例を削除するには、次の関数を実行します。

SELECT google_disable_named_hints('my_hint1');

名前付きヒントを削除する

名前付きヒントを削除するには、google_delete_named_hints(HINTS_NAME) 関数を使用します。

たとえば、データベースから my_hint1 という名前のヒントの例を削除するには、次の関数を実行します。

SELECT google_delete_named_hints('my_hint1');

名前付きヒント機能を無効にする

インスタンスで名前付きヒント機能を無効にするには、alloydb.enable_named_hints フラグを off に設定します。詳細については、インスタンスのデータベース フラグを構成するをご覧ください。

制限事項

名前付きヒントの使用には次の制限があります。

  • クエリ ID を使用して名前付きヒントを作成する場合、元のクエリテキストの長さが 2, 048 文字に制限されます。
  • 複雑なクエリのセマンティクスによっては、すべてのヒントとその組み合わせを完全に適用できるわけではありません。名前付きヒントを本番環境にデプロイする前に、クエリに対して目的のヒントをテストすることをおすすめします。
  • 複雑なクエリの結合順序の強制は制限されています。

  • 名前付きヒントを使用してプランの選択に影響を与えると、今後の AlloyDB オプティマイザーの改善に影響する可能性があります。次のイベントが発生した場合は、名前付きヒントの使用の選択を再検討し、それに応じて名前付きヒントを調整してください。

    • ワークロードに大きな変化がある。
    • オプティマイザーの変更と改善を含む新しい AlloyDB のロールアウトまたはアップグレードが利用できる。
    • 他のクエリ チューニング方法が同じクエリに適用される。
    • 名前付きヒントを使用すると、システム パフォーマンスに大きなオーバーヘッドが発生します。

制限事項の詳細については、pg_hint_plan のドキュメントをご覧ください。

次のステップ