PostgreSQLの実行計画取得

# はじめに

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

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

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

```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を指定すると実行時間等の情報が追加されます。
追加されている個所は★をつけています。

```sql
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はキャッシュにページが見つかったことを示します。

```sql
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を指定できます。
プログラムから扱う場合に使用することになると思います。

```sql
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などを実行する場合は、トランザクションを有効にして実行計画取得後にロールバックする必要があります。

```sql
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
```

# プリペアード文

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

- [PREPARE](https://www.postgresql.jp/document/11/html/sql-prepare.html)

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

```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
```


# 実行計画の読み方

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

- [PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～](https://www.slideshare.net/MikiShimogai/postgre-sql-explain)
- [PostgreSQLの実行計画について調べてみた](https://www.casleyconsulting.co.jp/blog/engineer/259/)


# 参考

- [PostgreSQL 11.5文書 EXPLAIN](https://www.postgresql.jp/document/11/html/sql-explain.html)