技术小册

主题

查询优化与执行计划：把慢查询拆成可验证的假设 ​

一句话：执行计划是优化器对「读多少页、做多少次比较、用多少内存」的估算；优化的动作，是让估算和实际对得上。

适用 PostgreSQL 16/17。示例执行计划为示意输出，成本和时间随硬件、数据分布变化。

为什么需要 ​

慢查询的原因通常不在 SQL 写法本身，而在三件事：扫了太多行、连接顺序不对、排序/哈希落盘。只看「执行了 3 秒」无法判断是哪一种；EXPLAIN (ANALYZE, BUFFERS) 把每一层做了多少工作摊开，才能定位。

核心机制 ​

一条 SQL 的路径 ​

Parser 解析语法 → Rewriter 展开视图和规则 → Planner 用统计信息估算成本、选计划 → Executor 执行。优化器不是「找最优」，而是在有限时间内找一个足够便宜的计划。

成本单位不是毫秒，而是以页读取为基准的相对值：seq_page_cost=1.0、random_page_cost=4.0（PostgreSQL 16/17 默认）、cpu_tuple_cost=0.01。所以 cost=0.43..12.45 只能用于比较计划，不能换算成时间。真正的耗时看 actual time。

统计信息决定估算 ​

ANALYZE 把列的值分布写进 pg_statistic，优化器据此估算 rows。估算偏差的常见来源：

  • 表刚大批量导入，没跑 ANALYZE。
  • 多列之间有相关性，单列统计看不到。例如 city 和 postcode 强相关，用 CREATE STATISTICS ... (dependencies) 补充。
  • 表达式/函数条件没有对应统计。
  • 参数化查询使用通用计划（generic plan），看不到具体参数的选择性。

扫描与连接 ​

  • Seq Scan：顺序读全表。小表或返回大部分行时是合理选择，不一定是「坏计划」。
  • Index Scan：走索引定位，再回表取其他列。
  • Index Only Scan：只读索引；但仍要检查可见性映射，可能产生 Heap Fetches。
  • Bitmap Index Scan + Bitmap Heap Scan：先把多个索引的候选行做成位图，再按物理页顺序回表，把随机 IO 变成更有序的 IO。它不是 PostgreSQL 独有；Oracle 的 bitmap index 是另一种实现，这里说的是 PostgreSQL 的计划节点。
  • Nested Loop：小表驱动大表，适合内层有索引的点查。
  • Hash Join：等值连接，先把小表建哈希表；内存不够会分批。
  • 并行计划用 Gather 汇总 worker；小查询开并行反而增加启动开销。

排序与内存 ​

work_mem=4MB（PostgreSQL 16/17 默认）是每个排序/哈希操作的上限，不是每个查询。一个查询有多个 Sort/Hash，就各占一份。内存不够时计划里会出现：

Sort Method: external merge  Disk: 32768kB

这表示排序落盘了。调大 work_mem 可以缓解，但要按并发数估算总内存。

最小上手 ​

准备一张 100 万行的订单表（与 01 篇相同的建表语句可复用）：

sql
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL,
  amount     numeric(12,2) NOT NULL
);

INSERT INTO orders (user_id, status, created_at, amount)
SELECT (random() * 100000)::bigint,
       (ARRAY['paid','pending','cancelled'])[1 + (random() * 2)::int],
       now() - (random() * 365)::int * interval '1 day',
       (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000);

ANALYZE orders;

没有复合索引时：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

示意输出（数值随环境变化）：

Limit  (cost=41235.67..41235.72 rows=20 width=24) (actual time=182.431..182.436 rows=20 loops=1)
  Buffers: shared hit=1250 read=9200
  ->  Sort  (cost=41235.67..41238.17 rows=1000 width=24) (actual time=182.428..182.430 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..41210.00 rows=1000 width=24) (actual time=0.015..180.221 rows=1024 loops=1)
              Filter: ((user_id = 42) AND (status = 'paid'::text))
              Rows Removed by Filter: 998976
              Buffers: shared hit=1250 read=9200
Planning Time: 0.180 ms
Execution Time: 182.500 ms

建复合索引后：

sql
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);
ANALYZE orders;

再次执行同一条 EXPLAIN，示意输出：

Limit  (cost=0.43..9.21 rows=20 width=24) (actual time=0.024..0.061 rows=20 loops=1)
  Buffers: shared hit=12 read=3
  ->  Index Scan using idx_orders_user_status_created on orders
        (cost=0.43..438.21 rows=1000 width=24) (actual time=0.022..0.056 rows=20 loops=1)
        Index Cond: ((user_id = 42) AND (status = 'paid'::text))
        Buffers: shared hit=12 read=3
Planning Time: 0.095 ms
Execution Time: 0.080 ms

逐行读：

  • Limit 的 actual time=0.024..0.061：第一行在 0.024ms 返回，读完 20 行共 0.061ms。
  • cost=0.43..9.21：启动成本 0.43，总成本 9.21，单位是相对成本，不是毫秒。
  • rows=1000 是优化器估算，actual ... rows=20 是实际；两者差距大时先怀疑统计信息。
  • loops=1：该节点执行一次。嵌套循环内层节点的 actual time 要乘以 loops 才是总耗时。
  • Buffers: shared hit=12 read=3：命中缓存 12 页、磁盘读 3 页。read 高说明缓存不足或数据冷。
  • Rows Removed by Filter: 998976：扫描后过滤掉的行数。这个数字接近表行数时，说明索引没有把条件变成 Index Cond。
  • Planning Time / Execution Time：计划时间和执行时间；优化看执行，计划时间异常高可能是分区/表太多。

生产动作 ​

  • 先找 SQL，再看计划：pg_stat_statements 按 total_exec_time 排序；需要 shared_preload_libraries='pg_stat_statements' 并重启。
  • 自动记录慢计划：auto_explain 配 auto_explain.log_min_duration=1000、log_analyze=on、log_buffers=on；log_min_duration_statement=1000（默认 -1，即关闭）记录慢 SQL。
  • EXPLAIN ANALYZE 会真的执行：对 UPDATE/DELETE 要先 BEGIN 再 ROLLBACK，避免改数据。
  • 估算偏差处理：rows 与 actual rows 差 10 倍以上，先 ANALYZE；仍偏就建扩展统计，或检查条件里的隐式类型转换。
  • 排序落盘：看到 external merge，先确认 work_mem（默认 4MB）是否够；调大后观察 temp 文件和内存，不要全局拉满。
  • 并行查询：max_parallel_workers_per_gather=2（PostgreSQL 16/17 默认）；小查询可以 SET max_parallel_workers_per_gather=0 对比，确认并行是否真的更快。
  • JIT：jit=on（PostgreSQL 16/17 默认），jit_above_cost=100000；短查询开 JIT 可能因编译开销变慢，可用 SET jit=off 做对比。
  • 排障路径：现象「查询突然变慢」→ EXPLAIN (ANALYZE, BUFFERS) → 看 Seq Scan 行数、Rows Removed by Filter、Sort Method、Buffers read → 分别对应统计信息/索引、条件选择性、work_mem、缓存或 IO。

边界 ​

  • 成本不是时间：cost 只用于比较计划；换硬件、换 random_page_cost 后，同一计划的成本数字会变。
  • 计划会随数据变：今天的好计划，数据分布变了可能变坏；不要把一个计划当成永久结论。
  • EXPLAIN ANALYZE 有副作用：它执行语句；对写语句务必包在回滚事务里。
  • 通用计划与定制计划：参数化查询可能选通用计划，具体参数下不是最优；可用 plan_cache_mode=force_custom_plan 对比，但会增加每次解析成本。
  • Bitmap Scan 不是万能：返回行很多时，位图本身也很大，仍可能退化为顺序扫描；它也不是 PostgreSQL 独有。
  • 并行不是越多越好：并行有启动和汇总成本，小表、短查询、高并发下可能更慢。
  • 索引不是唯一解：统计信息、SQL 改写、分区裁剪、物化视图，都可能比加索引更合适。

讲清楚 ​

执行计划回答的是「数据库打算怎么读」。EXPLAIN (ANALYZE, BUFFERS) 把估算和实际放在一起：rows 对不上，先修统计信息；Rows Removed by Filter 很大，说明索引没把条件吃进去；Sort Method: external merge 说明 work_mem 不够；Buffers read 高说明缓存或 IO 有问题。成本单位是相对页成本，不是毫秒，所以只看 cost 会误判。优化的顺序应该是：先用 pg_stat_statements 找到真正耗时的 SQL，再用计划验证假设，最后才动索引或参数。Bitmap Scan 只是 PostgreSQL 的一种计划节点，不要把「多个索引位图合并」当成它独有的能力。

把一门技术讲透到既懂也会用

技术小册 · 持续更新