このページでは、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 のドキュメントをご覧ください。