pg_hint_plan

このトピックでは、pg_hint_plan拡張機能について説明します。拡張機能を使用して、SQL文にヒントを追加できます。ヒントは、SQL文の実行方法を指定します。これにより、SQL文の実行計画を最適化できます。

背景情報

PolarDB for PostgreSQL (Compatible with Oracle) は、静的ルールではなくデータ統計を利用するコストベースのオプティマイザを使用します。オプティマイザは、SQL文のすべての可能な実行計画のコストを評価し、最も低いコストで実行計画を実行します。オプティマイザは最善の努力をしますが、オプティマイザはデータ間の基礎となる関係を考慮しないため、選択された実行計画は最善の計画ではない可能性があります。

Grand Unified Scheme (GUC) 変数を指定して実行計画を調整できますが、これはセッション全体に影響します。セッション全体に影響を与えたくない場合は、pg_hint_plan拡張機能を使用して、単一の実行プランを最適化できます。

注意事項

データ管理 (DMS) はヒントをサポートしていません。データベースにアクセスするには、他の方法を使用する必要があります。
pg_hint_plan拡張機能は、最初のコメントブロックからのみヒントを読み取ります。
pg_hint_plan拡張機能は、文字、数字、スペース、および特定の特殊文字のみを受け入れ、他の文字を識別するとすぐに解析を停止します。受け入れられた特殊文字には、_、()
pg_hint_plan拡張機能は、オブジェクト名を大文字と小文字を区別して比較します。これは、PostgreSQLがオブジェクト名を比較する方法とは異なります。たとえば、ヒント内のTBLという名前のオブジェクトは、データベース内のTBLのみに一致します。 tblまたはTblという名前のオブジェクトは無視されます。

制限事項

PL/pgSQLを使用して定義されたストアドプロシージャにpg_hint_plan拡張機能を使用するには、次の制限があります。

ヒントは、次のタイプのステートメントに対してのみ有効です。
- SELECT、INSERT、UPDATE、およびDELETEステートメント。
- RETURN QUERYステートメント。
- EXECUTE QUERYステートメント。
- OPENステートメント。
- 声明のために。
ヒントは、SQL文の最初の単語の直後に配置する必要があります。ヒントが最初の単語の前に配置されている場合、ヒントはステートメントの一部とは見なされません。

pg_hint_plan拡張機能の作成と読み込み

エクステンションを作成します。
```
CREATE EXTENSION pg_hint_plan;
```
エクステンションをロードします。
- 1人のユーザーの拡張機能を自動的にロードします。
  - 次のステートメントを実行して、拡張機能をロードします。
```
ALTER USER xxx set session_preload_libraries='pg_hint_plan';
```
    説明
    ステートメント内のxxxを実際のユーザー名に置き換えます。
  - 次のステートメントを実行して、単一のデータベースの拡張機能をロードします。
```
ALTER DATABASE xxx set session_preload_libraries='pg_hint_plan';
```
  説明
  不適切な設定によりアカウントがデータベースにログインできない場合は、次のいずれかのステートメントを実行して、別のアカウントまたはデータベースを介してPolarDBにログインし、設定をリセットします。
  ALTER USER xxx reset session_preload_libraries; ALTER DATABASE xxx reset session_preload_libraries;
- データベースクラスターの拡張機能を自動的に読み込みます。
  pg_hint_plan拡張機能を使用するには、Quota Centerに移動します。 polardb_pg_pg_hint_planに対応する [操作] 列で [適用] をクリックします。
- 拡張機能がロードされているかどうかを確認します。
  1. 次のステートメントを実行して、デバッグ情報をクライアントに送信できるようにします。
```
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
```
  2. 次のステートメントを実行して、拡張機能がロードされているかどうかを確認します。
```
/*+Set(enable_seqscan 1)*/select 1;
```
    拡張がロードされると、次の情報が返されます。
```
NOTICE:  pg_hint_plan: used hint: Set(enable_seqscan 1)
```
  3. 次のステートメントを実行して、クライアントへのデバッグ情報の送信を停止します。
```
RESET pg_hint_plan.debug_print;
RESET pg_hint_plan.message_level;
```

使用上の注意

基本的な使い方

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

例：

この例では、結合方法として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という名前のテーブルにヒントを配置できます。テーブルは次の列で構成されています。

列	説明

列	説明
id	ヒントのID。 IDは一意であり、自動的に生成されます。
norm_query_string	ヒントを追加するSQL文と一致するパターン。 SQL文の定数は、ワイルドカードとして機能する疑問符 (?) で置き換える必要があります。スペース文字はパターンの必要な部分です。
application_name	ヒントが適用されるアプリケーションの名前。このパラメーターが空の場合、ヒントはすべてのアプリケーションに適用されます。
ヒント	ヒントを含むコメント。コメントマークを含める必要はありません。

ヒントテーブルの例を次に示します。デフォルトでは、pg_hint_plan拡張機能を作成するユーザーには、ヒントテーブルに対する権限があります。ステートメントコメントとヒントテーブルの両方にヒントが追加されると、ヒントテーブルのヒントがコメントのヒントよりも優先されます。

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
 UPDATE hint_plan.hints
    SET hints = 'IndexScan(t1)'
  WHERE id = 1;
UPDATE 1
 DELETE FROM hint_plan.hints
  WHERE id = 1;
DELETE 1

ヒントタイプ

ヒントタイプ
ヒントは、実行計画への影響に基づいて、次の6つのタイプに分類されます。
- Hints for scan methods
  このタイプのヒントは、指定されたテーブルをスキャンするために使用されるメソッドを指定します。指定されたテーブルにエイリアスがある場合、pg_hint_plan拡張機能はエイリアスに基づいてテーブルを識別します。サポートされているスキャン方法には、SeqScan、IndexScanなどがあります。
  スキャン方法のヒントは、通常テーブル、継承テーブル、未ログテーブル、一時テーブル、およびシステムテーブルで有効です。ただし、スキャンメソッドのヒントは、外部テーブル、テーブル関数、定数の値が指定されているステートメント、普遍的な式、ビューでは効果的ではありません。とサブクエリ。
  例：
```
/*+
    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);
```
  説明
  上記の例は、次のヒントを示しています。
  NestLoop(t1 t2): t1およびt2テーブルを結合する方法を指定する。
  MergeJoin(t1 t2 t3): t1、t2、およびt3テーブルを結合するための方法を指定する。
  先行 (t1 t2 t3): 3つのテーブルが結合される順序を指定する。
- 行番号修正のヒント
  このタイプのヒントは、オプティマイザの制限によって引き起こされる行番号エラーを修正します。
```
/*+ Rows(a b #10) */ SELECT... ; # Sets the row number of join results to 10.
 /*+ Rows(a b +10) */ SELECT... ; # Increases the row number by 10.
 /*+ Rows(a b -10) */ SELECT... ; # Decreases the row number by 10.
 /*+ Rows(a b *10) */ SELECT... ; # Increases the row number by 10 times.
```
- 並列実行のヒント
  このタイプのヒントは、SQL文を並列に実行するために使用されるプランを指定します。
  並列実行のヒントは、通常テーブル、継承テーブル、未ログテーブル、およびシステムテーブルで有効です。ただし、並列実行のヒントは、外部テーブル、定数の値が指定されている句、普遍的な式、ビュー、およびサブクエリには影響しません。ビューの内部テーブルは、その実名またはエイリアスで指定できます。
  次の例は、各テーブルでSQL文を異なる方法で実行する方法を示しています。
  - 例1: c1テーブルの並列度 (DOP) を3に設定し、c2テーブルのDOPを5に設定します。
    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: t1テーブルのDOPを5に設定します。
    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パラメーターの値は、エグゼキュータが実行プランを生成した場合にのみ有効です。この値は、セッション全体に影響を与えることなく、クエリのパフォーマンスを向上させます。 GUCパラメーターに複数のヒントを設定すると、最新のヒントが有効になります。
  例：
```
 /*+ Set(random_page_cost 2.0) */
 SELECT * FROM table1 t1 WHERE key = 'value';
```

ヒント構文の一覧

次の表に、サポートされているすべてのヒント構文を示します。ヒントをクエリのコメントとしてサーバー固有の目的に追加できます。オプションのパラメーターは、構文で括弧 ([ ]) のペアで囲まれています。

データ型	ヒント構文	説明

データ型	ヒント構文	説明
スキャン方法のヒント	SeqScan (テーブル)	シーケンススキャンを指定します。
	TidScan (テーブル)	TIDスキャンを指定します。
	IndexScan (テーブル [インデックス...])	インデックススキャンを指定します。インデックスを指定できます。
	IndexOnlyScan (テーブル [インデックス...])	インデックスのみのスキャンを指定します。インデックスを指定できます。
	BitmapScan (テーブル [インデックス...])	ビットマップスキャンを指定します。インデックスを指定できます。
	NoSeqScan (テーブル)	シーケンススキャンを禁止します。
	NoTidScan (テーブル)	TIDスキャンを禁止します。
	NoIndexScan (テーブル)	インデックススキャンを禁止します。
	NoIndexOnlyScan (テーブル)	インデックススキャンを禁止します。テーブルのみがスキャンされます。
	NoBitmapScan (テーブル)	ビットマップスキャンを禁止します。
結合メソッドのヒント	NestLoop (テーブルテーブル [テーブル...])	ネストされたループ結合を指定します。
	HashJoin (テーブルテーブル [テーブル...])	ハッシュ結合を指定します。
	MergeJoin (テーブルテーブル [テーブル...])	マージ結合を指定します。
	NoNestLoop (テーブルテーブル [テーブル...])	ネストされたループ結合を禁止します。
	NoHashJoin (テーブルテーブル [テーブル...])	ハッシュ結合を禁止します。
	NoMergeJoin (テーブルテーブル [テーブル...])	マージ結合を禁止します。
参加注文のヒント	リーディング (テーブルテーブル [テーブル...])	結合順序を指定します。
参加注文のヒント	リーディング (<参加ペア>)	結合の順序と方向を指定します。
行番号修正のヒント	行 (テーブルテーブル [テーブル...] 修正)	指定したテーブルから取得した結合結果の行番号を修正します。次の演算子がサポートされています: #<n> 、+ <n> 、-<n> 、および * <n> 。 <n> 演算子はstrtod関数でサポートされています。
並列実行のヒント	Parallel (テーブル <# of workers> [soft \| hard])	指定されたテーブルの並列実行を指定または禁止します。説明 <worker#> パラメーターは、必要な作業プログラムの数を指定します。値0は、並列実行を禁止することを指定する。 3番目のパラメーターがsoftに設定されている場合、max_parallel_workers_per_gatherパラメーターの値のみが変更され、その他のパラメーターはオプティマイザによって指定されます。 3番目のパラメーターがhardに設定されている場合、関連するすべてのパラメーターの値が変更されます。 3番目のパラメーターはデフォルトでsoftに設定されています。
	PX(<# of workers>)	クロスノード並列実行を指定します。クロスノード並列実行の詳細については、「概要」をご参照ください。説明 <# of workers> はDOPを指定します。
	NoPX()	クロスノード並列実行を禁止します。
GUCパラメータ設定のヒント	セット (GUC-param値)	オプティマイザの実行時のGUCパラメーターの値を指定します。

説明

pg_hint_plan拡張機能は、クロスノード並列実行中に生成される実行プランを指定することもできます。クロスノード並列実行中、Rows(table table[ table...] correction) ヒントはサポートされません。結合メソッドのヒントは2つのテーブルの結合にのみ使用でき、結合順序のヒントはすべてのテーブルにのみ使用できます。