PG查询性能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
2、安装配置
1)修改配置文件
# su - postgres $ vi /data/pgsql13/data/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;
4、pg_stat_statements的限制
pg_stat_statements是累积的统计，累积的是pg_stat_statements配置后至当前时刻，无法查询这期间指定时间范围内的Top SQL情况；
可通过如下命令，清理历史统计信息：
SELECT pg_stat_statements_reset();
5、通过pg_stat_statements实现查询指定时间范围内的Top SQL
通过定时清理历史统计信息+定时查询pg_stat_statements的方式可实现查询指定时间范围内的Top SQL；
如在每天0点清理历史统计信息，在每天9、11、17点分别查询pg_stat_statements，可得到每天0～9、0～11、0～17这3个时间范围内的Top SQL。
