実行計画の維持 - Amazon Aurora

実行計画の維持

クエリ計画管理は、実行計画を追加、維持、および改善するための手法と機能を提供します。

計画パフォーマンスの評価

オプティマイザが計画を未承認として取得した後、apg_plan_mgmt.evolve_plan_baselines 関数を使用して、実際のパフォーマンスに基づいて計画を比較します。パフォーマンステストの結果に応じて、計画のステータスを未承認から承認済みまたは拒否に変更できます。要件に合わない場合は、代わりに apg_plan_mgmt.evolve_plan_baselines 関数を使用して計画を一時的に無効にすることができます。

より優れた計画の承認

以下の例は、apg_plan_mgmt.evolve_plan_baselines 関数を使用して、管理計画のステータスを承認済みに変更する方法を示しています。

SELECT apg_plan_mgmt.evolve_plan_baselines ( sql_hash, plan_hash, min_speedup_factor := 1.0, action := 'approve' ) FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved';
NOTICE: rangequery (1,10000) NOTICE: Baseline [ Planning time 0.761 ms, Execution time 13.261 ms] NOTICE: Baseline+1 [ Planning time 0.204 ms, Execution time 8.956 ms] NOTICE: Total time benefit: 4.862 ms, Execution time benefit: 4.305 ms NOTICE: Unapproved -> Approved evolve_plan_baselines ----------------------- 0 (1 row)

出力は、1 と 10,000 のパラメータのバインディングを持つ rangequery ステートメントのパフォーマンス報告を示しています。新しい未承認の計画 (Baseline+1) は、以前に承認された最も良い計画 (Baseline) よりも優れています。新しい計画が Approved になったことを確認するには、apg_plan_mgmt.dba_plans ビューをチェックします。

SELECT sql_hash, plan_hash, status, enabled, stmt_name FROM apg_plan_mgmt.dba_plans;
sql_hash | plan_hash | status | enabled | stmt_name ------------+-----------+----------+---------+------------ 1984047223 | 512153379 | Approved | t | rangequery 1984047223 | 512284451 | Approved | t | rangequery (2 rows)

管理計画には、ステートメントの計画ベースラインである 2 つの承認済み計画が含まれるようになりました。apg_plan_mgmt.set_plan_status 関数を呼び出して、計画のステータスフィールドを直接 'Approved''Rejected''Unapproved'、または 'Preferred' に設定することもできます。

低速な計画の拒否または無効化

計画を拒否または無効化するには、'reject' または 'disable' をアクションパラメータとして apg_plan_mgmt.evolve_plan_baselines 関数に渡します。この例では、ステートメントの最適な Unapproved 計画と比較して 10% 以上低速なキャプチャ済み Approved 計画を無効にします。

SELECT apg_plan_mgmt.evolve_plan_baselines( sql_hash, -- The managed statement ID plan_hash, -- The plan ID 1.1, -- number of times faster the plan must be 'disable' -- The action to take. This sets the enabled field to false. ) FROM apg_plan_mgmt.dba_plans WHERE status = 'Unapproved' AND -- plan is Unapproved origin = 'Automatic'; -- plan was auto-captured

直接、計画を拒否または無効に設定することもできます。計画の有効フィールドを直接 true または false に設定するには、apg_plan_mgmt.set_plan_enabled 関数を呼び出します。計画のステータスフィールドを直接 'Approved''Rejected''Unapproved'、または 'Preferred' に設定するには、apg_plan_mgmt.set_plan_status 関数を呼び出します。

計画の検証

利用不可能な計画を削除、または無効にするには、apg_plan_mgmt.validate_plans 関数を使用します。

管理計画が依存するオブジェクト (インデックスやテーブルなど) が削除されると、計画が利用不可能、または停滞となる可能性があります。ただし、削除されたオブジェクトが再作成されれば、計画が利用不可能なのは一時的です。利用不可能な計画が後で利用可能になる可能性がある場合は、利用不可能な計画を無効にするか、または削除せずに何もしないでいることができます。

利用不可能で、かつ過去 1 週間に使用されていないすべての計画を検索して削除するには、apg_plan_mgmt.validate_plans 関数を以下のように使用します。

SELECT apg_plan_mgmt.validate_plans(sql_hash, plan_hash, 'delete') FROM apg_plan_mgmt.dba_plans WHERE last_used < (current_date - interval '7 days');

計画を直接有効、または無効にするには、apg_plan_mgmt.set_plan_enabled 関数を使用します。

pg_hint_plan を使用した計画の修正

クエリオプティマイザは、すべてのステートメントに対して最適な計画を見つけるように設計されています。ほとんどの場合、オプティマイザは優れた計画を見つけます。ただし、オプティマイザが生成する計画よりもはるかに優れた計画が存在することがあります。オプティマイザに理想的な計画を生成させるために推奨される 2 つの方法は、pg_hint_plan エクステンションを使用すること、または PostgreSQL で Grand Unified Configuration (GUC) 可変を設定することです。

  • pg_hint_plan エクステンション - PostgreSQL の pg_hint_plan エクステンションを使用して、プランナーの動作を変更するための「ヒント」を指定します。pg_hint_plan エクステンションのインストールおよび使用方法の詳細については、「pg_hint_plan ドキュメント」を参照してください。

  • GUC 可変 - 1 つ以上のコストモデルパラメータ、または from_collapse_limitGEQO_threshold などの他のオプティマイザパラメータを上書きします。

これらの手法のいずれかを使用してクエリオプティマイザに計画の使用を強制する場合、クエリ計画管理を使用して、新しい計画を取得した後で強制的に使用することもできます。

pg_hint_plan エクステンションを使用して、SQL ステートメントの結合の順序、結合メソッド、またはアクセスパスを変更することができます。オプティマイザによる計画の作成方法を変更するには、特別な pg_hint_plan 構文を持つ SQL コメントを使用します。例えば、問題のある SQL ステートメントに双方向の結合があるとします。

SELECT * FROM t1, t2 WHERE t1.id = t2.id;

次に、オプティマイザが結合順序 (t1、t2) を選択したとします。しかし、ユーザーは結合順序 (t2、t1) の方が速いことを知っています。以下のヒントは、より高速な結合順序 (t2、t1) を使用するようオプティマイザに強制します。オプティマイザが SQL ステートメントの計画を生成しても、そのステートメントを実行しないように、EXPLAIN を含めます。(出力は表示されていません。)

/*+ Leading ((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

以下のステップは、pg_hint_plan の使用方法を示しています。

オプティマイザの生成した計画を変更し、pg_hint_plan を使用して計画を取得するには

  1. 手動取り込みモードをオンにします。

    SET apg_plan_mgmt.capture_plan_baselines = manual;
  2. 目的の SQL ステートメントのヒントを指定してください。

    /*+ Leading ((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

    これが実行された後、オプティマイザは apg_plan_mgmt.dba_plans ビューで計画をキャプチャします。クエリ計画管理は先頭のコメントを削除することでステートメントを正規化するため、キャプチャされた計画には特別な pg_hint_plan コメント構文は含まれません。

  3. apg_plan_mgmt.dba_plans ビューを使用して管理計画を表示します。

    SELECT sql_hash, plan_hash, status, sql_text, plan_outline FROM apg_plan_mgmt.dba_plans;
  4. 計画のステータスを Preferred に設定します。 これにより、最小コスト計画がまだ ApprovedPreferred になっていない場合、オプティマイザは一連の承認済み計画から選択せずに、その計画を実行することを選択します。

    SELECT apg_plan_mgmt.set_plan_status(sql-hash, plan-hash, 'preferred' );
  5. 手動計画取り込みをオフにして、管理計画の使用を強制します。

    SET apg_plan_mgmt.capture_plan_baselines = false; SET apg_plan_mgmt.use_plan_baselines = true;

    これで、元の SQL ステートメントが実行されると、オプティマイザは Approved 計画または Preferred 計画のいずれかを選択します。最小コスト計画が ApprovedPreferred でない場合、オプティマイザは Preferred 計画を選択します。

計画の削除

長期間使用されていない、または関連性がなくなった計画を削除します。各計画には last_used の日付があり、オプティマイザは計画を実行するたびに更新するか、ステートメントの最小コスト計画として選択します。last_used の日付を使用して、計画が最近使用され、まだ関連性があるかどうかを判断します。

例えば、以下のように apg_plan_mgmt.delete_plan 関数を使用することができます。これにより、最小コスト計画として選択されなかった、または少なくとも 31 日以内に実行されなかった計画はすべて削除されます。ただし、この例では明示的に拒否された計画は削除されません。

SELECT SUM(apg_plan_mgmt.delete_plan(sql_hash, plan_hash)) FROM apg_plan_mgmt.dba_plans WHERE last_used < (current_date - interval '31 days') AND status <> 'Rejected';

既に有効でなく、今後も有効にしない計画を削除するには、apg_plan_mgmt.validate_plans 関数を使用します。詳細については、「計画の検証」を参照してください。

計画を削除するための独自のポリシーを実装できます。現在の日付 last_usedapg_plan_mgmt.plan_retention_period パラメータ の値 (デフォルトでは 32 日) より大きい場合、計画は自動的に削除されます。もっと長い間隔を指定することや、独自の保持計画ポリシーを delete_plan 関数で直接呼び出すことによって実装することもできます。last_used の日付は、オプティマイザが最小コスト計画としてプランを選択したか、プランが実行された最新の日付です。

重要

計画をクリーンアップしないと、クエリ計画管理用に確保されている共有メモリが不足する可能性があります。マネージド計画に使用可能なメモリー量を制御するには、apg_plan_mgmt.max_plans パラメータを使用します。DB インスタンスレベルのパラメータグループでこのパラメータを設定し、変更を有効にするために DB インスタンスを再起動します。詳細については、apg_plan_mgmt.max_plans パラメータを参照してください。

計画のエクスポートおよびインポート

管理計画をエクスポートしたり、別の DB インスタンスにインポートできます。

管理計画をエクスポートする

承認されたユーザーは apg_plan_mgmt.plans テーブルの任意のサブセットを別のテーブルにコピーしてから、pg_dump コマンドを使用してそれを保存することができます。次に例を示します。

CREATE TABLE plans_copy AS SELECT * FROM apg_plan_mgmt.plans [ WHERE predicates ] ;
% pg_dump --table apg_plan_mgmt.plans_copy -Ft mysourcedatabase > plans_copy.tar
DROP TABLE apg_plan_mgmt.plans_copy;

管理計画をインポートする

  1. エクスポートした管理計画の .tar ファイルを、計画を復元する予定のシステムにコピーします。

  2. tar ファイルを新しいテーブルにコピーするには、pg_restore コマンドを使用します。

    % pg_restore --dbname mytargetdatabase -Ft plans_copy.tar
  3. 次の例に示すように、plans_copy テーブルを apg_plan_mgmt.plans テーブルとマージします。

    注記

    場合によっては、あるバージョンの apg_plan_mgmt エクステンションからダンプして別のバージョンに復元することがあります。このような場合は、計画テーブルの列が異なる可能性があります。その場合は、SELECT * を使用せず、列に明示的に名前を付けてください。

    INSERT INTO apg_plan_mgmt.plans SELECT * FROM plans_copy ON CONFLICT ON CONSTRAINT plans_pkey DO UPDATE SET status = EXCLUDED.status, enabled = EXCLUDED.enabled, -- Save the most recent last_used date -- last_used = CASE WHEN EXCLUDED.last_used > plans.last_used THEN EXCLUDED.last_used ELSE plans.last_used END, -- Save statistics gathered by evolve_plan_baselines, if it ran: -- estimated_startup_cost = EXCLUDED.estimated_startup_cost, estimated_total_cost = EXCLUDED.estimated_total_cost, planning_time_ms = EXCLUDED.planning_time_ms, execution_time_ms = EXCLUDED.execution_time_ms, total_time_benefit_ms = EXCLUDED.total_time_benefit_ms, execution_time_benefit_ms = EXCLUDED.execution_time_benefit_ms;
  4. 管理計画を共有メモリにリロードし、一時的な計画テーブルを削除します。

    SELECT apg_plan_mgmt.reload(); -- refresh shared memory DROP TABLE plans_copy;