卷 V · 计划CH 22深度 22/24

让 planner 做对选择

规划器很聪明，但它的判断建立在对数据的估计上。喂给它准确的信息、别给它添乱，它就替你选对路；反过来，很多「PostgreSQL 好慢」的抱怨，根子是规划器被过期的统计、或糟糕的查询写法误导了。这一章讲怎么和它好好配合。

统计信息选择性N+1调参

规划器的判断，全靠统计信息

第 20 章说过：规划器选计划，靠估算每个节点会吐出多少行。这个估算，来自 ANALYZE 收集的统计信息，存在系统表里，主要包括：

  • 每列有多少个不同值（n_distinct）——决定 = 某值 大概命中几分之几。
  • 最常见值（MCV）及其频率——决定热门值和冷门值的选择性差异。
  • 直方图——描述值的分布，用来估范围条件（>、BETWEEN）命中多少。

所以「保持统计新鲜」是第一要务。autovacuum 会顺手 ANALYZE，但在刚批量导入 / 大批更新之后，别等它，手动来一句：

ANALYZE orders;              -- 更新单表统计
ANALYZE;                     -- 更新全库
◆ 「它不用我的索引」的排查三步

这是最高频的困惑，按这个顺序查：

  1. 看 EXPLAIN 的估算行数对不对。如果它估「命中 10 万行」而实际只有 10 行，是统计过期——ANALYZE 一下再看。
  2. 如果估得准、它还是顺扫，那多半它是对的：命中行占比太高，顺扫确实比索引快（第 19 章那笔账）。可以 SET enable_seqscan = off 强制它走索引、再 EXPLAIN ANALYZE 对比实际耗时——你往往会发现强来的索引扫更慢，规划器赢了。
  3. 如果确实该走索引却走不了，检查查询写法：条件里对列套了函数（WHERE lower(email)=... 而索引建在 email 上）、类型不匹配触发了隐式转换、或 LIKE '%x' 左通配——这些都会让索引失效。对症下药（建表达式索引、对齐类型）。

扩展统计：当多列相关

规划器默认假设各列相互独立。但现实里列常常相关——比如「城市」和「邮编」高度相关（知道邮编基本就知道城市）。这时 WHERE city='...' AND zip='...' 的选择性，规划器会因为「独立假设」估得离谱地小（把两个条件的选择性相乘），进而选错计划。

对策是扩展统计，显式告诉它这几列相关：

CREATE STATISTICS s_city_zip (dependencies)
  ON city, zip FROM addresses;
ANALYZE addresses;

之后规划器就懂得这几列的相关性，估算准了，计划也就对了。这是应对「多列相关导致估算失准」的利器。

N+1：不是数据库的锅，是应用的锅

一个和规划器无关、却极常见的性能杀手，来自应用层：

// ❌ N+1：查 1 次拿到 100 个订单，再循环查 100 次拿客户
const orders = await db.query("SELECT * FROM orders LIMIT 100");
for (const o of orders) {
  o.customer = await db.query("SELECT * FROM customers WHERE id = $1", [o.customer_id]);
}
// 一共 101 次数据库往返，每次都有网络开销

正确做法是让数据库一次连好——用 JOIN，或用 WHERE id IN (...) 批量取。ORM 用户尤其要警惕：懒加载常常悄悄制造 N+1。把「循环里查数据库」当成一个危险信号——通常能改成一次 JOIN 或一次批量查询。

分页：别让 OFFSET 越翻越慢

第 5 章提过：LIMIT n OFFSET m 在 m 很大时会慢，因为数据库得先算出前 m 行再全扔掉。翻到第 1000 页，等于白算前 999 页。大数据集分页用键集分页：

-- 记住上一页最后一行的 id，下一页从它之后开始
SELECT * FROM orders
WHERE id > 上一页最后的id
ORDER BY id
LIMIT 20;    -- 走索引直接定位，和翻到第几页无关，永远快

该调的几个参数

PostgreSQL 默认配置偏保守（照顾小机器）。生产上值得调的几个：

  • random_page_cost：用 SSD 就从默认 4.0 调到 1.1——告诉规划器「随机读没那么贵了」，它会更愿意用索引。SSD 时代最该改的一个参数。
  • shared_buffers：PostgreSQL 自己的缓存，常设为内存的 25% 左右。
  • effective_cache_size：告诉规划器「操作系统 + PG 大概有多少内存能当缓存」（约内存的 50–75%），影响它对索引扫描的估价。
  • work_mem：单个排序 / 哈希操作能用多少内存。太小会让大排序落盘（第 20 章那个红旗）；但它是每操作、每连接的，调太大易 OOM，要谨慎。
▶ 上手 · 用 pg_stat_statements 找真正的慢查询

别猜哪条查询慢——测。装上扩展 pg_stat_statements（第 23 章会讲扩展），它会记录每类查询的累计耗时、调用次数、平均时间。一句查询就能揪出「最费时间的前 10 条」：

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

优化要先测量、再动手——花时间优化一条其实没多少调用的查询，是白费力气。total_exec_time 高的那几条，才是真正拖垮系统的元凶。

⇄ 对照 MySQL
  • 统计 & ANALYZE：思路一致，MySQL 也有表统计、也靠 ANALYZE TABLE 更新，「估错行数选错计划」两家都会犯。
  • 找慢查询：MySQL 用慢查询日志 + performance_schema；PostgreSQL 用 pg_stat_statements + log_min_duration_statement。工具不同，目标一样。
  • hint vs 参数：MySQL 文化里更常用 FORCE INDEX 等 hint 直接指挥优化器；PostgreSQL 文化里更倾向调统计、调 cost 参数、改写查询让规划器自己选对——「治本不治标」。这又是两家气质的差别。
  • N+1、键集分页：与数据库无关，是通用的应用层实践，MySQL 侧完全一样适用。

这一章的一句话

规划器靠统计信息判断，保持它新鲜（ANALYZE）是第一要务；「它不用我的索引」先查估算行数、多半它是对的；N+1 和大 OFFSET 是应用层的锅；SSD 上把 random_page_cost 调到 1.1；优化前先用 pg_stat_statements 测出真正的慢查询。

卷 V 完整了——你已经理解了 PostgreSQL 那个会思考的大脑。最后的卷 VI，我们走出原理，落到生产：怎么把 PostgreSQL 当成一个平台用好，以及怎么把你多年的 MySQL 直觉稳稳搬过来。