What is db module pg_stat_statements ?
Why it is requested to enable by clients?
How it is activated?
1. The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.
The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.
2. To implement a view type schema on this table which allows us to monitor queries that take long time. This way clients will be able to tune those and optimize performance.
This extension provides metrics on running queries : which are the most frequently run queries and how long each takes.
$ dpkg -l | grep postgres
If not installed, install it.
$ apt-get install postgresql-contrib-9.3
Default : shared_preload_libraries = ‘’
New : shared_preload_libraries = ‘pg_stat_statements’
– if using both pg_stat_statements and auto_explain, use ONE record and separate with commas
shared_preload_libraries = ‘auto_explain,pg_stat_statements’
– no extension creation required for auto_explain, only for pg_stat_statements
postgres=# \c mydatabase
mydatabase=# CREATE EXTENSION pg_stat_statements;
pg_stat_statements.max = 5000
pg_stat_statements.track = all
pg_stat_statements.save = on
SELECT query, calls, total_time, rows, 100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) AS hit_percent
from pg_stat_statements as s inner join pg_database as d on d.oid = s.dbid
where d.datname = ‘Database_Name’
order by total_time desc limit 5;