ApsaraDB RDS:pg_hint_plan拡張機能を使用したクエリプランのカスタマイズ

基本的な使用方法

ヒントは、スラッシュ、アスタリスク、プラス記号 (/* +) で始まり、アスタリスクとスラッシュ (*/) で終わります。 ヒントは、ヒント名とパラメータで構成されます。 ヒント名はパラメータの前にあります。 パラメータは括弧 () で囲まれ、スペースで区切られています。 読みやすくするために、各ヒントを改行で区切ることができます。

例：

この例では、HashJoinヒントは、pgbench_accountsテーブルをスキャンするためにSeqScanメソッドを使用することを指定します。

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM pgbench_branches b
    JOIN pgbench_accounts a ON b.bid = a.bid
   ORDER BY a.aid;

次の応答が返されます。

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

ヒントテーブル

ヒントを使用して、SQL文の実行計画を最適化できます。 ただし、これはSQL文が編集可能な場合にのみ便利です。 SQL文を編集できない場合は、hint_plan.hintsという名前のテーブルにヒントを配置できます。 hint_plan.hintsテーブルには、次の表に示す列が含まれています。

例：

INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
    VALUES (
        'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
        '',
        'SeqScan(t1)'
    );
INSERT 0 1
postgres=# UPDATE hint_plan.hints
postgres-#    SET hints = 'IndexScan(t1)'
postgres-#  WHERE id = 1;
UPDATE 1
postgres=# DELETE FROM hint_plan.hints
postgres-#  WHERE id = 1;
DELETE 1

ヒントタイプ

ヒントは、実行計画への影響に基づいて、次のタイプに分類されます。

• スキャン方法のヒント

  このタイプのヒントは、指定されたテーブルをスキャンするために使用されるメソッドを指定します。 指定されたテーブルにエイリアスがある場合、pg_hint_plan拡張機能はエイリアスに基づいてテーブルを識別します。 サポートされているスキャン方法には、SeqScan、IndexScan、およびNoSeqScanがあります。

  スキャンメソッドのヒントは、通常テーブル、継承テーブル、未ログテーブル、一時テーブル、およびシステムテーブルで有効です。 スキャンメソッドのヒントは、外部テーブル、テーブル関数、定数の値が指定されているステートメント、ユニバーサル式、ビュー、およびサブクエリでは無効です。

  例：

  /*+
      SeqScan(t1)
      IndexScan(t2 t2_pkey)
   */
  SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);

• 結合メソッドのヒント

  このタイプのヒントは、指定されたテーブルを結合するために使用されるメソッドを指定します。

  結合メソッドのヒントは、通常のテーブル、継承テーブル、未ログテーブル、一時テーブル、外部テーブル、システムテーブル、テーブル関数、定数の値が指定されているステートメント、および普遍的な式で有効です。 ビューとサブクエリでは、結合メソッドのヒントが無効です。

• 参加注文のヒント

  このタイプのヒントは、2つ以上のテーブルを結合する順序を指定します。 次のいずれかの方法を使用して、結合順序を指定するヒントを指定できます。

  • 各結合レベルで方向を制限することなく、指定したテーブルを結合する順序を指定します。

  • 指定したテーブルを結合する順序と、各結合レベルでの方向を指定します。

  例：

  /*+
      NestLoop(t1 t2)
      MergeJoin(t1 t2 t3)
      Leading(t1 t2 t3)
   */
  SELECT * FROM table1 t1
      JOIN table table2 t2 ON (t1.key = t2.key)
      JOIN table table3 t3 ON (t2.key = t3.key);

• 行番号修正のヒント

  このタイプのヒントは、オプティマイザによって引き起こされる行番号エラーを修正します。

  例：

  /*+ Rows(a b #10) */ SELECT... ;     //Set the row number to 10. 
  /*+ Rows(a b +10) */ SELECT... ;     //Increase the row number by 10. 
  /*+ Rows(a b -10) */ SELECT... ;     //Decrease the row number by 10. 
  /*+ Rows(a b *10) */ SELECT... ;     //Increase the row number by 10 times. 

• 並列実行のヒント

  このタイプのヒントは、SQL文を並列に実行するために使用されるプランを指定します。

  並列実行のヒントは、通常テーブル、継承テーブル、未ログテーブル、およびシステムテーブルで有効です。 並列実行のヒントは、外部テーブル、定数の値が指定されている句、ユニバーサル式、ビュー、およびサブクエリでは無効です。 ビューの内部テーブルは、その実名またはエイリアスに基づいて指定できます。

  次の例は、各テーブルでSQL文を異なる方法で実行する方法を示しています。

  • 例 1：

    explain /*+ Parallel(c1 3 hard) Parallel(c2 5 hard) */
           SELECT c2.a FROM c1 JOIN c2 ON (c1.a = c2.a);

    次の応答が返されます。

                                      QUERY PLAN                                   
    -------------------------------------------------------------------------------
     Hash Join  (cost=2.86..11406.38 rows=101 width=4)
       Hash Cond: (c1.a = c2.a)
       ->  Gather  (cost=0.00..7652.13 rows=1000101 width=4)
             Workers Planned: 3
             ->  Parallel Seq Scan on c1  (cost=0.00..7652.13 rows=322613 width=4)
       ->  Hash  (cost=1.59..1.59 rows=101 width=4)
             ->  Gather  (cost=0.00..1.59 rows=101 width=4)
                   Workers Planned: 5
                   ->  Parallel Seq Scan on c2  (cost=0.00..1.59 rows=59 width=4)

  • 例 2：

    EXPLAIN /*+ Parallel(tl 5 hard) */ SELECT sum(a) FROM tl;

    次の応答が返されます。

                                        QUERY PLAN                                  
    -----------------------------------------------------------------------------------
     Finalize Aggregate  (cost=693.02..693.03 rows=1 width=8)
       ->  Gather  (cost=693.00..693.01 rows=5 width=8)
             Workers Planned: 5
             ->  Partial Aggregate  (cost=693.00..693.01 rows=1 width=8)
                   ->  Parallel Seq Scan on tl  (cost=0.00..643.00 rows=20000 width=4)

• GUCパラメータ設定のヒント

  このタイプのヒントは、GUCパラメーターの値を一時的に変更します。 実行計画のGUCパラメーターの値は、期待どおりの効果を得るのに役立ちます。 ただし、指定されたヒントが他のSQL文の実行プランと競合する場合、これは適用されません。 GUCパラメーターを複数回設定すると、最新の値が有効になります。

  例：

  /*+ Set(random_page_cost 2.0) */
  SELECT * FROM table1 t1 WHERE key = 'value';

次の表に、pg_hint_plan拡張機能でサポートされているすべてのヒントを示します。

詳細については、PostgreSQL Webサイトをご覧ください。