コマンド
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」
- 1.結合されるテーブルどちらかのデータ量が少なくて、もう一方が多い
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句の条件の指定の仕方が悪い？
- Seq Scanではなく、Index Scanを実行したい
- 正しく件数を絞り込めているか(rowsの件数)
- 「不要なデータを抽出したりしていないか」
- 「JOINするレコード数を減らせないか」
- 必要なレコードを先に絞り込んでからJOINする、など
- インデックスを正しく使用しているか
-