查询优化器:计划是被代价估算选出来的

2.1核心观念:你喂输入,不指挥优化器

社区版 PostgreSQL 默认不支持 query hint——你无法命令它走某个计划,计划完全由代价模型自动选出。

优化器(planner / optimizer)对一条查询做的事,可以拆成一条流水线:解析 → 枚举出多个候选执行计划 → 给每个计划估算一个代价(cost)→ 选 cost 最小的 → 交给执行器跑。你在第 01 章看到的 EXPLAIN 输出,就是这条流水线最后选定的那个计划。

这张图直接对应调优的三个杠杆,也对应后面三章:

• 给准统计信息(本章 §2.2):让"估算代价"基于真实的数据分布。统计过期是头号事故源。
• 提供更便宜的路径(第 03 章索引):往"候选计划集"里塞入一个代价更低的选项,优化器自然会选它。
• 校准代价参数(第 05 章配置):调 random_page_cost 等,让抽象代价单位贴近你的硬件(SSD vs 机械盘)。

2.2代价从哪来:统计信息 + 代价模型

cost = 预计读取的页数 × 单页代价 + 预计处理的行数 × 单行代价;"预计多少"由统计信息算出。

统计信息存在哪、怎么更新

ANALYZE 命令(autovacuum 也会自动触发,见第 04 章)对每张表随机采样若干行,算出每列的分布特征,存进系统目录 pg_statistic(可读视图是 pg_stats)。关键的几个统计量:

看一眼真实的统计行,会让"估算"具体起来:

SELECT attname, n_distinct, null_frac,
       most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'customer_id');

   attname    | n_distinct | null_frac |        most_common_vals
--------------+------------+-----------+---------------------------------
 status       |          4 |       0   | {shipped,paid,pending,cancelled}
 customer_id  |     -0.85  |       0   | (空,几乎全是唯一值)

• status 的 n_distinct=4,且四个值都在 MCV 里。优化器查 status='shipped' 时,直接用 'shipped' 的真实频率(比如 60%)估选择率——很准。低基数列,统计信息几乎不会骗它。
• customer_id 的 n_distinct=-0.85,负数表示"按行数比例",即不同值约占总行数的 85%(近似唯一)。查 customer_id=42 的选择率 ≈ 1/(0.85 × 行数),极低——优化器会倾向走索引。

2.3四种取数方式:按选择率挑

从一张表取数有四种方式,优化器主要按"要取多少行占全表的比例"(选择率)来选。

这是把上一章的 Seq Scan / Index Scan 补全。同一条 WHERE,命中行占比不同,最便宜的取数方式就不同:

为什么命中行多了反而该用顺序扫描?因为索引扫描的回表是随机读:每拿到一个 TID 就要跳到表的对应页去取行。命中几行时,几次随机读很快;但要命中 30 万行时,30 万次随机跳读,比把整张表顺序读一遍还慢。优化器用 random_page_cost(默认 4.0,即随机读一页约等于顺序读 4 页的代价)来量化这个差别。这正是"顺序扫描有时比索引快"这个反直觉结论的来源。

关于 Index-Only Scan 的"页 all-visible"条件,牵涉到 MVCC 的可见性映射(visibility map),留到第 04 章讲——那里你会看到,VACUUM 不及时会让本可以 index-only 的扫描退化成要回表。这是优化器章和 MVCC 章的一个连接点。

2.4三种 JOIN 算法:按输入大小和是否有序挑

两表连接有三种算法,优化器按"输入多大、是否已按连接键有序、是否等值连接"来选。

第 01 章那个 Nested Loop + loops=980 的挑战题,现在可以正式拆解了。三种算法:

三种算法的取舍可以画成一棵决策树。注意这棵树是优化器在走,不是你——它对每种可行算法都估个代价,这里画的是"通常哪个会胜出":

2.5串起来:一次"选错计划"的完整故事

把本章和第 01 章的所有线索接成一条完整的因果链——这是面试和线上排查都会反复出现的剧本。

场景:一张 orders 表凌晨批量导入了 50 万条新订单,导入后没有跑 ANALYZE。早上一条带 JOIN 的报表查询突然从 200 毫秒涨到 40 秒。

Nested Loop  (cost=0.42..3201 rows=8 width=120)
             (actual time=0.1..39820.5 rows=503000 loops=1)
  ->  Seq Scan on orders o  (rows=8) (actual rows=503000 loops=1)
        Filter: (created_at >= '2026-06-02')
  ->  Index Scan using pk_customers c  (actual time=0.05..0.07 rows=1 loops=503000)

诊断(用第 01 章的眼睛):顶层 Nested Loop 估算 rows=8,真实 rows=503000——偏差六万倍。根因落在最里层的 Seq Scan on orders:它估 created_at >= '2026-06-02' 只命中 8 行,因为统计信息还是导入前的快照,那时这个日期之后确实几乎没有数据。优化器据此以为外层只有 8 行,放心地选了 Nested Loop。结果内层被驱动了 503000 次(loops=503000),每次 0.07 毫秒,累计约 35 秒。

修复:不改查询、不加 hint、不建新索引——只修输入:

ANALYZE orders;   -- 重新采样,让统计信息看到那 50 万新行

ANALYZE 后,优化器重新估算:created_at >= '2026-06-02' 现在命中约 50 万行。它立刻改主意——50 万行外层用 Nested Loop 太贵,转而选 Hash Join:把 customers 建成哈希表,用 50 万订单去探测,一次扫描搞定。查询回到几百毫秒。