作者：太阳

一、查问以后正在运行的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_sqlFROM pg_stat_activity pgsaWHERE pgsa.state != 'idle'    AND pgsa.state != 'idle in transaction'    AND pgsa.state != 'idle in transaction (aborted)'ORDER BY query_time DESCLIMIT 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/postgresql.confshared_preload_libraries='pg_stat_statements,pg_pathman'        pg_stat_statements.max = 10000pg_stat_statements.track = all2)重启失效：$ pg_ctl -D /data/pgsql13/data restart

3)载入pg_stat_statement插件

postgres=# \xExpanded display is on.--查看可用模块postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';-[ RECORD 1 ]-----+-----------------------------------------------------------------------name              | pg_stat_statementsdefault_version   | 1.8installed_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                //用户oiddbid                | 163959                //数据库oidqueryid             | -7584655433466348220            //查问idquery               | 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        //执行语句所破费的总工夫，单位msmin_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 | 10userid | postgres...

查问Top SQL：

-- 按总执行工夫查问Top SQLselect userid::regrole as user_name,* from pg_stat_statements order by total_exec_time desc limit 20;-- 按总IO耗费查问Top SQLselect userid::regrole as user_name,* from pg_stat_statements order by blk_read_time+blk_write_time desc limit 20;-- 按总调用次数查问Top SQLselect 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。

更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw