PostgreSQL开发与实战（4）查询性能Top SQL

作者：太阳

一、查询当前正在运行的Top SQL

查询当前正在运行的会话中耗时最长的Top SQL，where条件可按需修改

SELECT pgsa.datname AS database_name
    , pgsa.usename AS user_name
    , pgsa.client_addr AS client_addr
    , pgsa.application_name AS application_name
    , pgsa.state AS state
	, pgsa.backend_start AS backend_start
	, pgsa.xact_start AS xact_start
	, extract(epoch FROM now() - pgsa.xact_start) AS xact_time, pgsa.query_start AS query_start
	, extract(epoch FROM now() - pgsa.query_start) AS query_time
	, pgsa.query AS query_sql
FROM pg_stat_activity pgsa
WHERE pgsa.state != 'idle'
	AND pgsa.state != 'idle in transaction'
	AND pgsa.state != 'idle in transaction (aborted)'
ORDER BY query_time DESC
LIMIT 20;

pg_stat_activity视图各字段含义：http://postgres.cn/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

二、查询某个时间点之后的Top SQL

1、pg_stat_statements介绍

pg_stat_statements可跟踪服务器执行的所有SQL语句的计划信息和执行统计信息，详见官方文档说明：[http://postgres.cn/docs/13/pgstatstatements.html](http://postgres.cn/docs/13/pgstatstatements.html)

2、安装配置

1)修改配置文件

# su - postgres
$ vi /data/pgsql13/data/sql>postgresql.conf
shared_preload_libraries='pg_stat_statements,pg_pathman'        
pg_stat_statements.max = 10000
pg_stat_statements.track = all
2)重启生效：

$ pg_ctl -D /data/pgsql13/data restart

3)载入pg_stat_statement插件

postgres=# \x
Expanded display is on.

--查看可用模块
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
-[ RECORD 1 ]-----+-----------------------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.8
installed_version | 
comment           | track planning and execution statistics of all SQL statements executed

--载入模块，载入后pg_stat_statements表可正常使用
postgres=# create extension pg_stat_statements; 
CREATE EXTENSION

3、pg_stat_statements各字段含义

postgres=# select * from  pg_stat_statements limit 1;
-[ RECORD 1 ]-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
userid              | 16480				//用户oid
dbid                | 163959				//数据库oid
queryid             | -7584655433466348220	        //查询id
query               | SELECT ......			//SQL语句
plans               | 0 				//计划语句的次数，单位ms(启用pg_stat_statements.track_planning才会统计，默认关闭)
total_plan_time     | 0 				//计划语句所花费的总时间，单位ms(启用pg_stat_statements.track_planning才会统计，默认关闭)
min_plan_time       | 0 				//计划语句所花费的最短时间，单位ms(启用pg_stat_statements.track_planning才会统计，默认关闭)
max_plan_time       | 0 				//计划语句所花费的最长时间，单位ms(启用pg_stat_statements.track_planning才会统计，默认关闭)
mean_plan_time      | 0 				//计划语句所花费的平均时间，单位ms(启用pg_stat_statements.track_planning才会统计，默认关闭)
stddev_plan_time    | 0 				//计划语句花费的时间的总体标准偏差，单位ms(启用pg_stat_statements.track_planning才会统计，默认关闭)
calls               | 92545				//语句被执行的次数
total_exec_time     | 1563.9699899999862		//执行语句所花费的总时间，单位ms
min_exec_time       | 0.005605			        //执行语句所花费的最短时间，
max_exec_time       | 7.055763			        //执行语句所花费的最长时间，
mean_exec_time      | 0.01689956226700567		//执行语句所花费的最长时间，
stddev_exec_time    | 0.036137014177393116	        //执行语句花费的时间的总体标准偏差，
rows                | 17277				//语句检索或影响的总行数
shared_blks_hit     | 394706				//语句的共享块缓存命中总数
shared_blks_read    | 7 				//语句读取的共享块总数
shared_blks_dirtied | 6 				//被语句弄脏的共享块总数
shared_blks_written | 0 				//语句写入的共享块总数
local_blks_hit      | 0 				//语句的本地块缓存命中总数
local_blks_read     | 0 				//语句读取的本地块总数
local_blks_dirtied  | 0 				//被语句弄脏的本地块总数
local_blks_written  | 0 				//语句写入的本地块总数
temp_blks_read      | 0 				//语句读取的临时块总数
temp_blks_written   | 0 				//语句写入的临时块总数
blk_read_time       | 0 				//语句读取块所花费的总时间
blk_write_time      | 0 				//语句写入块所花费的总时间
wal_records         | 7874 				//语句生成的 WAL 记录总数
wal_fpi             | 5 				//语句生成的 WAL 整页图像总数
wal_bytes           | 450177 				//语句生成的 WAL 字节总数

oid是唯一标识，查询用户名与用户oid的关系：

postgres=# select userid,userid::regrole from pg_stat_statements group by userid;
-[ RECORD 1 ]----
userid | 10
userid | postgres
...

查询Top SQL：

-- 按总执行时间查询Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by total_exec_time desc limit 20;
-- 按总IO消耗查询Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by blk_read_time+blk_write_time desc limit 20;
-- 按总调用次数查询Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by calls desc limit 20;