pg_stat_statements
pg_stat_statements拡張モジュールをインストールする事で、サーバで実行されたすべてのSQL文のプラン生成時と実行時の統計情報を取得できるようになります。PostgreSQLにおいてSQLのパフォーマンスチューニングをするには無くてはならないものだと思います。
追加可能モジュールなのか確認
select * from pg_available_extensions where name ='pg_stat_statements';
拡張モジュールの追加
拡張モジュールはどのスキーマにインストールしても同一のデータベース内であれば利用できます。また、同一クラスター内の他データベースで実行されたSQLも参照する事可能です。ただし、pg_stat_statementsを参照できるのはインストールしたデータベースに接続している場合に限ります。
create extension pg_stat_statements;
拡張モジュールの確認
select
d.datname,
n.nspname,
e.extname,
e.extversion,
obj_description(e.oid) as description
from
pg_extension e
inner join
pg_namespace n on e.extnamespace = n.oid
inner join
pg_database d on d.datname = current_database()
;
myposdb=# \x
拡張表示は on です。
myposdb=# select * from pg_available_extensions
myposdb-# where name ='pg_stat_statements';
-[ RECORD 1 ]-----+-----------------------------------------------------------------------
name | pg_stat_statements
default_version | 1.10
installed_version |
comment | track planning and execution statistics of all SQL statements executed
myposdb=# create extension pg_stat_statements;
CREATE EXTENSION
myposdb=#
myposdb=# \x
拡張表示は off です。
--
-- 以下追加できた事を確認する。(簡易版）
--
myposdb=# select * from pg_extension order by extname;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
17084 | pg_stat_statements | 10 | 2200 | t | 1.10 | |
16405 | pgcrypto | 16389 | 16393 | t | 1.3 | |
13540 | plpgsql | 10 | 11 | f | 1.0 | |
(3 行)
--
-- 丁寧に見るなら
--
myposdb=# select
myposdb-# d.datname,
myposdb-# n.nspname,
myposdb-# e.extname,
myposdb-# e.extversion,
myposdb-# obj_description(e.oid) as description
myposdb-# from
myposdb-# pg_extension e
myposdb-# inner join
myposdb-# pg_namespace n on e.extnamespace = n.oid
myposdb-# inner join
myposdb-# pg_database d on d.datname = current_database()
myposdb-# ;
datname | nspname | extname | extversion | description
---------+------------+--------------------+------------+------------------------------------------------------------------------
myposdb | pg_catalog | plpgsql | 1.0 | PL/pgSQL procedural language
myposdb | esearch | pgcrypto | 1.3 | cryptographic functions
myposdb | public | pg_stat_statements | 1.10 | track planning and execution statistics of all SQL statements executed
(3 行)
myposdb=#
psstgresql.confを書き換える
pg_stat_statements.track_planning はSQLのプラン生成に費やした時間に関する情報を取得するためのパラメータなのですが、バージョン13以降で有効のようです。（バージョン12までは存在していません。そういった意味でもバージョン12以下の場合はバージョンアップ強くお勧めします）
--デフォルト
#shared_preload_libraries = '' # (change requires restart)
--以下の通り書き換え
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
pg_stat_statements.max = 1000
pg_stat_statements.track = all # all/top/none
pg_stat_statements.save = on # on/off
pg_stat_statements.track_planning = on
--
--保存した後、PostgreSQLを再起動して設定を反映させる
--pg_stat_statements.save = off にしておくとPostgreSQLサーバ再起動のタイミングでpg_stat_statementsビューの値がクリアされます。
設定が反映された事を確認する
select name, setting, unit from pg_settings where name like 'pg_stat_statements%' or name ='shared_preload_libraries' order by 1 desc;
myposdb=# select name, setting, unit from pg_settings where name like 'pg_stat_statements%'
myposdb-# or name ='shared_preload_libraries'
myposdb-# order by 1 desc;
name | setting | unit
-----------------------------------+--------------------+------
shared_preload_libraries | pg_stat_statements |
pg_stat_statements.track_utility | on |
pg_stat_statements.track_planning | on |
pg_stat_statements.track | all |
pg_stat_statements.save | on |
pg_stat_statements.max | 1000 |
(6 行)
myposdb=#設定が反映された事を確認する
以下はpg_authidを使ってもよいのですが、RDS PostgreSQLではアクセスできないと思うのでpg_rolesを使っています。
select a.rolname,d.datname --,s.userid,s.dbid,s.queryid ,s.calls,s.total_exec_time,s.query from pg_stat_statements s inner join pg_roles a on s.userid = a.oid inner join pg_database d on s.dbid = d.oid where s.query like '%ex01%';
設定が反映された事を確認する
select pg_stat_statements_reset();
-- 検証用にSELECT文を実行
--
myposdb=# select /* test1 */ * from ex01;
lno | name
-----+------
1 | 山本
(1 行)
-- pg_stat_statements ビューの確認
--
myposdb=# select a.rolname,d.datname --,s.userid,s.dbid,s.queryid
myposdb-# ,s.calls,s.total_exec_time,s.query
myposdb-# from pg_stat_statements s
myposdb-# inner join pg_roles a on s.userid = a.oid
myposdb-# inner join pg_database d on s.dbid = d.oid
myposdb-# where s.query like '%ex01%';
rolname | datname | calls | total_exec_time | query
---------+---------+-------+-----------------+--------------------------------
sooni | myposdb | 1 | 0.023098 | select /* test1 */ * from ex01
(1 行)
--
-- 再度同じSQLを実行
--
myposdb=# select /* test1 */ * from ex01;
lno | name
-----+------
1 | 山本
(1 行)
--
-- 再度pg_stat_statements ビューの確認
--
myposdb=# select a.rolname,d.datname --,s.userid,s.dbid,s.queryid
myposdb-# ,s.calls,s.total_exec_time,s.query
myposdb-# from pg_stat_statements s
myposdb-# inner join pg_authid a on s.userid = a.oid
myposdb-# inner join pg_database d on s.dbid = d.oid
myposdb-# where s.query like '%ex01%';
rolname | datname | calls | total_exec_time | query
---------+---------+-------+-----------------+--------------------------------
sooni | myposdb | 2 | 0.039187 | select /* test1 */ * from ex01
(1 行)
--
-- 統計情報をクリア
--
myposdb=# select pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 行)
--
-- クリアされている事を確認する
--
myposdb=# select a.rolname,d.datname --,s.userid,s.dbid,s.queryid
myposdb-# ,s.calls,s.total_exec_time,s.query
myposdb-# from pg_stat_statements s
myposdb-# inner join pg_authid a on s.userid = a.oid
myposdb-# inner join pg_database d on s.dbid = d.oid
myposdb-# where s.query like '%ex01%';
rolname | datname | calls | total_exec_time | query
---------+---------+-------+-----------------+-------
(0 行)
myposdb=#