はじめに
業務でindexによるパフォーマンスチューニングをする機会が増えてきたので、
個人的に学びになったサイト等を共有します。
随時追記します。
警告
個人的学習の一環で概念理解のために書いてあるので、
本質とは異なる点がございます。ご注意くださいませ。
チューニングの学習に役立ったサイト
JOINの種類がわかる
JOINの内部的な仕組みの概要の理解ができる
コードで結合の仕方を解説してくれている
インデックス付与による、結合のチューニング
やってみる
チュートリアル。やってみよう。
あまりにデータ量が少ない場合は、インデックスを作成しても効かない場合がある。
クエリプランナーはテーブルのサイズに基づいて最適な結合方法を選択します。テーブルのサイズが小さい場合、実行計画の最適化が難しいことがある。
だからだってばよ。
- 実行SQL
select * from msplayer a inner join msteam b on
a.teamid = b.teamid
- インデックスを作成しない場合の実行計画
Hash Join (cost=8846.00..41020.50 rows=100000 width=1428) Hash Cond: ((a.teamid)::text = (b.teamid)::text)
+-Seq Scan on msplayer a (cost=0.00..1637.00 rows=100000 width=961)
+-Hash (cost=1541.00..1541.00 rows=100000 width=467)
+-Seq Scan on msteam b (cost=0.00..1541.00 rows=100000 width=467)
- インデックチューニング後
CREATE INDEX msteamIDX1 ON msteam (teamid);
CREATE INDEX msplayerIDX1 ON msplayer (teamid);
Merge Join (cost=0.58..8589.63 rows=100000 width=1428) Merge Cond: ((a.teamid)::text = (b.teamid)::text)
+-Index Scan using msplayeridx1 on msplayer a (cost=0.29..4396.29 rows=100000 width=961)
+-Index Scan using msteamidx1 on msteam b (cost=0.29..3693.33 rows=100000 width=467)
考察 なんで早くなったの？
インデックスを張っていない状態だと、
駆動テーブルの件数と内部テーブルの件数の掛け合わせなので、
計算量としてはO(N^2)になっている。
(Nested Loopだったら上記、今回はHash Joinだから厳密には違うけど便宜上)
改善したのは、index scanで検索が行われたため。
インデックスの全体つまり全行を、インデックスの順番に沿って読みこんだため。
つまり結合キーでソートをかけた状態のサブクエリで検索をかければ、
同じスピードになるのか？
考察の検証
以下、やってみた。
- 実行SQL
select * from (select * from msplayer order by teamid ) a
inner join (select * from msteam order by teamid ) b on
a.teamid = b.teamid
- 実行計画
Merge Join (cost=94712.24..102217.28 rows=1 width=1428) Merge Cond: ((msplayer.teamid)::text = (msteam.teamid)::text)
+-Sort (cost=94711.82..94961.82 rows=100000 width=961) Sort Key: msplayer.teamid
| +-Seq Scan on msplayer (cost=0.00..1637.00 rows=100000 width=961)
+-Materialize (cost=0.42..5755.45 rows=100000 width=467)
+-Index Scan using msteam_teamid_key on msteam (cost=0.42..4505.45 rows=100000 width=467)
ソート処理に時間がかかっているだけで、結合は早くなっている。なるほど。
最後に
私の理解が間違ってたりしたら、コメントくださいませ。