性能分析利器：pg_stat_statements介绍

pg_stat_statements Columns

userid oid (references pg_authid.oid)

dbid oid (references pg_database.oid)
 
queryid bigint
 

query text:查询sql

plans bigint
语句被plan的次数（如果pg_stat_statements.track_planning被启用，否则为0）。

total_plan_time double precision

计划语句所花费的总时间，以毫秒为单位（如果pg_stat_statements.track_planning被启用，否则为0）。


total_exec_time double precision

执行该语句的总时间，以毫秒计
mean_exec_time double precision

执行该语句的平均时间，以毫秒计


rows bigint
检索到的或受语句影响的总行数

shared_blks_hit bigint（语句的共享块缓存hit总数）

shared_blks_read bigint （未命中块数量） shared_blks_hit/(shared_blks_hit+shared_blks_read)可计算出命中率

shared_blks_dirtied bigint（使shared buffer脏的块数量）

Total number of shared blocks dirtied by the statement

shared_blks_written bigint 

Total number of shared blocks written by the statement

local_blks_hit bigint

Total number of local block cache hits by the statement(临时表产生的)

local_blks_read bigint

Total number of local blocks read by the statement(临时表产生的)

local_blks_dirtied bigint

Total number of local blocks dirtied by the statement(临时表产生的)

local_blks_written bigint

Total number of local blocks written by the statement(临时表产生的)

temp_blks_read bigint

Total number of temp blocks read by the statement(distinct sort groupby  hashjoin 产生临时文件)

temp_blks_written bigint

Total number of temp blocks written by the statement(distinct sort groupby  hashjoin 产生临时文件)

blk_read_time double precision

Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time double precision

Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

wal_records bigint

Total number of WAL records generated by the statement

wal_fpi bigint

Total number of WAL full page images generated by the statement

wal_bytes numeric

Total amount of WAL generated by the statement in bytes

总体时间情况

SELECT sum(total_time) AS total_time,
sum(blk_read_time + blk_write_time) AS io_time,
sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
sum(calls) AS ncalls,
sum(rows) AS total_rows
FROM pg_stat_statements;

cpu开销top

WITH tpsql AS (
SELECT sum(total_time) AS total_time, sum(blk_read_time + blk_write_time) AS io_time,
sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
sum(calls) AS ncalls, sum(rows) AS total_rows
FROM pg_stat_statements
)
SELECT query,(pss.total_time-pss.blk_read_time-pss.blk_write_time)/tpsql.cpu_time*100 cpu_pct
FROM pg_stat_statements pss, tpsql
WHERE (pss.total_time-pss.blk_read_time-pss.blk_write_time)/tpsql.cpu_time >= 0.05
ORDER BY pss.total_time-pss.blk_read_time-pss.blk_write_time DESC LIMIT 5;

通过修改这条查询语句，你可以定义你的自定义筛选条件，从而获得TOP SQL。其他的一些常用分析语句如下表：

TOP SQL查找功能
单次调用最消耗IO SQL TOP:
select query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10; 

IO开销总量最大的 SQL TOP:
select query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10; 

单次平均调用最耗时 SQL TOP:
select query from pg_stat_statements order by mean_time desc limit 10;

执行耗时最大的 SQL TOP:
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 10;

最大执行时长与平均执行时长差异最大的TOP SQL
select userid::regrole, dbid, query from pg_stat_statements order by (max_time-mean_time) desc limit 10;

执行时间抖动最严重的TOP SQL
select query from pg_stat_statements order by stddev_time desc limit 10;

最耗共享内存TOP SQL
select query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10; 

最耗临时空间TOP SQL
select query from pg_stat_statements order by temp_blks_written desc limit 10; 

执行次数最多的TOP SQL
select query from pg_stat_statements order by calls desc limit 10; 

共享池命中率最低的TOP SQL
select query from pg_stat_statements order by (1-shared_blks_hit/(shared_blks_hit+shared_blks_read )) desc limit 10; 

Configuration Parameters

pg_stat_statements.max (integer)
pg_stat_statements.max是模块所追踪的最大语句数（即pg_stat_statements视图中的最大行数）。如果观察到比这更多的独立语句，关于最少执行的语句的信息将被丢弃。默认值是5000。这个参数只能在服务器启动时设置。通常设置为10000

pg_stat_statements.track (enum):top all none
pg_stat_statements.track控制模块对哪些语句进行统计。指定top来跟踪顶层语句（那些由客户直接发出的语句），all来跟踪嵌套语句（例如在函数中调用的语句），或者none来禁用语句统计收集。默认值是top。只有超级用户可以改变这个设置。

pg_stat_statements.track_utility (boolean)
pg_stat_statements.track_utility控制模块是否跟踪实用命令。实用命令是指除SELECT、INSERT、UPDATE和DELETE以外的所有命令。默认值是开。只有超级用户可以改变这个设置。

pg_stat_statements.track_planning (boolean)
pg_stat_statements.track_planning控制模块是否跟踪执行计划操作和持续时间。启用这个参数可能会产生明显的性能损失，特别是当具有相同查询结构的语句被许多并发连接执行时，这些连接争相更新少量的pg_stat_statements条目。默认值是关闭。只有超级用户可以改变这个设置。

pg_stat_statements.save (boolean)
pg_stat_statements.save指定是否在服务器关闭时保存语句统计。如果它是关闭的，那么统计数据在关机时不被保存，在服务器启动时也不被重新加载。默认值是开。这个参数只能在postgresql.conf文件或服务器命令行中设置。

Functions

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void
pg_stat_statements_reset丢弃到目前为止由pg_stat_statements收集的与指定的userid、dbid和queryid相对应的统计数据。

pg_stat_statements(showtext boolean) returns setof record
The pg_stat_statements view is defined in terms of a function also named pg_stat_statements. It is possible for clients to call the pg_stat_statements function directly, and by specifying showtext := false have query text be omitted (that is, the OUT argument that corresponds to the view’s query column will return nulls). This feature is intended to support external tools that might wish to avoid the overhead of repeatedly retrieving query texts of indeterminate length. Such tools can instead cache the first query text observed for each entry themselves, since that is all pg_stat_statements itself does, and then retrieve query texts only as needed. Since the server stores query texts in a file, this approach may reduce physical I/O for repeated examination of the pg_stat_statements data.