诊断与测量:先知道哪里慢

1.1调优的起点是测量,不是改参数

调优的第一步永远是定位瓶颈,不是打开 postgresql.conf。

有经验的调优都遵循同一个闭环。任何一次"我把 shared_buffers 调大了,好像快了点"如果跳过了测量和复测,都不算调优,只算碰运气。

这一章覆盖循环的第 ① 和 ② 步(测量与定位)。后面四章是第 ③ 步的弹药库:每一类根因对应哪些可调的变量。第 ④ 步复测,就是把同一个 EXPLAIN ANALYZE 再跑一遍,对比前后。

1.2三层测量视角:从实例到单条查询

测量有三个粒度,自顶向下收窄:实例在等什么 → 哪条查询最贵 → 这条查询慢在哪一步。

新手常常一上来就盯着某条查询的 EXPLAIN,但那条查询未必是真正的瓶颈。正确的顺序是从粗到细,像漏斗一样逐层收窄,避免在无关紧要的查询上花时间。

本章按漏斗的反方向讲解:先讲读者最常用、也最该先掌握的第 ③ 层(EXPLAIN,§1.3),再回到第 ② 层定位最贵的查询(pg_stat_statements,§1.4),最后是第 ① 层的全局视角(wait events,§1.5)。掌握了单条 EXPLAIN 的读法,上面两层的输出才有意义。

1.3EXPLAIN:优化器摊开给你看的执行计划

EXPLAIN 显示优化器为一条查询选定的执行计划和它对代价的估算;加 ANALYZE 则真正执行一遍,给出真实耗时和真实行数。

EXPLAIN 的两种模式

场景走查:一条查询,有索引和没索引

用一张一百万行的订单表 orders,查某个客户某天之后的订单。先看没有合适索引时优化器给出的计划:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42 AND created_at >= '2026-01-01';

Seq Scan on orders  (cost=0.00..18334.00 rows=12 width=84)
                    (actual time=0.412..82.318 rows=9 loops=1)
  Filter: ((customer_id = 42) AND (created_at >= '2026-01-01'::date))
  Rows Removed by Filter: 999991
  Buffers: shared hit=64 read=8270
Planning Time: 0.123 ms
Execution Time: 82.401 ms

逐行解读(读的是含义,不是语法)

• Seq Scan on orders:执行方式是顺序扫描——把整张表从头到尾读一遍。这就是"全表扫描"。
• cost=0.00..18334.00:优化器的估算代价,两个数是"启动代价..总代价"。单位不是毫秒(见下面的关键澄清),是一个以"顺序读一个页 = 1.0"为基准的抽象单位。
• rows=12:优化器估算这一步会输出 12 行。
• actual ... rows=9:真实只输出了 9 行。估算 12、真实 9,很接近——这里统计信息是准的。
• Rows Removed by Filter: 999991:破案了。为了找出 9 行,它读了一百万行、扔掉 999991 行。这是顺序扫描的代价,也是"该建索引了"的信号。
• Buffers: shared hit=64 read=8270:64 个页在内存里命中,8270 个页从磁盘读。大量 read 印证了它在啃整张表。
• Execution Time: 82.401 ms:真实总耗时。

现在为 customer_id 建一个索引,再跑同一条查询:

Index Scan using idx_orders_customer on orders
      (cost=0.42..8.91 rows=12 width=84)
      (actual time=0.028..0.041 rows=9 loops=1)
  Index Cond: (customer_id = 42)
  Filter: (created_at >= '2026-01-01'::date)
  Buffers: shared hit=5
Planning Time: 0.205 ms
Execution Time: 0.062 ms

同一条查询,82.401 ms → 0.062 ms,快了一千多倍。变化的不是查询,是优化器的执行方式:它现在用索引直接定位到 customer_id = 42 的行(Index Cond),只读了 5 个页(Buffers: shared hit=5),不再啃整张表。created_at 这个条件没进索引,所以仍作为 Filter 在取出的行上二次过滤。第 02、03 章会展开:优化器是怎么算出"用索引更便宜"的,以及索引为什么有时反而不被选。

1.4读懂关键数字:三处最常见的误读

cost 不是时间,actual time 是单次循环的耗时要乘 loops,rows 偏差指向统计信息。

图 1.3 标出了节点上的字段。真正容易出错的是它们的含义。三处误读几乎人人踩过:

误读一:把 cost 当成毫秒

cost 是一个抽象单位,基准是"顺序读取一个 8KB 数据页 = 1.0"(由参数 seq_page_cost 定义)。它用来让优化器比较不同计划的相对贵贱,不对应任何真实时间。一个 cost=18334 的计划不是"18334 毫秒",而是"按优化器的模型,比 cost=8.91 的计划贵约两千倍"。要看真实时间,只看 actual time 和 Execution Time。第 05 章会讲,调 random_page_cost 等代价参数,本质就是在校准这套抽象单位,让它更贴近你的硬件。

误读二:把 actual time 当成该节点的总耗时

actual time=0.028..0.041 的两个数是"返回第一行的时间..返回最后一行的时间",而且是单次循环(per loop)的值。当节点的 loops > 1 时,该节点的真实总耗时约等于 (第二个数) × loops。这是嵌套循环里最坑人的地方:

误读三:忽略 rows 估算与真实的偏差

这是最该养成的习惯:每看一个计划,先扫一眼哪个节点的 rows(估算)和 actual rows(真实)差得最远。优化器是根据估算行数选计划的;如果它以为某步出 10 行、实际出 100 万行,它就会基于错误前提选错算法(比如该用 hash join 却选了 nested loop)。偏差大的那个节点,几乎总是问题的源头,且修法通常在第 02 章:更新统计信息、提高统计精度、或重写让优化器能估准。

1.5第 ② 层:用 pg_stat_statements 找最贵的查询

pg_stat_statements 把每类查询的执行次数和累计耗时记下来,让你按"总耗时"而不是"单次耗时"排优先级。

它是官方扩展,需要先在 postgresql.conf 里加载并创建:

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'  (需重启)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 按累计耗时排名,这是定位瓶颈的主力查询
SELECT
  substring(query, 1, 50) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

             query              | calls |  total_ms  |  mean_ms
--------------------------------+-------+------------+-----------
 SELECT * FROM orders WHERE ... | 48210 |  241050.5  |    5.00
 SELECT count(*) FROM events    |     3 |   45200.1  | 15066.70
 UPDATE inventory SET qty ...   |  9820 |   38110.2  |    3.88

1.6第 ① 层:实例在等什么(wait events)

pg_stat_activity 的 wait_event 字段,告诉你此刻活跃后端正卡在哪一类等待上——是磁盘 IO、锁,还是别的。

当问题不是"某条查询慢",而是"整个库都变慢了",就要上升到实例视角。pg_stat_activity 记录每个连接此刻在做什么、在等什么:

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

 wait_event_type |   wait_event   | count
-----------------+----------------+-------
 IO              | DataFileRead   |    18
 Lock            | transactionid  |     3
                 |                |     5   -- NULL = 正在跑,没在等

把等待按类型归因,直接指向后面对应的章节:

Nested Loop  (cost=0.42..91234 rows=5 width=120)
             (actual time=0.05..1850.2 rows=4900 loops=1)
  ->  Seq Scan on customers c  (cost=0..1894 rows=10 width=40)
                 (actual time=0.02..6.1 rows=980 loops=1)
        Filter: (region = 'APAC')
  ->  Index Scan using idx_orders_cust on orders o
                 (cost=0.42..89 rows=1 width=80)
                 (actual time=0.3..1.86 rows=5 loops=980)