pg_stat_statementsの有効化
pg_stat_statementsは、実行されたSQL文ごとに実行回数や実行時間を集計する拡張機能である。postgresql.confのshared_preload_librariesに追加し、PostgreSQLを再起動してからCREATE EXTENSIONで有効化する。
shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;
累積の実行時間でクエリを絞り込む
pg_stat_statementsビューには、クエリごとの実行回数や実行時間の統計が集計される。累積の実行時間であるtotal_exec_timeで降順に並べると、システム全体に対する負荷が大きいクエリから確認できる。
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
query | calls | total_exec_time | mean_exec_time | rows
--------------------------------------------+-------+------------------+-----------------+------
SELECT * FROM accounts WHERE balance = $1 | 3 | 12.209 | 4.070 | 6
SELECT * FROM accounts WHERE id = $1 | 5 | 0.034 | 0.007 | 5
query: 実行されたSQL文（リテラル値は$1のようなパラメータに正規化される）calls: 実行回数total_exec_time: 実行時間の累積値（ミリ秒）mean_exec_time: 1回あたりの実行時間の平均値（ミリ秒）rows: 累積の返却行数
上記の例ではbalance列を条件にしたクエリが、実行回数は3回と少ないにもかかわらず、id列を条件にしたクエリよりtotal_exec_timeが大きい。mean_exec_timeを見ると1回あたり4.070msかかっており、インデックスが効いていないクエリであると推測できる。
total_exec_timeとmean_exec_timeの使い分け
total_exec_timeは実行回数の影響を受けるため、callsが多いクエリは1回あたりが軽くても上位に来やすい。1回あたりの重さを見るにはmean_exec_timeで並べ替える。
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
システム全体のボトルネックを探すならtotal_exec_time、個々のクエリの効率を見るならmean_exec_timeというように、目的に応じて並べ替えの基準を使い分ける。
統計情報のリセット
pg_stat_statementsが集計する統計は、サーバー起動からの累積値である。リリース後の変化など特定期間の傾向を調べたい場合は、対象期間の開始前に統計をリセットする。
SELECT pg_stat_statements_reset();
注意点
pg_stat_statementsはクエリ文字列をリテラル値ごとに区別せず$1のようなパラメータとして正規化して集計する。そのため、同じ形のクエリであれば条件の値が異なっても1つの行に集計される。特定のパラメータの組み合わせで遅いクエリを調べたい場合は、【PostgreSQL】EXPLAIN (ANALYZE, BUFFERS)でキャッシュヒット率を確認する
で実際の値を使って個別に実行計画を確認する。
