PostgreSQL 慢查询定位与执行计划优化实战

dbaai

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，需要重启实例：

2. shared_preload_libraries = 'pg_stat_statements,auto_explain'
3. pg_stat_statements.max = 10000
4. pg_stat_statements.track = all
5. auto_explain.log_min_duration = '1s'
6. auto_explain.log_analyze = on
7. auto_explain.log_buffers = on
8. auto_explain.log_nested_statements = on

复制代码

重启后在业务库建扩展：

2. CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

复制代码

auto_explain 的价值在于：慢 SQL 发生时你不在场，它已经把带 ANALYZE 和 BUFFERS 的真实计划写进日志了。log_min_duration 别设太小，否则日志会炸。

2. 谁最耗时间

2. SELECT substring(query, 1, 120)                                    AS q,
3.        calls,
4.        round(total_exec_time::numeric, 1)                           AS total_ms,
5.        round(mean_exec_time::numeric, 2)                            AS mean_ms,
6.        rows,
7.        round((shared_blks_hit * 100.0
8.               / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS hit_pct
9. FROM   pg_stat_statements
10. WHERE  query NOT LIKE '%pg_stat_statements%'
11. ORDER  BY total_exec_time DESC
12. LIMIT  10;

复制代码

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. 此刻谁在堵（第一现场）

2. SELECT pid,
3.        usename,
4.        state,
5.        wait_event_type,
6.        wait_event,
7.        now() - query_start        AS dur,
8.        pg_blocking_pids(pid)      AS blocked_by,
9.        left(query, 80)            AS q
10. FROM   pg_stat_activity
11. WHERE  state <> 'idle'
12. AND    pid <> pg_backend_pid()
13. ORDER  BY 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. 拿到真实执行计划

2. EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
3. SELECT * FROM orders WHERE status = 'NEW' AND create_time >= '2026-09-01';

复制代码

重点读三处，以这行为例：

2. Seq Scan on orders  (cost=0.00..18342.00 rows=1200 width=48)
3.                     (actual time=0.030..142.220 rows=118432 loops=1)
4.   Filter: (status = 'NEW'::text)
5.   Rows Removed by Filter: 117232
6.   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）函数包裹列 → 改写成范围查询

2. -- 慢：索引完全用不上
3. SELECT * FROM orders WHERE date(create_time) = '2026-09-01';
5. -- 快：改成半开区间，走 create_time 的 btree 索引
6. SELECT * FROM orders
7. WHERE  create_time >= '2026-09-01' AND create_time < '2026-09-02';
9. CREATE INDEX idx_orders_ct ON orders (create_time);
11. -- SQL 实在改不了，退而求其次建表达式索引
12. CREATE INDEX idx_orders_ct_day ON orders (date(create_time));

复制代码

（2）前导通配符 → pg_trgm + GIN

2. CREATE EXTENSION IF NOT EXISTS pg_trgm;
3. CREATE INDEX idx_cust_name_trgm ON customer USING gin (name gin_trgm_ops);
4. SELECT * FROM customer WHERE name LIKE '%科技%';

复制代码

注意 trgm 索引对短字符串（少于 3 个字符）效果差，中文场景要实测，别想当然。

（3）优化器死活不走索引 → 校准代价参数

2. ALTER SYSTEM SET random_page_cost = 1.1;        -- SSD / NVMe 环境
3. ALTER SYSTEM SET effective_cache_size = '8GB';  -- 一般设物理内存的 50%~75%
4. SELECT pg_reload_conf();

复制代码

改完再跑一次 EXPLAIN (ANALYZE, BUFFERS) 验证，不要改完就当完事。

（4）膨胀 → 调 autovacuum 或手动回收

2. SELECT relname,
3.        n_live_tup,
4.        n_dead_tup,
5.        round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) AS dead_pct,
6.        last_autovacuum,
7.        last_autoanalyze
8. FROM   pg_stat_user_tables
9. WHERE  n_live_tup > 10000
10. ORDER  BY n_dead_tup DESC
11. LIMIT  10;

复制代码

对热点大表单独立调阈值：

2. ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
3.                         autovacuum_analyze_scale_factor = 0.01,
4.                         autovacuum_vacuum_cost_delay = 10);
6. VACUUM (ANALYZE, VERBOSE) orders;

复制代码

VACUUM 只标记空间可复用，不把空间还给操作系统；要真正收缩文件得用 VACUUM FULL，它会排他锁表并重建，必须放维护窗口，表大的时候可能锁几十分钟。

6. 治理前后实测对比

同一套环境、同一批 SQL，治理前后的实测数据（示例环境，仅供参考量级）：

对比项	治理前	治理后
函数包裹 create_time 的查询	Seq Scan 118432 行 / 142 ms	Index Scan 1240 行 / 1.8 ms
LIKE '%科技%' 全表扫描	Seq Scan / 860 ms	Bitmap Index Scan / 6.4 ms
random_page_cost 4.0 → 1.1	Seq Scan / 210 ms	Index Scan / 3.1 ms
orders 表 dead_pct	38.6%	1.2%
该 SQL 的 Buffers: shared read	15802	196
列表页 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 值、活跃会话数，作为告警阈值依据，别拍脑袋定数字。