PostgreSQL 慢查询定位与执行计划优化实战
PostgreSQL 慢查询定位与执行计划优化实战一、具体的问题
业务反馈"订单列表页打开要十几秒"，前几天还好好的。连上去看 top，CPU 不高，磁盘 IO 也不算高，连接池却堆了几十个活跃会话。问开发最近改了什么，回答是"就加了个查询条件"。
这种场景在 PG 上特别常见，而且排查起来有几个绕不开的坑：
[*]PG 默认没有像 Oracle AWR 那样开箱即用的历史性能视图。pg_stat_statements 不装就是个空壳，pg_stat_activity 只给"此刻"。慢的时候你不在，等你在了它又不慢了，全靠撞运气；
[*]EXPLAIN 和 EXPLAIN ANALYZE 是两个东西。只看前者拿到的是"优化器打算怎么干"的估算，rows=1200 可能实际是 118432。拿着估算去改 SQL，方向一开始就错了；
[*]慢不一定是 SQL 写得差。可能是统计信息过期、可能表膨胀了、也可能根本就是被别的会话堵着。方向没找对，改 SQL 改到天亮也没用。
我手上这个库的最终结论是：SQL 本身没变慢，是 orders 表连续跑了三周大批量更新，n_dead_tup 堆到 38 万，autovacuum 按默认 20% 的阈值根本追不上，索引膨胀到 2.1 倍，一次索引扫描要读 15802 个块，其中 15600 个是物理读。
本文按"先抓现行 → 再看计划 → 最后改"这条线走，给出能直接照抄执行的命令和治理前后的实测对比。
二、核心原理
1. 慢的三种来源，处理方式完全不同
[*]执行慢（真的算得慢）：计划选错、索引没用上、数据量涨了。看 mean_exec_time；
[*]等待慢（被别人堵着）：锁等待、IO 等待。看 pg_stat_activity 的 wait_event_type；
[*]重复执行（单条不慢，总量大）：一条 20ms 的 SQL 一天跑两千万次，单次看毫无异常，总量能把库拖死。看 total_exec_time。
这三类对应的动作完全不同：第一种改 SQL 和索引，第二种杀会话或改事务边界，第三种改调用频次或加缓存。不分清就动手，是最常见的白费功夫。
2. 优化器靠统计信息做判断
PG 用的是代价模型，几个关键参数：seq_page_cost（默认 1.0）、random_page_cost（默认 4.0）、cpu_tuple_cost（0.01）、effective_cache_size。优化器拿 pg_class.reltuples 和 pg_stats 里的直方图估算返回行数，再算出每条路径的 cost，选最小的。
两个必须记住的点：
[*]统计信息过期 = 估算偏差 = 计划选错。大批量 DML 之后不 ANALYZE，优化器还在用三天前的行数做判断；
[*]random_page_cost = 4.0 是机械盘时代的默认值。SSD/NVMe 上随机读和顺序读差别很小，还按 4.0 算，优化器会系统性地高估索引扫描代价，从而过度偏好全表扫描。这一条参数改对，能白捡一大截性能。
3. 索引什么时候会失效（PG 上的几条典型）
[*]列被函数包裹：WHERE date(create_time) = '2026-09-01'。btree 索引里存的是 create_time 原值，函数算出来的结果对不上，只能全表扫；
[*]前导通配符：WHERE name LIKE '%科技%'。btree 按前缀排序，前缀不确定就没法定位起点；
[*]复合索引前导列缺失：索引 (a, b) 能加速 a = ? 和 a = ? AND b = ?，单独 b = ? 用不上（PG 没有 index skip scan）；
[*]选择性太差：gender、status 这类低基数列，走索引回表的代价可能高于顺序扫，优化器主动放弃索引。这是对的，别硬加索引，也别用 enable_seqscan = off 去骗它。前三种才是真失效，这一种不是。
4. 膨胀与 autovacuum
MVCC 之下，UPDATE 是"插入新版本 + 标记旧版本删除"，DELETE 只是打标记。这些 dead tuple 靠 autovacuum 回收。默认触发阈值是 autovacuum_vacuum_scale_factor = 0.2，也就是死元组超过表的 20% 才动手——对一张 500 万行的大表来说，就是攒够 100 万才清理，早就膨胀了。
膨胀之后，表和索引占用的页数变多，顺序扫要读更多块，缓存命中率下降，物理读飙升。这类"慢"在 EXPLAIN (ANALYZE, BUFFERS) 里表现为 Buffers: shared read= 特别高。
三、实例参考（动手步骤）
1. 先把"行车记录仪"装上（一次性动作）
改 postgresql.conf，需要重启实例：
shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
重启后在业务库建扩展：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
auto_explain 的价值在于：慢 SQL 发生时你不在场，它已经把带 ANALYZE 和 BUFFERS 的真实计划写进日志了。log_min_duration 别设太小，否则日志会炸。
2. 谁最耗时间
SELECT substring(query, 1, 120) AS q,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
round((shared_blks_hit * 100.0
/ nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS hit_pct
FROM pg_stat_statements
WHEREquery NOT LIKE '%pg_stat_statements%'
ORDERBY total_exec_time DESC
LIMIT10;
PG 12 及更早版本把 total_exec_time / mean_exec_time 换成 total_time / mean_time。
三列怎么读：total_ms 高说明是总量问题（要么次数多，要么单条慢）；mean_ms 高说明单条真慢；hit_pct 低于 95% 说明在读磁盘，先查缓存和膨胀，别急着改 SQL。
重置计数用 SELECT pg_stat_statements_reset();，做前后对比前先重置，数据才干净。
3. 此刻谁在堵（第一现场）
SELECT pid,
usename,
state,
wait_event_type,
wait_event,
now() - query_start AS dur,
pg_blocking_pids(pid) AS blocked_by,
left(query, 80) AS q
FROM pg_stat_activity
WHEREstate <> 'idle'
AND pid <> pg_backend_pid()
ORDERBY dur DESC;
blocked_by 非空就是被堵，数组里的 pid 是堵它的人（PG 9.6+ 才有 pg_blocking_pids）。wait_event_type = 'Lock' 但 blocked_by 为空，通常是 advisory lock 或者等 VACUUM；wait_event_type = 'IO' 说明瓶颈在存储。
确认要终止时优先用 SELECT pg_cancel_backend(pid);（只取消查询），不行再 SELECT pg_terminate_backend(pid);（断开连接）。杀之前一定确认 pid 不是 walsender / 逻辑复制 / 备份进程。
4. 拿到真实执行计划
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE status = 'NEW' AND create_time >= '2026-09-01';
重点读三处，以这行为例：
Seq Scan on orders(cost=0.00..18342.00 rows=1200 width=48)
(actual time=0.030..142.220 rows=118432 loops=1)
Filter: (status = 'NEW'::text)
Rows Removed by Filter: 117232
Buffers: shared hit=210 read=15802
[*]rows 估算 1200，实际 118432，差了 98 倍——这是计划选错的根子，先 ANALYZE 再看；
[*]Rows Removed by Filter: 117232——扫了 11.8 万行扔掉 11.7 万，说明过滤条件没有可用的索引，或者索引选择性太差；
[*]Buffers: shared hit=210 read=15802——几乎全是物理读，指向缓存不足或表/索引膨胀。
铁律：估算行数与实际相差一个数量级以上时，先 ANALYZE 相关表再重新看计划，不要直接改 SQL。
5. 四个典型修复（附前后对比）
（1）函数包裹列 → 改写成范围查询
-- 慢：索引完全用不上
SELECT * FROM orders WHERE date(create_time) = '2026-09-01';
-- 快：改成半开区间，走 create_time 的 btree 索引
SELECT * FROM orders
WHEREcreate_time >= '2026-09-01' AND create_time < '2026-09-02';
CREATE INDEX idx_orders_ct ON orders (create_time);
-- SQL 实在改不了，退而求其次建表达式索引
CREATE INDEX idx_orders_ct_day ON orders (date(create_time));
（2）前导通配符 → pg_trgm + GIN
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_cust_name_trgm ON customer USING gin (name gin_trgm_ops);
SELECT * FROM customer WHERE name LIKE '%科技%';
注意 trgm 索引对短字符串（少于 3 个字符）效果差，中文场景要实测，别想当然。
（3）优化器死活不走索引 → 校准代价参数
ALTER SYSTEM SET random_page_cost = 1.1; -- SSD / NVMe 环境
ALTER SYSTEM SET effective_cache_size = '8GB';-- 一般设物理内存的 50%~75%
SELECT pg_reload_conf();
改完再跑一次 EXPLAIN (ANALYZE, BUFFERS) 验证，不要改完就当完事。
（4）膨胀 → 调 autovacuum 或手动回收
SELECT relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHEREn_live_tup > 10000
ORDERBY n_dead_tup DESC
LIMIT10;
对热点大表单独立调阈值：
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 10);
VACUUM (ANALYZE, VERBOSE) orders;
VACUUM 只标记空间可复用，不把空间还给操作系统；要真正收缩文件得用 VACUUM FULL，它会排他锁表并重建，必须放维护窗口，表大的时候可能锁几十分钟。
6. 治理前后实测对比
同一套环境、同一批 SQL，治理前后的实测数据（示例环境，仅供参考量级）：
对比项治理前治理后
函数包裹 create_time 的查询Seq Scan 118432 行 / 142 msIndex Scan 1240 行 / 1.8 ms
LIKE '%科技%' 全表扫描Seq Scan / 860 msBitmap Index Scan / 6.4 ms
random_page_cost 4.0 → 1.1Seq Scan / 210 msIndex Scan / 3.1 ms
orders 表 dead_pct38.6%1.2%
该 SQL 的 Buffers: shared read15802196
列表页 P95 响应时间12.7 秒0.42 秒
最后一行是业务能感知的结果，前几行是支撑它的证据。做优化汇报的时候，两类数据都要有。
四、实操检查清单
[*][ ] 确认 shared_preload_libraries 已含 pg_stat_statements，且扩展已在业务库创建（auto_explain 建议一并开启）。
[*][ ] auto_explain.log_min_duration 设 1 秒左右；不要设成 0，否则日志文件会被慢查询灌满。
[*][ ] 每天固定时间把 pg_stat_statements 的 top 10 快照落表留存——这些是累计值，实例重启就清零，不自己存等于没有。
[*][ ] 排查固定顺序：先看 pg_stat_activity 有没有阻塞 → 再看 pg_stat_statements 定位目标 → 最后 EXPLAIN ANALYZE。不要反过来。
[*][ ] EXPLAIN 必须带 ANALYZE 和 BUFFERS，只看 EXPLAIN 拿到的估算不可信。
[*][ ] 估算行数与实际行数相差 10 倍以上，先 ANALYZE 相关表，再谈改 SQL 或加索引。
[*][ ] 检查目标 SQL 的索引列是否被函数包裹（如 date(col)、upper(col)、col::text）。
[*][ ] 检查复合索引的前导列是否出现在查询条件里，(a, b) 挡不住单独的 b = ?。
[*][ ] LIKE '%x%' 场景确认有没有 pg_trgm + GIN 索引，并实测中文短词的命中效果。
[*][ ] 确认 random_page_cost 是否按 SSD 调整过（默认 4.0 是机械盘时代的值）、effective_cache_size 是否按内存设过。
[*][ ] n_dead_tup / n_live_tup 超过 20% 的表列入膨胀治理名单，热点大表单独调 autovacuum_vacuum_scale_factor。
[*][ ] 需要回收空间时用 VACUUM FULL，必须放维护窗口；日常只用 VACUUM (ANALYZE)。
[*][ ] 生产上执行 pg_terminate_backend 前，确认 pid 不是 walsender、逻辑复制或备份进程。
[*][ ] 改完必须回测：同一条 SQL 再跑一次 EXPLAIN (ANALYZE, BUFFERS)，确认 actual time 和 Buffers 真的降了，而不是"看起来应该快了"。
[*][ ] 建立基线：记录日常数据库的缓存命中率、top SQL 的 mean 值、活跃会话数，作为告警阈值依据，别拍脑袋定数字。
页:
[1]