PostgreSQLにおけるSQLの実行計画の取得

SQLが遅い…。そんな時にまず確認する必要があるのが、そのSQLの実行計画です。

本記事では、PostgreSQLにおけるSQLの実行計画の取得方法をまとめました。

実行計画とは
実行計画の取得方法（EXPLAIN）
実際の実行時間も取得する（EXPLAIN ANALYZE）
まとめ

実行計画とは

実行計画とは、短い時間でSQLを処理するために計算して具体的な方法のことです。

例えば、検索条件にインデックスが張られているのでインデックスを使用して検索をかけよう、といったようなことです。

データベースの中のオプティマイザという機能が実行計画を立てます。

実行計画の取得方法（EXPLAIN）

実行計画は【EXPLAIN SQL文】で取得することができます。実行計画を出力するだけで、実際にSQLは実行されません。

以下は、単純にテーブルを全項目全件取得するSQLでの実行計画の取得例です。

EXPLAIN SELECT * FROM test_table1;

                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on test_table1  (cost=0.00..173528.84 rows=9999884 width=27)

「Seq Scan」とあるので、test_table1テーブルを単純に全件検索していることが分かります。

ちなみに、「(cost…」の部分は推定値です。
「cost」の左側の数字は初期処理の推定コスト、例えばハッシュ結合の際のハッシュ表を作成するコストです。
「cost」の右側の数字は全体推定コストです。
「cost」は数字が大きければ大きいほど処理時間がかかることを意味しますが、決して具体的な時間を表しているわけではありません。
「rows」は行の推定数です。
「width」は1行あたりの推定平均幅（バイト）です。

上記しましたが、実際にSQLは実行されないため、更新や削除などのデータを変更するSQLの実行計画も気楽に取得することができます。

-- 実行計画取得前のデータ件数
SELECT COUNT(*) FROM test_table1;

  count
----------
 10000000


-- 全件削除の実行計画取得
EXPLAIN DELETE FROM test_table1;

                                 QUERY PLAN
----------------------------------------------------------------------------
 Delete on test_table1  (cost=0.00..173528.84 rows=9999884 width=6)
   ->  Seq Scan on test_table1  (cost=0.00..173528.84 rows=9999884 width=6)


-- 実行計画取得後のデータ件数
SELECT COUNT(*) FROM test_table1;

  count
----------
 10000000

実際の実行時間も取得する（EXPLAIN ANALYZE）

【EXPLAIN ANALYZE SQL文】で、実際にSQLを実行し、実際の実行時間も合わせて取得することも可能です。

EXPLAIN ANALYZE SELECT * FROM test_table1;

                                 QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on test_table1  (cost=0.00..173528.84 rows=9999884 width=27)
                          (actual time=0.755..3240.600 rows=10000000 loops=1)
 Planning time: 0.286 ms
 Execution time: 5076.337 ms

「(actual time…」の部分が実際にSQLを実行して得た数値です。
「actual time」の左側の数字は1行目のデータを処理するまでの時間です。
「actual time」の右側の数字は全体の処理時間です。
「actual time」は「cost」と具体的な時間（ミリ秒）を表しています。
「rows」は行数です。
「loops」は実行する回数です。
「Planning time」は実行計画を立てるためにかかった時間（ミリ秒）です。
「Execution time」は実行計画に基づいた処理にかかった時間（ミリ秒）です。

実際に実行をするため、特に本番環境で実行する際には注意が必要です。
データの更新がかかる処理は、EXPLAIN ANALYZE実行後にROLLBACKしてデータを戻すことが必須です。（それでも対象にロックがかかるので要注意）
SELECT文でも負荷のかかるSQLだと、他セッションのSQLの処理時間に影響を与えてしまう可能性もあります。

以下はデータの更新がかかることの例です。

-- 実行計画取得前のデータ件数
SELECT COUNT(*) FROM test_table1;

  count
----------
 10000000


-- 全件削除の実行計画取得
EXPLAIN ANALYZE DELETE FROM test_table1;

                                    QUERY PLAN
------------------------------------------------------------------------------------
 Delete on test_table1  (cost=0.00..173528.84 rows=9999884 width=6)
                        (actual time=21626.148..21626.148 rows=0 loops=1)
   ->  Seq Scan on test_table1  (cost=0.00..173528.84 rows=9999884 width=6)
                                (actual time=1.106..6281.506 rows=10000000 loops=1)
 Planning time: 0.173 ms
 Execution time: 21626.299 ms


-- 実行計画取得後のデータ件数
SELECT COUNT(*) FROM test_table1;

 count
-------
     0

実際にデータが更新されてしまうことを防ぐために、ROLLBACKをする例です。

-- 実行計画取得前のデータ件数
SELECT COUNT(*) FROM test_table1;

  count
----------
 10000000


-- 全件削除の実行計画取得（計画取得後にROLLBACKでデータを戻す）
BEGIN;

EXPLAIN ANALYZE DELETE FROM test_table1;

                                    QUERY PLAN
------------------------------------------------------------------------------------
 Delete on test_table1  (cost=0.00..173528.84 rows=9999884 width=6)
                        (actual time=21626.148..21626.148 rows=0 loops=1)
   ->  Seq Scan on test_table1  (cost=0.00..173528.84 rows=9999884 width=6)
                                (actual time=1.106..6281.506 rows=10000000 loops=1)
 Planning time: 0.173 ms
 Execution time: 21626.299 ms

ROLLBACK;


-- 実行計画取得後のデータ件数
SELECT COUNT(*) FROM test_table1;

  count
----------
 10000000