Japan

BluStellar（ブルーステラ）

製品・ソリューション

  • セキュリティ

    NECは、「正しくつくる」「正常をつづける」「攻撃からまもる」の3つの軸で、お客様のビジネスの信頼性向上・生産性向上・事業拡大につながるご支援をしていきます。

関連リンク

業種・業務

関連リンク

企業情報

関連リンク

関連リンク

関連リンク

サイト内の現在位置

PostgreSQL

周辺OSSツール紹介

pg_hint_plan 機能概要

pg_hint_plan は PostgreSQLのプランナが作成するクエリ実行計画につき、ユーザー側で実行計画を差し替えて固定できるようにするツールです。
(※導入目的はDBパフォーマンスチューニングとなります)

PostgreSQLでは、クエリ実行時にプランナがテーブル統計情報を参照して最小コストとなる実行計画を作成しますが、必ずしも最速な処理時間とならない場合があります。
この場合において、ユーザーは一切介入できません。
pg_hint_planを使用することにより、ユーザー側で実行計画を固定できるため、ユーザーの環境に応じたクエリ実行の細かな最適化を実現できます。
(※ただし、ユーザーによる対象DBへの十分な知識と理解が必要です)

1. 実行計画の指定方法

pg_hint_planで実行計画を指定するには、コメント構文(/*～*/)の開始直後に(+)を付けて、以下のようにヒント句を指定します。
 　/*+ ヒント句 */        
以下の例では、Hash JoinとSeqScanの使用を指示し、実際にそれらが用いられていることをEXPLAINコマンドで確認しています。

/*+ HashJoin(a b) SeqScan(a) */ 
EXPLAIN ANALYZE
SELECT * FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid ;
                                                                      QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=235759.02..332988.11 rows=833334 width=461) (actual time=269.474..557.574 rows=1000000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=234759.00..235800.67 rows=416667 width=461) (actual time=266.109..337.128 rows=333333 loops=3)
         Sort Key: a.aid
         Sort Method: external merge  Disk: 38080kB
         Worker 0:  Sort Method: external merge  Disk: 37320kB
         Worker 1:  Sort Method: external merge  Disk: 37352kB
         -> Hash Join  (cost=1.23..22119.19 rows=416667 width=461) (actual time=0.151..139.361 rows=333333 loops=3)
               Hash Cond: (a.bid = b.bid)
               -> Parallel Seq Scan on pgbench_accounts a  (cost=0.00..20560.67 rows=416667 width=97) (actual time=0.025..37.148 rows=333333 loops=3)
               ->  Hash  (cost=1.10..1.10 rows=10 width=364) (actual time=0.023..0.024 rows=10 loops=3)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     -> Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.011..0.012 rows=10 loops=3)
 Planning Time: 0.720 ms
 Execution Time: 600.923 ms
(16 行)

ヒント句を指定しない場合、Nested LoopとIndexScanが使用されることがわかります。

EXPLAIN ANALYZE
SELECT * FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid ;
                                                                            QUERY PLAN                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.42..180105.82 rows=1000000 width=461) (actual time=0.119..1159.170 rows=1000000 loops=1)
   Join Filter: (b.bid = a.bid)
   Rows Removed by Join Filter: 4500000
   -> Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..42377.43 rows=1000000 width=97) (actual time=0.089..345.014 rows=1000000 loops=1)
   ->  Materialize  (cost=0.00..1.15 rows=10 width=364) (actual time=0.000..0.000 rows=6 loops=1000000)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.016..0.035 rows=10 loops=1)
 Planning Time: 0.894 ms
 Execution Time: 1198.720 ms
(8 行)

hint_plan.hintsテーブルにあらかじめSQL文とヒント句を登録しておくと、登録済みSQL文の実行時にヒント句を指定しなくてもヒントが自動で使用されます。
　hint_plan.hintsテーブルの登録例： 特定のSQL文にHash JoinとSeqScanを適用させたい場合

SELECT * FROM hint_plan.hints ;
   id |                     norm_query_string                                                   | application_name |          hints          
----+---------------------------------------------------------------------+-------------------+--------------------------
  1   | EXPLAIN ANALYZE                                                                     +| psql                      | HashJoin(a b) SeqScan(a)
       | SELECT * FROM pgbench_branches b                                       +|                              |
       | JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid ;    |                              |
(1 行)

上記の設定を行うことで、ヒント句を指定しなくても、Hash JoinとSeqScanが適用されます。

EXPLAIN ANALYZE
SELECT * FROM pgbench_branches b
JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid ;
                                                                      QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=235759.02..332988.11 rows=833334 width=461) (actual time=393.180..654.768 rows=1000000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=234759.00..235800.67 rows=416667 width=461) (actual time=378.591..452.648 rows=333333 loops=3)
         Sort Key: a.aid
         Sort Method: external merge  Disk: 29840kB
         Worker 0:  Sort Method: external merge  Disk: 48832kB
         Worker 1:  Sort Method: external merge  Disk: 34040kB
         -> Hash Join Hash Join  (cost=1.23..22119.19 rows=416667 width=461) (actual time=0.187..198.556 rows=333333 loops=3)
              Hash Cond: (a.bid = b.bid)
              -> Parallel Seq Scan on pgbench_accounts a  (cost=0.00..20560.67 rows=416667 width=97) (actual time=0.035..53.531 rows=333333 loops=3)
              ->  Hash  (cost=1.10..1.10 rows=10 width=364) (actual time=0.026..0.028 rows=10 loops=3)
                   Buckets: 1024  Batches: 1  Memory Usage: 9kB
                   ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.014..0.016 rows=10 loops=3)
 Planning Time: 0.371 ms
 Execution Time: 693.908 ms
(16 行)

※ヒント用テーブルに登録済みのクエリ文字列に対して、登録済みのヒントが自動で適用される

2. 利用可能なヒント句

PostgreSQLで利用可能なヒント句の一例を以下に示します。
PostgreSQLのヒント句と同等の意味を持つOracleのヒント句についても記載していますので参考にしてください。

ヒント句グループ ヒント句の内容 Oracleのヒント句 PostgreSQLの
ヒント句
スキャン方式 全表をスキャンする FULL SeqScan
索引スキャンを使用する INDEX IndexScan
結合方式 指定した各表をハッシュ結合する USE_HASH Hashjoin
結合順序 ヒント句内に記述された順序のとおりに結合を行なう LEADING Leading
パラメータの一時的な変更 指定したパラメータを指定した値に変更する OPT_PARAM Set
Parallelヒント 指定された数の同時サーバーをパラレル操作に使用する PARALLEL Parallel
3. データベース製品ごとのヒント句の指定方法の違い

ヒント句の指定方法は、データベース製品ごとに異なります。
PostgreSQL、Oracle、SQL Serverのヒント句の記述例を以下に示します。

  ヒント句の記述例
PostgreSQL explain /*+ IndexScan(e) */ select name, address from employee e where id > 2;
Oracle explain plan for select /*+ INDEX(e) */ name, address from employee e where id > 2;
SQL Server select name, address from employee e with (INDEX(e) ) where id > 2;

pg_hint_planでは、比較的Oracleに近い方法でヒント句を指定することができます。
Microsoft SQL Serverではいくつかの例外を除き、テーブル ヒントは、FROM 句で WITH キーワードを用いて指定します。

各データベース製品のヒント機能については以下を参照してください。
PostgreSQLのヒント一覧
https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_list.md
Oracleのヒント一覧
https://docs.oracle.com/cd/E82638_01/sqlrf/Comments.html
SQL Serverのヒント一覧
https://learn.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16

資料請求・お問い合わせnew window

お問い合わせ

Escキーで閉じる 閉じる