PostgreSQL 慢查询定位实录：一条 SQL 从 2.3 秒降到 12 毫秒

上周有个订单查询接口的 P99 从 80 毫秒涨到 2.3 秒，应用日志干干净净，监控上 CPU 和内存都不高。翻了半天代码没找到问题，最后把数据库慢查询日志打开，五分钟就锁定了元凶：一条带 LIMIT 的分页 SQL。这篇把定位过程和优化思路完整写下来，都是一线能直接用的手段。

一、先让数据库自己说话

不要靠猜。PostgreSQL 自带两件利器：pg_stat_statements 扩展按 SQL 指纹聚合执行次数、总耗时和平均耗时，慢查询日志则按阈值逐条记录。两者配合，问题 SQL 基本藏不住。

启用方式很简单，先在 postgresql.conf 的 shared_preload_libraries 里加上 pg_stat_statements 并重启，然后建扩展、设阈值：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ALTER SYSTEM SET log_min_duration_statement = '200ms';

SELECT pg_reload_conf();

接着按总耗时排序，前三名通常就是真正的麻烦制造者：

SELECT calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

这里有个容易踩的坑：要按 total_exec_time 排序，而不是 mean_exec_time。平均只要 5 毫秒、但每秒被调用两千次的 SQL，对数据库的伤害远大于偶尔跑三秒的报表查询。优化收益等于单次节省时间乘以调用次数，这一点决定了你该先动哪条 SQL。

二、EXPLAIN 只看三样东西

定位到目标 SQL 后，用 EXPLAIN (ANALYZE, BUFFERS) 看真实执行计划。不加 ANALYZE 只是优化器的估算，加 BUFFERS 才能看到缓存命中和磁盘读取，两个参数缺一不可。看计划我一般只关注三点：

第一，大表上出现 Seq Scan，说明索引没被用上或者压根没有。第二，估算行数 rows 与实际行数 actual rows 差一个数量级以上，说明统计信息过期，优化器选错了路径。第三，shared read 很高而 shared hit 很低，说明数据不在共享缓冲区里，瓶颈在磁盘 IO 而不在 CPU。

我那条 SQL 的计划里，Seq Scan 扫了 42 万行，actual rows 却只有 20 行。原因是 WHERE 里的 user_id 字段是 bigint，而应用传进来的是字符串，PostgreSQL 做了隐式类型转换，索引直接失效。

三、三类高频病因

第一类是隐式类型转换。bigint 对字符串常量、varchar 对整型，都会让 B-Tree 索引失效。这类问题最隐蔽，因为 SQL 语法完全正确，跑起来也不报错，只有看执行计划才能发现。解决办法是把参数类型在应用层对齐，别指望数据库替你兜底。

第二类是统计信息过期。大批量导入或删除之后，pg_statistic 里的数据分布还是旧的，优化器会严重低估或高估行数。手动 ANALYZE 一张表通常就能恢复，但生产环境对大表执行会有短暂的锁竞争，建议放在低峰期，或者调大 autovacuum_analyze_scale_factor 让自动分析更积极一些。

第三类是会让索引白建的写法。在索引列上套函数，比如 where date(created_at) = '2026-11-14'；用 LIKE '%关键字' 做前缀模糊；用 OR 连接不同字段。前者可以改写成范围查询 created_at >= ... and created_at < ...，中者考虑 pg_trgm 的 GIN 索引，后者拆成 UNION ALL 往往更快。

四、索引不是越多越好

索引的本质是用写入成本和磁盘空间换查询速度。每加一个索引，INSERT 和 UPDATE 都多一次维护开销，表膨胀也会更快。我的原则是：组合索引按选择度从高到低排列，把最常用的等值条件放最左边；能靠组合索引覆盖查询字段时就别回表，Index Only Scan 比 Index Scan 快一个量级；单表索引超过六个就该停下来复盘，想想是不是查询模式本身有问题。

再提醒一个分页的细节。ORDER BY 加 LIMIT 的分页，如果偏移量很大，比如 OFFSET 100000，数据库仍然要扫描并丢弃前十万行，耗时随页码线性增长。改成基于游标的分页，用 where id > 上一页最后一个 id order by id limit 20，耗时可以从秒级直接降到毫秒级。这也是我那条 SQL 最终最主要的优化点，配合 user_id 上的组合索引，整体从 2.3 秒降到 12 毫秒。

五、把定位变成日常

优化完并不算结束。把 log_min_duration_statement 长期开着，阈值设 200 到 500 毫秒，配合 pg_stat_statements 每周看一次 Top 20，比等到接口超时再救火划算得多。稳定的系统不是没有慢查询，而是慢查询在变成事故之前就被看见了。

最后补一个血的教训：任何 EXPLAIN ANALYZE 都不要直接在生产主库上跑，尤其是带 UPDATE 或 DELETE 的语句，它会真的执行。稳妥的做法是先开事务再 ROLLBACK，或者干脆在只读从库上验证计划。慢查询排查是个手艺活，工具只是放大器，判断还得靠对数据分布和执行路径的理解。