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:
加索引,调内存配置等。