はじめに

業務で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)

ソート処理に時間がかかっているだけで、結合は早くなっている。なるほど。

最後に

私の理解が間違ってたりしたら、コメントくださいませ。