Help us understand the problem. What is going on with this article?

PostgreSQLの実行計画取得

More than 1 year has passed since last update.

はじめに

PostgreSQLではEXPLAINコマンドでSQLの実行計画を表示することができます。
EXPLAINコマンドの構文は以下のようになります。

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

pb_benchで生成されたテーブルに対して、実際にSQLの実行計画を取得すると以下のようになります。

testdb=# explain SELECT abalance FROM pgbench_accounts WHERE aid = 1;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.29..8.31 rows=1 width=4)
   Index Cond: (aid = 1)
(2 rows)

様々なオプションをつけて実行計画を作成してみる

よく利用するオプションについて、実際にオプションをつけて実行計画を取得してみます。

ANALYZE

ANALYZEを指定すると実行時間等の情報が追加されます。
追加されている個所は★をつけています。

testdb=# explain analyze SELECT abalance FROM pgbench_accounts WHERE aid = 1;
                                                               QUERY PLAN                                     

--------------------------------------------------------------------------------------------------------------
--------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.29..8.31 rows=1 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
   Index Cond: (aid = 1)
 Planning Time: 0.046 ms
 Execution Time: 0.025 ms
(4 rows)

BUFFERS

BUFFERSを指定するとバッファの使用状況の情報が追加されます。
ANALYZEと一緒に使用します。

追加されている個所は★をつけています。
Shared hitはキャッシュにページが見つかったことを示します。

testdb=# explain (analyze, buffers) SELECT abalance FROM pgbench_accounts WHERE aid = 1;
                                                               QUERY PLAN                                     

--------------------------------------------------------------------------------------------------------------
--------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.29..8.31 rows=1 width=4) (actual time=0.0
11..0.011 rows=1 loops=1)
   Index Cond: (aid = 1)
   Buffers: shared hit=3
 Planning Time: 0.044 ms
 Execution Time: 0.023 ms
(5 rows)

FORMAT

FORMATを指定すると出力するフォーマットを指定できます。
フォーマットはデフォルトのテキスト以外にXML、JSON、YAMLを指定できます。
プログラムから扱う場合に使用することになると思います。

testdb=# explain (analyze, buffers) SELECT abalance FROM pgbench_accounts WHERE aid = 1;
                                                               QUERY PLAN                                     

--------------------------------------------------------------------------------------------------------------
--------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.29..8.31 rows=1 width=4) (actual time=0.0
11..0.011 rows=1 loops=1)
   Index Cond: (aid = 1)
   Buffers: shared hit=3
 Planning Time: 0.044 ms
 Execution Time: 0.023 ms
(5 rows)

testdb=# explain (analyze, format xml) SELECT abalance FROM pgbench_accounts WHERE aid = 1;
                               QUERY PLAN                               
------------------------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">              +
   <Query>                                                             +
     <Plan>                                                            +
       <Node-Type>Index Scan</Node-Type>                               +
       <Parallel-Aware>false</Parallel-Aware>                          +
       <Scan-Direction>Forward</Scan-Direction>                        +
       <Index-Name>pgbench_accounts_pkey</Index-Name>                  +
       <Relation-Name>pgbench_accounts</Relation-Name>                 +
       <Alias>pgbench_accounts</Alias>                                 +
       <Startup-Cost>0.29</Startup-Cost>                               +
       <Total-Cost>8.31</Total-Cost>                                   +
       <Plan-Rows>1</Plan-Rows>                                        +
       <Plan-Width>4</Plan-Width>                                      +
       <Actual-Startup-Time>0.007</Actual-Startup-Time>                +
       <Actual-Total-Time>0.008</Actual-Total-Time>                    +
       <Actual-Rows>1</Actual-Rows>                                    +
       <Actual-Loops>1</Actual-Loops>                                  +
       <Index-Cond>(aid = 1)</Index-Cond>                              +
       <Rows-Removed-by-Index-Recheck>0</Rows-Removed-by-Index-Recheck>+
     </Plan>                                                           +
     <Planning-Time>0.049</Planning-Time>                              +
     <Triggers>                                                        +
     </Triggers>                                                       +
     <Execution-Time>0.020</Execution-Time>                            +
   </Query>                                                            +
 </explain>
(1 row)

注意点

ANALYZEを指定した場合、実際にSQLが実行されることに注意が必要です。
INSERT、UPDATE、DELETEなどを実行する場合は、トランザクションを有効にして実行計画取得後にロールバックする必要があります。

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

プリペアード文

SQLにプリペアード文を用いている場合、実行計画を取得する際にバインド変数ではなく値を指定すると、異なる実行計画となることがあるので注意が必要です。

バインド変数を使用している場合は、以下のように実行する必要があります。

testdb=# PREPARE stmt(int) as SELECT abalance FROM pgbench_accounts WHERE aid = $1;
PREPARE
testdb=# explain execute stmt(1);
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.29..8.31 rows=1 width=4)
   Index Cond: (aid = 1)
(2 rows)

testdb=# DEALLOCATE stmt

実行計画の読み方

実行計画の読み方は以下を参照。

参考

mkyz08
SIer&バックエンドエンジニア&日曜プログラマー。 Apache Camel/VoltDB/Oracle/Apache karaf。 基本的に仕事外での自分用のメモ(興味があること)として記事を書いています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away