概要
postgresqlで実行したSQLの統計（実行回数、処理時間等）を保存する設定を記載しています。
確認環境
- OS：ubuntu 20.04
- DB：postgresql12
手順
1 postgresql.confでshared_preload_libraries の値を設定する。
/etc/postgresql/12/main/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
2 postgresqlを再起動する
sudo service postgresql restart
3 psqlで機能を利用したいデータベースにログインし、CREATE EXTENSIONで機能を有効化する
$ psql -U ＜ユーザ＞ -h ＜ホスト名＞ -d ＜データベース＞
postgres=# CREATE EXTENSION pg_stat_statements;
4 確認
postgres=# \d pg_stat_statements
postgres=# \x
postgres=# SELECT * FROM pg_stat_statements LIMIT 3;
補足
・postgresqlの再起動がcould not access file "pg_stat_statements":でエラーになる場合はライブラリがインストールされていません。
→contribパッケージ（postgresql-contrib、postgresql12-contrib等）をapt、yum等でインストールします。
ログ
postgres=# \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
queryid | bigint | | |
query | text | | |
calls | bigint | | |
total_time | double precision | | |
min_time | double precision | | |
max_time | double precision | | |
mean_time | double precision | | |
stddev_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
postgres=# SELECT *
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
-[ RECORD 1 ]-------+----------------------------------------------------------------------------------------------------
userid | 10
dbid | 13427
queryid | 6187140662117863622
query | SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT $1
calls | 19
total_time | 5.9750000000000005
min_time | 0.2197
max_time | 0.5009
mean_time | 0.31447368421052624
stddev_time | 0.05755371717022978
rows | 65
shared_blks_hit | 6
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0
折りたたみ
展開