Performanceが悪くてどこから手をつければいいんだろうというあなた。まずはlogを取得して分析しましょう。RDSのPerformance Insightsとか監視ツールが導入されていれば過去との比較ができたり便利そうだけど、なんにも導入されていないまずはオンプレを想定しています。logの見方だったり、分析の仕方はまたおいおい記事にするつもり。

pg_stat_statements

遅いSQLを特定する際に利用するパフォーマンスビュー

How to install

Windows に One Click Installer を使って PostgreSQL をインストールしている場合には、デフォルトで pg_stat_statments モジュールがインストールされている

設定

pg_stat_statments モジュールを使えるようにpostgresql.confに設定しDB再起動するだけです。
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

How to use

累積応答時間が遅いSQL Top10
-- total_exec_timeは累積ミリ秒
select * from pg_stat_statements order by total_exec_time desc limit 10
実行当たりの応答時間が遅いSQL Top10
-- mean_exec_time = total_exec_time/call
select * from pg_stat_statements order by mean_exec_time desc limit 10
統計値をいったんリセットする
-- performance test直前に実行しておくと以前の統計値とごちゃまぜにならなくて便利
select pg_stat_statements_reset()

pg_stat_user_functions

https://www.postgresql.jp/document/12/html/monitoring-stats.html
function別に遅延状況を確認できます。
このパフォーマンスビューはpg_stat_statementsと違ってinstallは必要ありません。なにもしなくても利用可能です。

How to use

累積応答時間が遅いfunction Top10
-- total_timeは累積ミリ秒
select * from pg_stat_user_functions order by total_exec_time desc limit 10
実行当たりの応答時間が遅いfunction Top10
select * from pg_stat_user_functions order by total_extime/calls desc limit 10
self_timeが遅いfunction Top10
/* total_timeが大きくてもself_timeが少ない場合があります。そういう場合はfunctionからcallしているfunctionが遅いことが考えられます。nestが多い場合は実際のslow functionを捕らえることが困難となります*/
select * from pg_stat_user_functions order by self_time desc limit 10
統計値をいったんリセットする
-- performance test直前に実行しておくと以前の統計値とごちゃまぜにならなくて便利
select pg_stat_reset()

explain analyze

https://www.postgresql.jp/document/10/html/sql-explain.html
実行計画をSQL levelで確認する
actual timeが大きい箇所を見ることでtuningすべき箇所を特定可能
下記3rd party toolのinputとする場合はjson出力してください

auto_explain

https://www.postgresql.jp/document/10/html/auto-explain.html
毎回explain analyzeするのは面倒なので実行されたSQLの実行計画をpostgresql.logへ出力する方法です。

how to install

contrib packageを導入する必要があります。
https://tech-lab.sios.jp/archives/8865#auto_explain

設定

postgresql.confへ設定し、DB再起動することで有効になります。
#pg_stat_statementsと併用しない場合はこちら
#shared_preload_libraries = 'auto_explain'
shared_preload_libraries = 'pg_stat_statements,auto_explain'
logging_collector = on
#ここに設定する値以上の応答時間のSQL実行計画を取得する
#0にするとすべてのSQLの実行計画が取得される
#つまり大量のlog出力&アプリ遅延も発生
auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
#functionが発行するSQLも取得する場合はtrueに設定
auto_explain.log_nested_statements=true

3rd party tool

pgbadger
Summalize & Visulaizer input postgresql.log
Sample html output file
http://pgbadger.darold.net/samplev7.html

参考資料
http://pgbadger.darold.net/
https://www.slideshare.net/tkishimo/postgresql-119950899

pgbadger利用時に設定するpostgresql.conf設定パラメタ例
lc_messages = 'C'
track_functions = all
log_lock_waits = on
log_temp_files = 0
log_checkpoints = on
#log_connections = on
#log_disconnections = on
log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u, db=%d'
shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
logging_collector = on
auto_explain.log_min_duration = 0
auto_explain.log_analyze = true
auto_explain.log_nested_statements=true
Parser explain plan
Query Plan Visualizer input json explain plan
http://tatiyants.com/postgres-query-plan-visualization/