PostgreSQL 性能分析实践

性能问题通常表现为接口变慢、数据库 CPU/IO 升高。本文介绍如何用 PostgreSQL 自带工具快速定位瓶颈并采取行动。

1. 用 EXPLAIN 看执行计划

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ... ;

• Seq Scan 行数很大且代价高时，考虑为过滤列建索引或改写查询。
• Bitmap Heap Scan 常伴随大量堆回表，可看 Buffers: shared hit/read 判断是否多为磁盘读。
• Nested Loop 内表很大且无合适索引时，容易变成“大循环”。应先检查行数估算和 JOIN 键索引，再结合数据规模、可用内存与执行计划评估 Hash Join 等替代方案；不要把增大 work_mem 当作固定解法。

关注 actual time、rows 与 Buffers，找出“实际行数远大于估算行数”的节点，这类节点往往是统计信息过旧或表达式导致估不准，可针对性做 ANALYZE 或建表达式索引。

2. 用 pg_stat 看对象热度

• pg_stat_user_tables：seq_scan、seq_tup_read、idx_scan、idx_tup_fetch。顺序扫描多而索引扫描少时，可考虑加索引或检查查询是否未走索引。
• pg_stat_user_indexes：idx_scan 可看出哪些索引真正被用，长期为 0 的索引可评估是否删除以减轻写入负担。
• pg_stat_statements（需安装扩展）：结合当前 PostgreSQL 版本提供的总执行时间、调用次数和返回行数等字段，找出总耗时高、调用频繁或单次返回行数异常的 SQL，再使用 EXPLAIN 细化分析。较新版本通常使用 total_exec_time，具体字段以对应版本文档为准。

3. 慢查询日志

在 postgresql.conf 中设置例如：

log_min_duration_statement = 1000   # 记录执行时间 > 1s 的语句
log_line_prefix = '%t [%p] '

通过日志找到“谁在什么时候跑了慢 SQL”，再在业务或脚本中复现，用 EXPLAIN 分析并加索引、改 SQL 或调参数。

4. 常见优化动作小结

现象	可做动作
某表 Seq Scan 多	为 WHERE/JOIN 列建索引，或做 ANALYZE
排序/哈希溢出	适当增大 work_mem（会话或单查询）
死元组多、膨胀	调 autovacuum 或对表单独执行 VACUUM/ANALYZE
单条 SQL 很慢	EXPLAIN (ANALYZE, BUFFERS) 看计划，改索引或 SQL
连接数过多	使用连接池，控制 max_connections

更完整的性能调优与内部机制可参考 PostgreSQL 官方文档 中“性能提示”“监控”“例行维护”等部分。