Campaign Classic v7 - Purpose and usage of db module pg_stat_statements

What is db module pg_stat_statements?

Why it is requested to enable by clients?

How it is activated?

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.

Link https://www.postgresql.org/docs/9.4/pgstatstatements.html

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.

pg_stat_statements

1. Verify that official PostgreSQL contrib package for the postgres version is installed.

   code language-none
   $ dpkg -l | grep postgres postgresql-contrib-9.3

   If not installed, install it.

   code language-none
   $ apt-get install postgresql-contrib-9.3

2. Change postgresql.conf to preload the module on startup.

   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.

   code language-none
   shared_preload_libraries = 'auto_explain,pg_stat_statements'

3. In the database that you want to monitor, execute CREATE query:

   No extension creation required for auto_explain, only for pg_stat_statements.

   code language-none
   psql postgres=# \c mydatabase mydatabase=# CREATE EXTENSION pg_stat_statements;

4. In the CUSTOMIZED OPTIONS section of postgresql.conf, add the lines:

   code language-none
   pg_stat_statements.max = 5000 pg_stat_statements.track = all pg_stat_statements.save = on

5. Restart the postgresql service.

Example: Get top 5 most costly queries in the databases.

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;