pgsql使用积累系列_安装pg_stat_statements获取慢sql

 

1. 安装postgresql(10.1)

参考链接：postgresql10安装

2. 配置postgresql.conf文件

打开配置文件找到shared_preload_libraries，添加pg_stat_statements，配置好要重启pgsql。如下图所示

3. 连接指定数据库，并安装pg_stat_statements插件

psql -p 5433 -d pgtestdb -U admin

create extension pg_stat_statements

如下图所示：

4. 查看sql

重置：

select pg_stat_reset();
select pg_stat_statements_reset();

执行要分析的sql:

update rccl_passenger set is_guide = 1
from (select pax_id from rccl_group_leader) t
where rccl_passenger.guest_id = t.pax_id::int and rccl_passenger.synctime > current_date;

update rccl_passenger set is_guide = 1
from (select phone from rccl_external_gourp_leader) t
where rccl_passenger.phone = t.phone and rccl_passenger.synctime > current_date;

......

获取执行最慢的top5 sql
select * from pg_stat_statements order by total_time desc limit 5;

获取最耗资源的top5 sql

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

分析慢sql:

PostgreSql的Explain命令详解 toplchx这篇文章关于执行计划分析比较详细

EXPLAIN (ANALYZE,Buffers) update rccl_passenger set is_guide = 1
from (select pax_id from rccl_group_leader) t
where rccl_passenger.guest_id = t.pax_id::int and rccl_passenger.synctime > current_date

优化sql:

加索引，调内存配置等。