クエリ計画カスタマイズ拡張機能の使用方法 - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL は、特別なコメント文をヒントとして使用して PostgreSQL の実行計画を変更できる pg_hint_plan 拡張機能を提供します。

前提条件

インスタンスは RDS PostgreSQL 10 以降を実行します。
説明
拡張機能を作成できない場合は、まずマイナーエンジンバージョンをアップグレードしてください。たとえば、RDS PostgreSQL 17 インスタンスの場合、マイナーエンジンバージョンは 20241030 以降である必要があります。
RDS PostgreSQL インスタンスの特権アカウントを作成済みであること。詳細については、「アカウントの作成」をご参照ください。

背景情報

PostgreSQL は、静的なルールではなくデータ統計を利用するコストベースのオプティマイザーを使用します。SQL 文に対して、オプティマイザーは考えられる各実行計画のコストを評価し、最もコストの低いものを選択します。オプティマイザーは最適な実行計画を選択しようとしますが、データに存在する可能性のあるいくつかの固有の関係を理解していないため、これらの実行計画は完全ではない場合があります。pg_hint_plan 拡張機能を使用して、SQL 文にヒントを追加できます。ヒントは、SQL 文を実行する方法を指定します。これにより、SQL 文の実行計画を最適化できます。

拡張機能のインストールとアンインストール

コンソールでの拡張機能の管理

拡張機能のインストール
1. RDS インスタンスリストにアクセスし、上部のナビゲーションバーでリージョンを選択し、ターゲットインスタンスの ID をクリックします。
2. 左側のナビゲーションウィンドウで、[プラグイン] をクリックします。
3. [プラグインの管理] ページで、[未インストール] タブをクリックし、pg_hint_plan 拡張機能を検索して、[操作] 列の [インストール] をクリックします。
4. 表示されるダイアログボックスで、ターゲットデータベースと特権アカウントを選択し、[インストール] をクリックして拡張機能をターゲットデータベースにインストールします。
  インスタンスのステータスが [インスタンスのメンテナンス中] から [実行中] に変わると、拡張機能は正常にインストールされます。
拡張機能の更新とアンインストール
- [プラグインの管理] ページで、[インストール済み] タブをクリックし、ターゲット拡張機能の [操作] 列にある [バージョンのアップグレード] をクリックして、最新バージョンにアップグレードします。
  注意
  [操作] 列に [バージョンのアップグレード] ボタンがない場合、拡張機能はすでに最新バージョンです。
- [プラグインの管理] ページで、[インストール済み] タブをクリックし、[操作] 列の [アンインストール] をクリックして、ターゲット拡張機能をアンインストールします。

SQL コマンドを使用した拡張機能の管理

インスタンスパラメーターを設定し、shared_preload_libraries の [実行中の値] に pg_hint_plan を追加します。たとえば、[実行中の値] を 'pg_stat_statements,auto_explain,pg_hint_plan' に変更します。
特権アカウントを使用して、拡張機能をインストールするデータベースに接続し、次の SQL 文を実行して拡張機能を管理します。
- 拡張機能のインストール
```
CREATE EXTENSION pg_hint_plan;
```
- 拡張機能のアンインストール
```
DROP EXTENSION pg_hint_plan;
```

コメントヒント

pg_hint_plan のコメントヒントは /*+ で始まり、*/ で終わります。

ヒント文は、ヒント名と括弧内のパラメーターで構成され、パラメーターはスペースで区切られます。読みやすくするために、各ヒント文は新しい行から始めることができます。

例

結合メソッドとして HashJoin を使用し、test_table01 テーブルをスキャンするために SeqScan を使用します。

 /*+
    HashJoin(a b)
    SeqScan(a)
  */
 EXPLAIN SELECT *
    FROM test_table02 b
    JOIN test_table01 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 test_table01 a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on test_table02 b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

SQL クエリでテーブル名にエイリアスが使用されている場合、pg_hint_plan ヒント文でも同じエイリアスを使用する必要があります。

例

ヒント文 (エイリアスあり):

/*+ IndexScan(t) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

次の結果が返されます。

             QUERY PLAN              
-------------------------------------
 Index Scan using tbl_a_idx on tbl t
   Index Cond: (a = 1)

ヒント文 (エイリアスなし):

/*+ IndexScan(tbl) */ 
EXPLAIN (COSTS OFF) 
SELECT * 
FROM tbl t 
WHERE a = 1;

次の結果が返されます。

    QUERY PLAN     
-------------------
 Seq Scan on tbl t
   Filter: (a = 1)

ヒントテーブル

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

説明

デフォルトでは、pg_hint_plan 拡張機能を作成したユーザーは、hint_plan.hints テーブルに対する権限を持ちます。hint_plan.hints テーブルのヒントは、pg_hint_plan 拡張機能を使用して追加したヒントよりも優先されます。

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

ヒントテーブルの有効化

SET pg_hint_plan.enable_hint_table = on;

ヒントテーブルへのデータの挿入

INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
VALUES (
    'EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;',
    '',
    'SeqScan(t1)'
);

ヒントテーブル内のデータの更新

UPDATE hint_plan.hints
SET hints = 'IndexScan(t1)'
WHERE id = 1;

ヒントテーブルからのデータの削除

DELETE FROM hint_plan.hints
WHERE id = 1;

ヒントの種類

ヒント句が実行計画にどのように影響するかに基づいて、次の 6 つのタイプに分類できます。

スキャンメソッドのヒント
このタイプのヒントは、指定されたテーブルのスキャンに使用されるメソッドを指定します。指定されたテーブルにエイリアスがある場合、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 つ以上のテーブルが結合される順序を指定します。次のいずれかのメソッドを使用して、結合順序を指定するヒントを指定できます。
- 各結合レベルで方向を制限することなく、指定されたテーブルを結合する順序を指定します。
- 指定されたテーブルを結合する順序と各結合レベルでの方向を指定します。
例
```
-- 各結合レベルで方向を制限せずに結合順序を指定する
/*+
    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);

-- 結合順序と方向を指定する
/*+
    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... ;     // 結合結果の行数を 10 に設定します。
/*+ Rows(a b +10) */ SELECT... ;     // 行数を 10 増やします。
/*+ Rows(a b -10) */ SELECT... ;     // 行数を 10 減らします。
/*+ Rows(a b *10) */ SELECT... ;     // 行数に 10 を掛けます。

並列実行のヒント

このタイプのヒントは、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';
```

サポートされているヒントフォーマット

カテゴリ	フォーマット	説明
スキャンメソッドのヒント	SeqScan(table)	テーブルのシーケンシャルスキャンを強制します。
	TidScan(table)	テーブルのタプル識別子 (TID) スキャンを強制します。
	IndexScan(table[ index...])	インデックススキャンを指定します。インデックスを指定できます。
	IndexOnlyScan(table[ index...])	インデックスオンリースキャンを指定します。インデックスを指定できます。
	BitmapScan(table[ index...])	ビットマップスキャンを指定します。
	NoSeqScan(table)	シーケンシャルスキャンを禁止します。
	NoTidScan(table)	TID スキャンを禁止します。
	NoIndexScan(table)	インデックススキャンを禁止します。
	NoIndexOnlyScan(table)	インデックススキャンを禁止します。テーブルのみがスキャンされます。
	NoBitmapScan(table)	ビットマップスキャンを禁止します。
結合メソッドのヒント	NestLoop(table table[ table...])	ネストされたループ結合を指定します。
	HashJoin(table table[ table...])	ハッシュ結合を指定します。
	MergeJoin(table table[ table...])	マージ結合を指定します。
	NoNestLoop(table table[ table...])	ネストされたループ結合を禁止します。
	NoHashJoin(table table[ table...])	ハッシュ結合を禁止します。
	NoMergeJoin(table table[ table...])	マージ結合を禁止します。
結合順序のヒント	Leading(table table[ table...])	結合順序を指定します。
結合順序のヒント	Leading(<join pair>)	結合順序と方向を指定します。
行数補正のヒント	Rows(table table[ table...] correction)	指定されたテーブルから取得された結合結果の行数を修正します。利用可能な修正方法には、絶対値 `#<n>`、加算 `+ <n>`、減算 `-<n>`、および乗算 `* <n>` があります。`<n>` は strtod 関数で読み取ることができる数値です。
並列実行のヒント	Parallel(table <# of workers> [soft\|hard])	指定されたテーブルの並列実行を指定または禁止します。`<worker#>` は、使用する並列ワーカーの数です。このパラメーターを 0 に設定すると、並列実行は禁止されます。 3 番目のパラメーターを soft に設定すると、max_parallel_workers_per_gather パラメーターの値のみが変更され、他のパラメーターはオプティマイザーによって指定されます。3 番目のパラメーターを hard に設定すると、関連するすべてのパラメーターの値が変更されます。3 番目のパラメーターのデフォルト値は soft です。
GUC パラメーター設定のヒント	Set(GUC-param value)	オプティマイザーの実行時に GUC パラメーターの値を指定します。

詳細については、「pg_hint_plan」をご参照ください。