PostgreSQLのチューニングに関して
SQLのチューニングが必要になる場合の原因としては
OracleからPostgreSQLに乗り換えた際の
処理時間の増加によるものが主となります。
Oracleでは1分未満でもPostgreSQLに乗り換えた場合に
数時間が経過しても処理が終わらないという場合があります。
まずは何が原因でそのような
膨大な時間が経過してしまうようになっているのかを
見極める必要があります。
EXPLAINコマンドを用いて
実行結果に統計情報を付加していきます。
※統計情報に対してどのような情報を表示させるかというのを
オプションで追加することができるが、
それは割愛。
EXPLAIN * FROM hoge;
これに対して
そのSQLの結果として
cost、row、width、filterといった情報が
表示されます。
cost: cost=0.00..10.00 といった表示がされ、この場合はコストが0から10ということを表している。
コストが小さいと全体として実行に手間がかかっていないsqlということになり、
逆にコストが大きいと直すべき箇所があるかも？と考える指標になる。
row： 処理行数の表示
width: 上記rowにおける各行のバイト数を表示
filter: SQLにおいて指定した条件を表示
name='田中'などの条件を指定した場合それが表示される。
このcostが大きいSQLから順に改修を検討していく
お手軽な対処法としては以下の二つがあげられる
1.INDEXの活用
2.条件の見直し
1.INDEXの活用
処理行数の多さによる部分はあるが、
事前に条件に用いられている項目でINDEXを貼る
↓
その後実行結果に差異が出るかを確認する
2.条件の見直し
例えばJOIN句の条件を見直すことができる
外部結合の後に内部結合を実施すると
外部結合でデータ量が増えたものを内部結合で圧縮する形になるため
読むデータが増えてしまっている。
内部結合から実施することで、結合の際に見るデータが減るため
効率の向上を図ることができる
またWHERE句でA.1 = B.1のようにするなら、
結合の条件の際にその条件を記載することで、
後続の結合等にデータを引き渡す際の件数削減を図ることができる。
JOIN句の条件を見直す
外部結合のON句に条件を指定した場合、
外部結合先がないデータに対しての各行はNULLが入ってしまう
そのため列数が増加した状態になっている。
※ON句で絞り込むか、WHERE句で絞り込むかは
結合後データのNULLを許容しているのか否かによる部分があるため、
すべての事象において条件をWHERE句に配置するべきとは言えない
また大規模なSQLを実施する際は
INDEXだけではなく、コミット件数を幅広く取ってあげ、
トランザクション等を用いることで処理効率の向上を図ることができる。
またSQL上というよりはPostgreSQLの仕様の話ではあるが、
削除等を行ったデータは内部的に削除フラグが立っているだけで
データとしては残っているという場合があるため、
VACUME等でディスク側のパフォーマンス向上を図るのも一つの手段ではないだろうか