💨

【過去Blogからの移行記事】初心者級SQLチューニング（DB環境はPostgreSQL）

2022/09/18に公開

11月から昨日まで、職場でSQLの速度改善のためにpg_stat(PostgreSQLの統計情報)とニラメッコしまくりだった。
昔からSQLにだけは深入りしたくないと思ってたけど、いよいよやる羽目になったのでしんどかったｗ

要点を簡潔にまとめると、下記のとおり。

・統計情報をリセットするSQL文

select pg_stat_reset();
※要postgres権限

・色々ある統計情報のうち、とりあえずTABLEスキャンとINDEXスキャンの情報を見るSQL文

SELECT
*
FROM
pg_stat_user_tables
ORDER BY
seq_tup_read DESC
, idx_tup_fetch DESC;

・クエリの実行プラン(の解析情報)を見るSQL文

EXPLAIN ANALYZE
※これに続けて、実行プランを解析したい対象のSQL文を書いて実行するだけ。

スロークエリだと判っているとあるSQL文について、開発対象のシステムの機能から実行して、その時のSQLログを取り出す。
対象SQLの取り扱っているテーブルに対して、できるだけ抽出対象のレコードと余計なレコードとを織り交ぜて登録しておく。
対象SQLをEXPLAIN ANALYZEして、実行プランのrowsが余計なレコードも含んでそうな値になってたら、それの対策を打つ。(SQLの組み換えとか組み直しとか)
さらに。統計情報をクリアしてEXPLAIN ANALYZEし、直後のpg_stat_user_tablesを見る。そこでseq_scanやseq_tup_readが余計なレコードに対してもかかっているようなら、うまいことINDEXを使うようにSQLを工夫する。(追加のINDEXを検討したり、絞込みだけするSELECTを用意してその結果をASで別名にしてその後のJOINで使ったり)
納得いくまで上記を繰り返す。
以上。

作業内容を上記に絞り込むまでには色々とWeb漁って用語や動作原理的なところの下調べをやったんだけど、ややこしいことをとっぱらっていったら、これだけになったw

調査の過程で特に参考にしたページを、下記に羅列しておきます。

インデックスを作成して，SQLの速度をチューニングする手順　（PostgreSQLで，EXPLAIN文とCREATE INDEX文によるパフォーマンス改善）

本日は以上です。