このページでは、AlloyDB for PostgreSQL でクエリプラン パッチを作成して管理する方法について説明します。
クエリプラン パッチは、クエリと、クエリプランの詳細を指定できる一連のヒントとの関連付けです。ヒントは、クエリに対して優先される最終実行プランに関する追加情報を指定します。たとえば、クエリでテーブルをスキャンする場合は、他のタイプのスキャン（シーケンシャル スキャンなど）の代わりにインデックス スキャンを使用します。
ヒントの仕様内で最終的なプランの選択を制限するために、クエリ プランナーは実行プランの生成中に、まずヒントをクエリに適用します。ヒントは、その後クエリが発行されるたびに自動的に適用されます。このアプローチでは、プランナーからさまざまなクエリプランを強制的に適用できます。たとえば、ヒントを使用して特定のテーブルでインデックス スキャンを強制することや、複数のテーブル間で特定の結合順序を強制することが可能です。
AlloyDB クエリプラン パッチは、オープンソースの pg_hint_plan 拡張機能のすべてのヒントをサポートしています。
また、AlloyDB はカラム型エンジンに対して次のヒントをサポートしています。
ColumnarScan(table): テーブルでカラム型スキャンを強制します。NoColumnarScan(table): テーブルでカラム型スキャンを無効にします。
AlloyDB では、パラメータ化されたクエリとパラメータ化されていないクエリの両方に対してプランパッチを作成できます。このページでは、パラメータ化されていないクエリを「パラメータ センシティブ クエリ」と呼びます。
ワークフロー
クエリプラン パッチを使用する手順は次のとおりです。
- プランパッチを作成するクエリを特定します。
- クエリが次回実行されるときに適用されるヒントを含むプランパッチを作成します。
- プランパッチの適用を確認します。
このページでは、次のテーブルとインデックスを例として使用します。
CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);
DROP EXTENSION IF EXISTS google_auto_hints;
以前のバージョンで作成したクエリプラン パッチを引き続き使用するには、このページの手順に沿って再作成します。
始める前に
インスタンスでクエリプラン パッチ機能を有効にします。
alloydb.enable_query_plan_patchフラグをonに設定します。このフラグは、サーバー全体レベルまたはセッション レベルで有効にできます。この機能の使用によって発生する可能性があるオーバーヘッドを最小限に抑えるため、このフラグはセッション レベルでのみ有効にしてください。詳細については、インスタンスのデータベース フラグを構成するをご覧ください。
フラグが有効になっていることを確認するには、
show alloydb.enable_query_plan_patch;コマンドを実行します。フラグが有効になっている場合、出力は「on」を返します。クエリプラン パッチを使用するデータベースごとに、AlloyDB プライマリ インスタンスから
alloydbsuperuserユーザーまたはpostgresユーザーとしてデータベースに拡張機能を作成します。CREATE EXTENSION google_auto_hints CASCADE;
必要なロール
クエリプラン パッチの作成と管理に必要な権限を取得するには、次の Identity and Access Management（IAM）ロールを付与するよう管理者に依頼してください。
alloydbsuperuserロール
デフォルトの権限では、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_plan_patch() 関数を使用します。この関数では、クエリとデータベース内のヒントの関連付けを作成します。
SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);
次のように置き換えます。
PLAN_PATCH_NAME: プランパッチの名前。これは、データベース内で一意である必要があります。SQL_ID（省略可）: プランパッチを作成するクエリのクエリ ID。クエリ ID またはクエリテキスト（
SQL_TEXTパラメータ）を使用して、プランパッチを作成できます。ただし、AlloyDB はクエリ ID に基づいて正規化されたクエリテキストを自動的に特定するため、クエリ ID を使用してプランパッチを作成することをおすすめします。SQL_TEXT（省略可）: プランパッチを作成するクエリのクエリテキスト。クエリテキストを使用する場合、クエリ内のリテラル値と定数値を除き、テキストが目的のクエリと同じである必要があります。大文字と小文字の違いなどの不一致があると、プランパッチが適用されないことがあります。リテラルと定数を含むクエリプラン パッチを作成する方法については、パラメータ依存のクエリプラン パッチを作成するをご覧ください。
APPLICATION_NAME（省略可）: プランパッチを適用するセッション クライアント アプリケーションの名前。空の文字列を使用すると、クエリを発行するクライアント アプリケーションに関係なく、プランパッチをクエリに適用できます。HINTS: クエリのヒントのスペース区切りリスト。DISABLED（省略可）: BOOL。TRUEの場合、最初に無効としてプランパッチを作成します。
例:
SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);
このクエリでは、my_hint1 という名前のプランパッチを作成します。ヒント IndexScan(t) はプランナーによって適用され、このクエリの例の次回の実行でテーブル t のインデックス スキャンを強制します。
プランパッチを作成したら、次の例に示すように google_query_plan_patch_view を使用して、プランパッチが作成されたかどうかを確認できます。
postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_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_plan_patch(
PLAN_PATCH_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_plan_patch() 関数の SENSITIVE_TO_PARAM パラメータを TRUE に設定します。
SELECT google_create_plan_patch(
PLAN_PATCH_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_query_plan_patch_view 関数を使用します。
postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f
クエリプラン パッチを有効にする
既存のプランパッチを有効にするには、google_enable_plan_patch(PLAN_PATCH_NAME) 関数を使用します。デフォルトでは、プランパッチは作成時に有効になります。
たとえば、データベースから以前に無効にしたプランパッチ my_hint1 を再度有効にするには、次の関数を実行します。
SELECT google_enable_plan_patch('my_hint1');
クエリプラン パッチを無効にする
既存のプランパッチを無効にするには、google_disable_plan_patch(PLAN_PATCH_NAME) 関数を使用します。
たとえば、データベースからプランパッチの例 my_hint1 を削除するには、次の関数を実行します。
SELECT google_disable_plan_patch('my_hint1');
クエリプラン パッチを削除する
プランパッチを削除するには、google_delete_plan_patch(PLAN_PATCH_NAME) 関数を使用します。
たとえば、データベースからプランパッチの例 my_hint1 を削除するには、次の関数を実行します。
SELECT google_delete_plan_patch('my_hint1');
クエリプラン パッチ機能を無効にする
インスタンスでクエリプラン パッチ機能を無効にするには、alloydb.enable_query_plan_patch フラグを off に設定します。詳細については、インスタンスのデータベース フラグを構成するをご覧ください。
制限事項
クエリプラン パッチの使用には次の制限があります。
- クエリ ID を使用してクエリプラン パッチを作成する場合、元のクエリテキストの長さが 2,048 文字に制限されます。
- 複雑なクエリのセマンティクスによっては、すべてのヒントとその組み合わせを完全に適用できるわけではありません。クエリプラン パッチを本番環境にデプロイする前に、クエリに対して目的のヒントをテストすることをおすすめします。
- 複雑なクエリの結合順序の強制は制限されています。
クエリプラン パッチを使用してプランの選択に影響を与えると、今後の AlloyDB オプティマイザーの改善に影響する可能性があります。次のイベントが発生した場合は、クエリプラン パッチの使用の選択を再検討し、それに応じてパッチを調整してください。
- ワークロードに大きな変化がある。
- オプティマイザーの変更と改善を含む新しい AlloyDB のロールアウトまたはアップグレードが利用できる。
- 他のクエリ チューニング方法が同じクエリに適用される。
- クエリプラン パッチを使用すると、システム パフォーマンスに大きなオーバーヘッドが発生する。
制限事項の詳細については、pg_hint_plan のドキュメントをご覧ください。