PostgreSQL 的查询执行过程（Planner/Optimizer 是如何工作的）

知识点图片

PostgreSQL 的查询执行过程是一个高度复杂且精妙的系统。当客户端发送一条 SQL 语句到 PostgreSQL 时，这条语句会经历一系列的阶段，最终返回结果。其中，Planner/Optimizer（计划器/优化器） 是整个数据库的“大脑”，决定了查询执行的效率。

以下是 PostgreSQL 查询执行过程的完整解析，并重点深入探讨 Planner/Optimizer 是如何工作的。

第一部分：查询的生命周期（宏观视角）

一条 SQL 语句在 PostgreSQL 中的一生可以分为以下四个主要阶段：

Parser（解析器）： 检查 SQL 的语法是否正确，将其转换为一棵解析树（Parse Tree）。
Analyzer/Rewriter（分析器/重写器）： 进行语义分析（检查表名、列名是否存在，权限是否正确），并根据规则系统（Rule System）重写查询（例如，将视图展开为底层的表查询）。输出查询树（Query Tree）。
Planner/Optimizer（计划器/优化器）： （核心） 接收查询树，评估各种可能的执行方案，计算代价（Cost），并选择代价最低的方案。输出计划树（Plan Tree）。
Executor（执行器）： 按照计划树的节点顺序，采用“火山模型（Volcano Model）”或拉取模型（Pull Model）逐行获取数据，执行物理操作（扫描、过滤、连接等），并将结果返回给客户端。

第二部分：Planner/Optimizer 是如何工作的？（深度解析）

PostgreSQL 的优化器是一个基于代价的优化器（CBO, Cost-Based Optimizer）。它的唯一目标是：在所有可能的执行路径中，找到执行代价（Cost）最低的那一条。

优化器的工作过程可以分为以下几个关键步骤：

1. 预处理与逻辑优化（Logical Optimization）

在生成物理路径之前，优化器会先对查询树进行等价的逻辑简化：

常量折叠（Constant Folding）： 例如将 WHERE age > 10 + 5 简化为 WHERE age > 15。
谓词下推（Predicate Pushdown）： 将过滤条件尽可能深地推向叶子节点（表扫描阶段），以便尽早过滤掉无用数据，减少后续 Join 操作的数据量。
外连接消除（Outer Join Simplification）： 在某些条件下（例如 WHERE 过滤掉了 NULL 值），将 LEFT JOIN 转换为 INNER JOIN，从而增加后续 Join 顺序的灵活性。
IN/EXISTS 转换： 将子查询转换为效率更高的 Semi-Join（半连接）或 Anti-Join（反连接）。

2. 路径生成（Path Generation）

优化器会为查询生成所有可能的物理执行路径（Path）。路径分为单表扫描路径和多表连接路径。

A. 单表扫描路径生成
对于查询中的每一张表，优化器会考虑以下扫描方式：

Seq Scan（全表扫描）： 从头到尾读取整个表。适合读取表中大部分数据的场景。
Index Scan（索引扫描）： 遍历索引树找到满足条件的行指针，再去表（Heap）中读取实际数据。
Index Only Scan（仅索引扫描）： 如果查询的列全都包含在索引中，则直接从索引返回数据，不回表查询（依赖可见性映射表 VM）。
Bitmap Index/Heap Scan（位图扫描）： 当通过索引获取的数据较多且分布零散时，先扫描索引生成一个包含物理块位置的位图（Bitmap），然后根据位图按顺序读取表数据，将随机 I/O 转化为顺序 I/O。

B. 多表连接路径生成（Join Paths）
如果有多个表进行 JOIN，优化器会考虑三种物理连接算法：

Nested Loop Join（嵌套循环连接）： 两层 for 循环。驱动表（外表）每出一行，就去被驱动表（内表）中匹配。适合外表极小，且内表连接键有索引的情况。
Hash Join（哈希连接）： 将较小的表放入内存构建 Hash 表，然后扫描较大的表，逐行探测 Hash 表寻找匹配。适合处理大数据量的等值连接（Equi-Join）。
Merge Join（归并连接）： 将两个表按连接键排序，然后像拉链一样进行匹配。如果输入的数据本身就是有序的（例如通过索引扫描得来），此方法极其高效。

C. 决定 Join 的顺序（Join Ordering）
这是优化器中最复杂、最耗时的部分（NP-Hard 问题）。比如有 A, B, C 三张表，可以是 (A JOIN B) JOIN C，也可以是 (A JOIN C) JOIN B。

动态规划（Dynamic Programming）： 当参与 Join 的表数量较少（默认 < 12 个，由 geqo_threshold 参数控制）时，PostgreSQL 采用自底向上的动态规划算法，穷举所有可能的组合，确保找到绝对的最优解。
遗传算法（GEQO - Genetic Query Optimizer）： 当表数量过多时，穷举会导致编译时间呈指数级爆炸。此时 PostgreSQL 会改用遗传算法，通过交叉、变异等概率启发式算法，在合理的时间内找到一个“足够好”的次优解。

3. 代价估算（Cost Estimation）

优化器在生成路径的同时，会为每条路径计算“代价”。这个代价是一个无量纲的相对值，主要由 I/O 代价和 CPU 代价组成。

代价计算依赖于系统统计信息：

PostgreSQL 后台的 Auto-Vacuum 进程会定期执行 ANALYZE，收集各个表的统计数据，存在 pg_statistic 系统表中（可通过 pg_stats 视图查看）。
关键的统计信息包括：表的总行数（reltuples）、总页数（relpages）、列的唯一值个数（n_distinct）、空值比例（null_frac）、最常见值（MCV, Most Common Values） 以及数据直方图（Histograms）。

代价计算模型参数：

seq_page_cost（默认 1.0）：顺序读取一个磁盘页的代价。
random_page_cost（默认 4.0）：随机读取一个磁盘页的代价（在全 SSD 环境下，通常建议调低至 1.1 左右）。
cpu_tuple_cost（默认 0.01）：CPU 处理一行数据的代价。
cpu_operator_cost（默认 0.0025）：执行一次操作（如比较运算、哈希运算）的代价。

代价的数学逻辑：
Cost = (预估读取的页面数 * 页面读取代价) + (预估处理的行数 * CPU处理代价)
优化器利用统计信息（比如直方图）来估算选择率（Selectivity），即某个 WHERE 条件过滤后剩下的数据比例。选择率越准确，估算出的行数就越准确，算出的代价就越真实。

4. 生成计划树（Plan Generation）

在所有的 Path 都生成并估算代价后，优化器会挑选出 Cost 最低的 Path。
最后，优化器将这个选中的 Path 转化为可执行的计划树（Plan Tree）。树的每一个节点（Node）代表一个物理操作（如 SeqScan, HashJoin, Sort, Aggregate）。

第三部分：如何观察优化器的工作？

作为开发者，你可以使用 EXPLAIN 命令来偷窥优化器生成的计划树。

1. `EXPLAIN`（查看执行计划和估算代价）

sql

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

输出示例解析：

cost=10.00..105.50：第一个数字（10.00）是启动代价（输出第一行数据前的准备工作），第二个数字（105.50）是总代价。
rows=1000：优化器预估会返回的行数（极度依赖统计信息）。
width=64：预估每行的平均字节宽度。

2. `EXPLAIN ANALYZE`（实际执行并对比）

sql

EXPLAIN ANALYZE SELECT ...

这不仅会生成计划，还会真正执行这句 SQL。输出会多出 actual time 和 actual rows。

诊断性能问题的核心： 对比 估算的 rows 和 actual rows。如果这两者相差几个数量级，说明统计信息过期了（需要运行 ANALYZE），或者存在相关的列依赖导致优化器算错了选择率。优化器由于被错误的信息误导，选错了执行计划（比如该用 Hash Join 却用了 Nested Loop）。

总结

PostgreSQL 的 Planner/Optimizer 工作流程本质上是：
逻辑简化 -> 穷举组合（查表方式、连接算法、连接顺序） -> 根据统计信息计算 I/O 和 CPU 代价 -> 选择代价最小的方案 -> 移交执行器。

理解这个过程，对于数据库性能调优至关重要。你就会明白为什么需要建立合适的索引，为什么 SSD 时代要调整 random_page_cost，以及为什么定期执行 VACUUM ANALYZE 保持统计信息的新鲜是维持数据库高性能的生命线。

右滑查看面试常问

播面