Aurora PostgreSQL 実行計画の管理 - Amazon Aurora
計画パフォーマンスの評価計画の検証pg_hint_plan を使用した計画の修正計画の削除計画のエクスポートおよびインポート

Aurora PostgreSQL 実行計画の管理

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

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

オプティマイザが計画を未承認として取得した後、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_limit や GEQO_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 に設定します｡ これにより、最小コスト計画がまだ Approved や Preferred になっていない場合、オプティマイザは一連の承認済み計画から選択せずに、その計画を実行することを選択します。

    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 計画のいずれかを選択します。最小コスト計画が Approved や Preferred でない場合、オプティマイザは Preferred 計画を選択します。

計画の削除

計画は、1 か月以上、具体的には 32 日間使用されなかった場合、自動的に削除されます。これは、apg_plan_mgmt.plan_retention_period パラメータのデフォルト設定です。計画の保持期間をより長い期間に変更できますが、最小期間は 1 です。計画が最後に使用された日付から、last_used の日付を現在の日付から引いて日数を算出します。last_used の日付は、オプティマイザが最小コスト計画としてプランを選択したか、プランが実行された最新の日付です。計画の日付は apg_plan_mgmt.dba_plans ビューに保存されます。

長期間使用されていない、または有用ではない計画を削除することをお勧めします。すべての計画には last_used の日付があり、オプティマイザは計画を実行するたびに更新するか、ステートメントの最小コスト計画として計画を選択します。最後の last_used の日付を確認して、安全に削除できる計画を確認してください。

次のクエリは、プランの総数、削除に失敗したプラン、削除に成功したプランの数を含む 3 列のテーブルを返します。apg_plan_mgmt.delete_plan 関数を使用して過去 31 日以内に最小コスト計画として選択されておらず、ステータスが Rejected ではないすべての計画を削除する方法の例であるネストしたクエリが入っています。

SELECT (SELECT COUNT(*) from apg_plan_mgmt.dba_plans) total_plans,
       COUNT(*) FILTER (WHERE result = -1) failed_to_delete,
       COUNT(*) FILTER (WHERE result = 0) successfully_deleted
       FROM (
            SELECT apg_plan_mgmt.delete_plan(sql_hash, plan_hash) as result
            FROM apg_plan_mgmt.dba_plans
            WHERE last_used < (current_date - interval '31 days')
            AND status <> 'Rejected'
            ) as dba_plans ;   


 total_plans | failed_to_delete | successfully_deleted
-------------+------------------+----------------------
           3 |                0 |                    2

詳細については、「apg_plan_mgmt.delete_plan」を参照してください。

有効ではなく、無効のままになると見られる計画を削除するには、apg_plan_mgmt.validate_plans 関数を使用します。この関数により、無効な計画を削除または無効にすることができます。詳細については、「計画の検証」を参照してください。

重要

無関係な計画を削除しないと、クエリプラン管理用に確保されている共有メモリが不足する可能性があります。マネージド計画に使用可能なメモリー量を制御するには、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;