コマンド

EXPLAIN文

実行計画を確認したいSQLの前に「EXPLAIN」をつけることで、実行計画を表示できる。

[test_db=# explain select * from sample_tbl;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on sample_tbl  (cost=0.00..22.70 rows=1270 width=36)

ANALYZEオプション

EXPLAIN文で表示されるのは、予想コスト。
ANALYZEオプションをつけることで、実際の処理時間も表示させることができる。
※実際にクエリが実行されるため、INSERT/UPDATE/DELETEの場合は注意する

test_db=# explain analyze select * from sample_tbl;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on sample_tbl  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.012..0.013 rows=2 loops=1)
 Planning time: 0.067 ms
 Execution time: 0.039 ms
(3 rows)

「EXPLAIN ANALYZE」を実行した場合の表示内容の意味は以下のとおり。

actual time : 処理時間
rows : 実行結果として、実際に戻ってきた行数
loops : ステップの実行回数
Plannning time : 解析されたクエリから実行計画を生成し、最適化するのに要した時間
Execution time : 実行時間

ANALYZE文

統計情報を取得するためのSQL。

[test_db=# analyze sample_tbl;
ANALYZE

実行計画の見方

実行計画はツリー構造になっている。
表示された実行計画を確認する際にも、最下層のノードから処理を追っていく。

ノードの種類

各処理の単位であるノードについて整理。

テーブルスキャンノード

テーブルからデータを取り出す役割のノード。

Seq Scan
Index Scan
Bitmap Scan

Seq Scan on app_detail  (cost=0.00..418.68 rows=8468 width=348) (actual time=0.003..7.486 rows=8469 loops=1)

Index Scan using app_detail_pkey on app_detail  (cost=0.00..1455.15 rows=8468 width=348) (actual time=0.066..10.038 rows=8469 loops=1)

cost

costには2つの数値が出力される。
1つ目は始動コストで、「最初の行が返されるまでの時間」。
2つ目は総コストで、「すべての行が返されるまでの時間」。
※バージョンによって違うこともあるらしい...

(cost=0.00..12769.39 rows=235939 width=56) (actual time=0.045..1179.461 rows=235939 loops=1)

結合系ノード

複数テーブルを結合する役割のノード。
※結合方式とインデックスの関係も理解しておく

Nested Loop Join

outer table1行ごとに、inner tableを走査する
- そのため、テーブル件数が増えるとコストが大きくなる
- 結合キーでindexがある場合は、inner table走査時にIndex Scanになるため、高速化ができる
有効なケース
- 1.結合されるテーブルどちらかのデータ量が少なくて、もう一方が多い
  - inner table
- 2.inner table側がインデックススキャンを使える
  - INNER JOINとかLEFT OUTER JOINに指定しているテーブルが「inner table」

Hash Join

inner tableのハッシュ表をメモリ上に作成する
- サイズがメモリサイズより大きくなってしまった場合、遅くなる
- 結合する列にインデックスは不要
  - パフォーマンス改善をするときには、WHERE句述語にインデックスを定義する
有効なケース
- 結合対象のデータ量が多い場合
  - かつ、データがソートされていない場合

Sort Merge Join

結合キーでソートし、マッチングする(※「ファスナーのように組み合わせる」イメージ)
- outer tableは１回調べればよく、走査回数は減る
- ソートさえできれば速い
  - なので、結合キーにインデックスを張れば早くなる
有効なケース
- 結合対象のデータ量が多い場合
  - かつ、データが既にソートされている場合

SQLパフォーマンスチューニングのポイント

あくまでも個人的な考え。もっと充実させていきたい。

作業の流れ

まずはexplain analyzeで、実際の実行プランを取得
統計情報が正しいか？
- 推定のrowsと、実際のrowsの値を比較する
- 近い場合は「統計情報が正しい」といえるが、あまりにも離れている場合は「統計情報が正しくない」
- その場合は、ANALYZE文を実行して統計情報を最新化する

実行プラン確認

実行プランから、ネックになっている処理を突き止める
- まずは「Total runtime」を確認
- 各処理の「actual time」(実際の処理時間)を確認
  - Total runtimeに対して、最も時間がかかっている処理を突き止める
  - その処理に対して「なぜ重いのか」を推測し、チューニングを実行していく
- 「なぜ重いのか」を考える観点としては...
  - インデックスを正しく使用しているか
    - Seq Scanではなく、Index Scanを実行したい
      - インデックスの貼り忘れ？
      - WHERE句の条件の指定の仕方が悪い？
  - 正しく件数を絞り込めているか(rowsの件数)
    - 「不要なデータを抽出したりしていないか」
    - 「JOINするレコード数を減らせないか」
      - 必要なレコードを先に絞り込んでからJOINする、など

【PostgreSQL】パフォーマンスチューニング(できるようになるための知識)