PostgreSQL中高耗SQL的获取可以使用pg_stat_statements模块来获取，pg_stat_statements模块提供执行SQL语句的执行统计信息。
该模块必须在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。增加或移除该模块需要将数据库重启。
当pg_stat_statements被载入时，它会跟踪该服务器的所有数据库的统计信息。该模块提供了视图pg_stat_statements以及函数pg_stat_statements_reset用于访问和操纵这些统计信息。这些视图和函数不是全局可用的，但是可以在指定数据库创建该扩展。
1. 创建扩展模块
创建extension模块
postgres=# CREATE EXTENSION pg_stat_statements; CREATE EXTENSION |
2. 配置postgresql.conf参数文件
修改数据库PG_HOME下的postgresql.conf文件
shared_preload_libraries= 'pg_stat_statements'
pg_stat_statements.max= 10000 #pg_stat_statements中记录的最大的SQL条目数，默认为5000
pg_stat_statements.track= all#记录pg_stat_statements中的
pg_stat_satements.saveon #用来控制数据库在关闭的时候，是否将SQL信息保存到文件中。默认打开
pg_stat_satements.track_utilityon #追踪SQL命令：DQLDDL 以及DQL,DDL以外的其他SQL命令(off只记录DQLDDL)
如果没有配置postgresql.conf文件中的shared_preload_libraries,那么将会提示如下报错：
ERROR:pg_stat_statements must be loaded via shared_preload_libraries
3. 重启数据库
使用pg_ctl重新启动数据库，使扩展生效。
pg_ctl start -D $PGDATA -l tmp/pg_rotate_logfile() |
4. 验证
进入数据库，查看pg_stat_statements视图，有数据则安装成功。
psql -U postgres -d pgtestdb select * from pg_stat_statements; |
5. pg_stat_statements视图结构
该视图的结构信息如下：
由于安全性原因，只有超级用户和pg_read_all_stats角色的成员被允许看到其他用户执行的查询的SQL文本或者queryid。
6. pg_stat_statements使用
log_min_duration_statement这个参数可以控制阈值的时间，如果查询花费的时间长于此阈值时间，则会记录该SQL。默认为1s。可以使用
ALTER SYSTEM SETlog_min_duration_statement = 1000;
更改阈值记录，单位为ms。
6.1、SQL执行时间获取
我们可以在数据库中看到平均运行时间最高的查询，如下所示：
SELECT total_time, min_time,(total_time/calls) as avg_time, max_time, mean_time, calls, rows,query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; |
返回结果如下：
其中各项的涵义：
total_time：返回查询的总运行时间（以毫秒为单位）。
min_time、avg_time和max_time：返回查询的最小、平均和最大运行时间。
mean_time：使用total_time/调用返回查询的平均运行时间（以毫秒为单位）。
Calls (调用)：返回查询运行的总数。
Rows(行数)：返回由于查询而返回或受影响的行总数。
Query(查询)：返回正在运行的查询。默认情况下，最多显示1024个查询字节。可以使用track_activity_query_size参数更改此值。
7、重置pg_stat_statements统计信息
pg_stat_statements所获得的统计数据一直累积到重置。
可以使用以下脚本进行按天备份。
备份完成后可以通过具有超级用户权限的用户连接到数据库以重置统计数据来运行重置：
SELECTpg_stat_statements_reset();
#!/bin/bash # this script is aimed to delete the expired data; # and use the vacummdb command to clean up databases. # Copyright(c) 2016--2016 yuxiangli All Copyright reserved. echo "-----------------------------------------------------" echo `date +%Y%m%d%H%M%S` dates= `date +%Y%m%d` psql -U hbdx_xxx -h 133.0.xxx.xx -d testdb -p xxx << EOF create table public.pg_stat_statements_$dates as select * from public.pg_stat_statements; SELECT pg_stat_statements_reset(); \q EOF echo "-----------------------------------------------------" echo `date +%Y%m%d%H%M%S` echo "-----------------------------------------------------" |
