はじめに
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
実行計画の読み方
実行計画の読み方は以下を参照。