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 関数を使用します。この関数により、無効な計画を削除または無効にすることができます。詳細については、「計画の検証」を参照してください。

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 計画を選択します。