Postgre: Windows 版でクエリーログの分析をしたい

はじめに

Power BI の Direct Query で発行される Postgres の Query を確認したかったのでその際に調べたことの記録

環境

• Postgres 14.0.1
  • Document は 13 用の日本語へのリンク
• pgAdmin 4.6.5
• Windows 10

SQL Server のように View にあるんじゃないか？

カタログのビューで以下やってみたが、無かった。
→　統計情報だった

現在も動いてるクエリーなんかはこれで確認して殺したり、とか出来るみたい。

pg_stat_activity

SELECT query
	FROM pg_catalog.pg_stat_activity;

ログを吐き出せば良さそう

設定ファイルはどこ？

C:\Program Files\PostgreSQL\14\data\postgresql.conf

変更する設定は？

最低限は以下だけかな、と

log_min_duration_statement -1

これを 0 へ

log_min_duration_statement (integer)
文の実行に少なくとも指定したミリ秒数かかった場合、それぞれの文の実行に要した時間をログに記録します。 0に設定すれば、すべての文の実行時間が出力されます。 -1（デフォルト）は、文実行時間の記録を無効にします。 例えば、250msと設定した場合、250msもしくはそれ以上長くかかった全てのSQL文がログとして残ります。 このパラメータを有効にすることにより、アプリケーションで最適化されていない問い合わせを追跡するのが便利になります。 スーパーユーザのみこの設定を変更できます。

設定詳細

When to log や What to log で設定調整しつつ

いつログを取得するか
何をログに

出力フォルダは？

C:\Program Files\PostgreSQL\14\data\log

#log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA

ローテーション出来る？

多分、以下

#log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.

ログが文字化けするよ？

原因

• UTF8 と ShiftJIS 混在になってる。頑張ればそれぞれで見ることは可能

対処

― Install 時に、Localを "C" ( none select ) にする

or

• postgresql.conf で以下修正。今回はこっち

locals

lc_messages = 'C'			# locale for system error message
					# strings
lc_monetary = 'C'			# locale for monetary formatting
lc_numeric = 'C'			# locale for number formatting
lc_time = 'C'				# locale for time formatting

説明詳細

Parse、Bind、Execute ってログが記録されるけど何？

拡張問い合わせ によるもので、全部出ちゃうって話。
詳細に見たくなくて Execute だけってしたかったんだけど・・log 設定では見つからない・・

Log の CSV 形式出力

CSV log にすれば フィルターかけやすいかな、ということで
以下みたいにして、"csvlog" を追加すれば、再起動後に出力される

log_destination = 'stderr, csvlog' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.

CSV の確認したい場合は、LinqPad がおすすめ

で、あとは、"execute" が含まれるものを、column 1: 登録時刻で降順にすれば欲しいものが一覧化出来て確認が楽になった、と

CSV には出力されてるのに、最新が見えないんだけど

キャッシュが効いてるので、用途によってはキャッシュを殺す

PostgreDB 再起動

設定の反映が必要な時に

1. Win + R
2. services.msc 起動
3. Postgre Server探して再起動

あとがき

Postgres の Document 翻訳作業は以下で 13.x が実施中ということなので、英語得意で何か Contribute したい人には向いてる気もする。
ちょっと気になるけど、まだやりたいことというか、やらなければってことが多すぎて・・手が出ない