The pg_stat_states module provides a way to track execution statistics for all SQL statements executed by a server.
The module must be loaded by adding pg_stat_states to shared_preload_libraries of postgresql.conf because it requires additional shared memory. This means that adding or removing the module requires a server reboot.
When pg_stat_states are loaded, it tracks all database statistics for the server. The module provides a view pg_stat_states and functions pg_stat_statements_reset and pg_stat_states for accessing and manipulating these statistics. These views and functions are not globally available, but they can be enabled for specific databases using CREATE EXTENSION pg_stat_states.
apt, yum installation
You need to install the same version of contrib package, for example, ubuntu.
# apt install postgresql-contrib-9.6 # dpkg -l |grep -i postgresql-contrib-9.6 ii postgresql-contrib-9.6 9.6.8-1.pgdg16.04+1 amd64 additional facilities for PostgreSQL
Source Code Compilation and Installation
When compiling and installing, if it is in the following way, it is already installed.
$ make world $ make install-world
Otherwise, you need to go into the source code under contrib pg_stat_statements\ to compile and install separately
$ cd /tmp/postgresql-9.6.8\contrib\pg_stat_statements\ $ make $ make install
Create pg_stat_statements
Since pg_stat_states are used both as hook s and as extension s, two things need to be done:
1. Restart postgresql after modifying the shared_preload_libraries value of postgrsql.conf.
$ vi postgresql.conf shared_preload_libraries = 'pg_stat_statements' # (change requires restart) # systemctl stop postgresql # systemctl start postgresql
2. Create extension
Since pg_stat_states are for database level, you need to first enter the specified database.
postgres=# \c peiybdb peiybdb=# create extension pg_stat_statements；
Once created, one view pg_stat_statements and two functions pg_stat_statements_reset are added.
peiybdb=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
public | pg_stat_statements | SETOF record | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double precision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision | normal
public | pg_stat_statements_reset | void | | normal
(2 rows)
peiybdb=# \df+ pg_stat_statements
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
--------+--------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+----------+----------+----------+-------------------+----------+------------------------+-------------
public | pg_stat_statements | SETOF record | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double precision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision | normal | volatile | safe | postgres | invoker | | c | pg_stat_statements_1_3 |
(1 row)
peiybdb=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | pg_stat_statements | view | postgres
peiybdb=# \d+ pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Modifiers | Storage | Description
---------------------+------------------+-----------+----------+-------------
userid | oid | | plain |
dbid | oid | | plain |
queryid | bigint | | plain |
query | text | | extended |
calls | bigint | | plain |
total_time | double precision | | plain |
min_time | double precision | | plain |
max_time | double precision | | plain |
mean_time | double precision | | plain |
stddev_time | double precision | | plain |
rows | bigint | | plain |
shared_blks_hit | bigint | | plain |
shared_blks_read | bigint | | plain |
shared_blks_dirtied | bigint | | plain |
shared_blks_written | bigint | | plain |
local_blks_hit | bigint | | plain |
local_blks_read | bigint | | plain |
local_blks_dirtied | bigint | | plain |
local_blks_written | bigint | | plain |
temp_blks_read | bigint | | plain |
temp_blks_written | bigint | | plain |
blk_read_time | double precision | | plain |
blk_write_time | double precision | | plain |
View definition:
SELECT pg_stat_statements.userid,
pg_stat_statements.dbid,
pg_stat_statements.queryid,
pg_stat_statements.query,
pg_stat_statements.calls,
pg_stat_statements.total_time,
pg_stat_statements.min_time,
pg_stat_statements.max_time,
pg_stat_statements.mean_time,
pg_stat_statements.stddev_time,
pg_stat_statements.rows,
pg_stat_statements.shared_blks_hit,
pg_stat_statements.shared_blks_read,
pg_stat_statements.shared_blks_dirtied,
pg_stat_statements.shared_blks_written,
pg_stat_statements.local_blks_hit,
pg_stat_statements.local_blks_read,
pg_stat_statements.local_blks_dirtied,
pg_stat_statements.local_blks_written,
pg_stat_statements.temp_blks_read,
pg_stat_statements.temp_blks_written,
pg_stat_statements.blk_read_time,
pg_stat_statements.blk_write_time
FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);
The pg_stat_statements_reset() function can be used to reset the pg_stat_statements to facilitate the analysis of slow sql in stages, such as special optimization and large version online monitoring.
peiybdb=# select pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
peiybdb=#
peiybdb=#
peiybdb=# select count(1) from pg_stat_statements;
count
-------
1
(1 row)
Reference resources:
http://postgres.cn/docs/9.6/pgstatstatements.html
https://blog.csdn.net/ctypyb2002/article/details/77711802