pg_stat_statements 属于一个 Extension 模块，用来记录数据库所有的SQL语句的运行信息，这个模块需要设置参数 shared_preload_libraries 的值为 “pg_stat_statements” , 并且需要重启 pg 服务。

pg_stat_statements 模块用于记录 SQL 的运行信息，这个和 Oracle 的视图 v$sql 类似，v$sql 里详细记录了数据库 SQL的运行状态，包括SQL的执行时间，执行次数，逻辑读，解析等信息, 下面介绍下 pg_stat_statements 模块的下载和使用。

pg_stat_statements 视图

从上面看出， pg_stat_statements 视图提供了语句所涉及的行数,执行时间shared_buffer 命中的数据块等信息。

加载 pg_stat_statements 模块

设置 postgresql.conf 模块并重启 PG 服务

1
2
3
4

shared_preload_libraries = 'pg_stat_statements'  
custom_variable_classes = 'pg_stat_statements'  
pg_stat_statements.max = 10000  
pg_stat_statements.track = all

备注：参数 shared_preload_libraries 修改后需要重启PG服务.

加载 pg_stat_statements 模块

1
2

skytf=# create extension pg_stat_statements;  
CREATE EXTENSION

备注：9.1 版本以后，需要使用 “CREATE EXTENSION” 加载外部模块。

使用 pgbench 执行SQL

1

pgbench -c 10 -T 30 -n -M prepared -d skytf -U skytf -f script_1.sql

这里用的是默认的 pgbench 脚本，关于 pgbench 的使用，可以参考之前写的一篇 blog : https://postgres.fun/20110820103909.html

查询执行时间 TOP SQL

查询执行总时间排前五位的 SQL 语句信息

1
2
3
4
5
6
7
8
9
10
11

skytf=# 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 ORDER BY total_time DESC LIMIT 5;  
 query | calls | total_time | rows | hit_percent  
----------------------------------------------------------------------+-------+------------------+------+----------------------  
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | 3119 | 147.078177 | 3119 | 99.9780330821782396  
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | 3119 | 90.9786530000002 | 3119 | 100.0000000000000000  
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | 3119 | 7.25809 | 3119 | 97.8061756156419679  
alter table pgbench_accounts add primary key (aid) | 1 | 7.232948 | 0 | 13.3546405966033716  
vacuum analyze pgbench_accounts | 1 | 7.0277 | 0 | 40.3064903846153846  
(5 rows)

关于 pg_stat_statements_reset() 函数

pg_stat_statements_reset()
pg_stat_statements_reset discards all statistics gathered so far by pg_stat_statements. By default, this function can only be executed by superusers.

备注：上面的解释很明白， pg_stat_statements_reset()函数将会丢弃所有的语句统计信息。

pg_stat_statements_reset 函数测试

1
2
3
4
5
6
7
8
9
10
11
12
13

skytf=# select pg_stat_statements_reset();  
pg_stat_statements_reset  
--------------------------  
  
(1 row)

skytf=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /  
skytf-# nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent  
skytf-# FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;  
 query | calls | total_time | rows | hit_percent  
------------------------------------+-------+------------+------+-------------  
select pg_stat_statements_reset(); | 1 | 0.146981 | 1 |  
(1 row)

备注：在运行了函数 pg_stat_statements_reset() 之后，视图 pg_stat_statements 的信息被清空了。

总结

1. pg_stat_statements 用于分析语句的执行状态，在比较繁忙的生产库上应用对性能分析会有很大帮助;
2. pg_stat_statements 模块加载会消耗部分内存，可以通过 pg_stat_statements.max * track_activity_query_size
   来计算。这个值是比较小的, 假如 pg_stat_statements.max 值为 10000, 也就消耗了 10 M内存。
3. 加载 pg_stat_statements 模块需要重启 PG服务，这点需要注意。

附一: pg_stat_statements 配置参数

pg_stat_statements.max (integer)
pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 1000. This parameter can only be set at server start.

pg_stat_statements.track (enum)
pg_stat_statements.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions), or none to disable. The default value is top. Only superusers can change this setting.

pg_stat_statements.track_utility (boolean)
pg_stat_statements.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE. The default value is on. Only superusers can change this setting.

pg_stat_statements.save (boolean)
pg_stat_statements.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are not saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.