部署插件contrib
rpm -ivh postgresql10-contrib-10.10-1PGDG.rhel7.x86_64.rpm --force --nodeps
配置postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all #抓取所有sql
注: 如果不做上述操作,如下操作会报错:pg_stat_statements must be loaded via shared_preload_libraries
创建扩展对象pg_stat_statements
该对象用于存储sql执行相关执行信息,也可以理解为一张表。
create extension pg_stat_statements;
select * from pg_stat_statements;
重启pg
systemctl restart postgresql-10.service
验证查询
SELECT query, calls, total_time, (total_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY average DESC LIMIT 10;
重置查询日志,相当于清空表pg_stat_statements
select pg_stat_statements_reset() ;