PostgreSQL pg_stat_statements 查询分析与优化

启用（postgresql.conf）

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = all

查询统计

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

优化路径

• 针对高 calls 与高 mean_time 的语句建立索引或改写
• 结合 EXPLAIN (ANALYZE, BUFFERS) 验证计划与 IO

总结

pg_stat_statements 能有效定位性能热点，为持续优化提供依据。