PostgreSQL性能优化：从慢查询定位到配置调优的完整实操

数据库变慢时，很多人第一反应是改参数，但正确顺序是：先用数据找出慢在哪里，再决定是加索引、改SQL还是调配置。
本文按“定位→优化→验证”的流程，给出可直接执行的命令和配置片段，适合刚开始接手PostgreSQL的运维人员。

开始之前：确认环境和准备工作

操作前先确认几件事，避免改错文件或调错参数：

• 确认PostgreSQL版本：psql -V
• 确认配置文件位置：登录数据库后执行 SHOW config_file;，通常输出类似 /etc/postgresql/14/main/postgresql.conf
• 确认当前连接数和数据量级：SELECT count(*) FROM pg_stat_activity;
• 任何参数修改前先备份配置：cp postgresql.conf postgresql.conf.bak
• 涉及重启的操作，建议在业务低峰期进行，并提前确认能接受短暂中断

第一步：用pg_stat_statements找出真正拖慢系统的SQL

凭感觉猜瓶颈容易走偏，先让数据库告诉你哪些SQL最耗时。

启用扩展

编辑 postgresql.conf，在共享预加载库中加入：

shared_preload_libraries = 'pg_stat_statements'

保存后重启PostgreSQL（不同系统命令略有差异，常见的是 systemctl restart postgresql）。
重启完成后，在业务库中执行：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

查看耗时最高的查询

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

重点关注 total_exec_time 高且 calls 也多的语句，这类查询优化收益最大。
如果只想看单次特别慢的，把排序字段换成 mean_exec_time。

第二步：针对慢查询做索引和SQL层面的优化

找到目标SQL后，先用 EXPLAIN ANALYZE 看执行计划：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

常见问题和对应处理：

• 出现 Seq Scan 且表数据量大，考虑在过滤字段上建索引：CREATE INDEX idx_orders_user_id ON orders(user_id);
• 查询条件里对字段做了函数运算，例如 WHERE date(created_at) = '2024-01-01'，会导致索引失效，改成范围查询：WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
• 返回列很多但只用到少数字段，检查是否可以用覆盖索引减少回表
• 索引不是越多越好，写多读少的表加索引会拖慢写入，建之前先评估

建完索引后重新执行 EXPLAIN ANALYZE，观察是否从 Seq Scan 变成 Index Scan，以及执行时间是否下降。

第三步：调整内存相关参数

如果SQL和索引都合理，但整体仍然偏慢，再考虑参数。这些参数改完需要重启或重载配置，改之前记好原值。

• shared_buffers：PostgreSQL缓存数据用的内存，经验上可设为系统内存的25%左右，例如16G内存的机器可尝试 4GB
• work_mem：单个排序或哈希操作可用的内存，设置过小会导致磁盘临时文件，可先设为 64MB 观察
• effective_cache_size：告诉优化器系统大概有多少缓存可用，一般可设为系统内存的50%~75%
• maintenance_work_mem：维护操作（如VACUUM、建索引）使用的内存，可适当调大

修改后执行重载（部分参数需重启）：

systemctl reload postgresql

改完用 SHOW shared_buffers; 确认是否生效。

常见坑和验证方法

几个容易踩的坑：

• 只看CPU不看等待事件，可能真正瓶颈在磁盘IO或锁竞争
• work_mem 设得过大，并发高时会耗尽内存，要结合连接数估算
• 忘了定期执行 VACUUM 和 ANALYZE，统计信息过期会让优化器选错执行计划
• 改了参数不记录，出问题无法回滚

验证优化是否有效，可以从这几个角度复查：

• 重新查询 pg_stat_statements，对比优化前后的 mean_exec_time
• 用 EXPLAIN ANALYZE 确认执行计划变化
• 观察系统层面的CPU、IO、内存使用是否趋于平稳

几个常见疑问

优化一定要重启数据库吗？
不一定。像 work_mem、effective_cache_size 这类参数执行 reload 即可生效；shared_buffers 等需要重启。改之前先查清楚参数属于哪一类。

没有pg_stat_statements怎么办？
可以在业务低峰期临时开启慢查询日志，通过 log_min_duration_statement 记录超过阈值的SQL，但生产环境长期开启会增加日志量，建议按需开启。

索引加了反而更慢是怎么回事？
可能是索引没被优化器选中，或者写入压力增大导致整体变慢。先用 EXPLAIN ANALYZE 确认索引是否真的被使用，再决定是否保留。

PostgreSQL性能优化没有一步到位的万能参数，关键是把“定位→优化→验证”这个循环跑通。
先让数据说话，再动手改，改完一定回头验证，这样才能知道调整是否真的有效。