PostgreSQL查询优化器深度剖析：从代价模型到执行计划的工程实现

引言

PostgreSQL的查询优化器（Optimizer）是整个数据库系统中最复杂的子系统之一，代码量超过15万行。它负责将SQL语句转化为最高效的执行计划——同样的查询，合理的执行计划可能比差劲的计划快数百甚至数千倍。深入理解PostgreSQL优化器的工作原理，不仅有助于写出更高效的SQL，更能在面对慢查询时精准定位问题根源。

1. 查询处理的完整流水线

PostgreSQL的查询处理经过以下阶段：

1. 解析（Parser）：将SQL文本转换为Parse Tree
2. 重写（Rewriter）：应用视图规则展开、规则系统转换
3. 优化器（Planner/Optimizer）：生成最优执行计划
4. 执行器（Executor）：按照计划树执行并返回结果

其中优化器是最核心的阶段，它决定了整个查询的性能走向。

2. 逻辑优化：查询等价变换

逻辑优化阶段的输出仍然是逻辑上正确的查询树，目标是通过等价变换减少后续物理优化的搜索空间：

2.1 子查询提升（子查询展开）

PostgreSQL会自动将EXISTS子查询转换为SEMI JOIN，IN子查询转换为JOIN或SEMI JOIN。相关子查询则被提升为LATERAL JOIN。这种变换使得后续的JOIN顺序优化能够处理子查询。

2.2 谓词下推（Predicate Pushdown）

将WHERE条件尽可能向数据源靠近，减少参与JOIN的元组数量。PostgreSQL的推入策略非常激进：即使跨越JOIN边，只要满足等价变换条件，就会将过滤条件下推。

2.3 外JOIN消除

当查询只引用了LEFT JOIN右表的非NULL列，或引用了右表的主键但不使用其数据时，PostgreSQL会自动消除不必要的外JOIN，将其转换为内JOIN。

3. 物理优化：代价模型与搜索策略

物理优化阶段决定具体的执行方法：扫描方式（全表扫描还是索引扫描）、JOIN算法（Nested Loop/Hash Join/Merge Join）、JOIN顺序等。

3.1 代价模型

PostgreSQL的代价模型基于以下参数：

• seq_page_cost：顺序读取一页的代价（默认1.0）
• random_page_cost：随机读取一页的代价（默认4.0，SSD建议设为1.1）
• cpu_tuple_cost：处理一个元组的CPU代价（默认0.01）
• cpu_index_tuple_cost：处理一个索引元组的CPU代价（默认0.005）
• cpu_operator_cost：执行一次CPU操作符的代价（默认0.0025）

对于每个候选计划，优化器综合计算CPU代价和I/O代价，选择总代价最低的计划。正确设置SSD上的random_page_cost对计划质量至关重要。

3.2 动态规划搜索：JOIN顺序优化

对于有N个关系的JOIN，可能的JOIN顺序有N!种。PostgreSQL使用动态规划算法自底向上搜索最优计划：

• 第k层考虑所有大小为k的子集的最优计划
• 对于每个子集，考虑可能的最后一对连接方式
• 记录每个子集的最小代价计划

当join_collapse_limit内的FROM项都被优化后，外层的JOIN顺序就固定为书写顺序。

3.3 GEQO：遗传算法用于多表JOIN

当超过geqo_threshold（默认12）个关系参与JOIN时，动态规划的O(N!)复杂度变得不可接受。PostgreSQL切换到遗传算法（GEQO）来搜索近似最优解。GEQO通过种群进化机制快速收敛到较好的JOIN计划，虽然不保证全局最优，但对于大量JOIN表的场景效率远高于穷举。

4. 统计信息与代价估算

准确的代价估算依赖于pg_statistic系统目录收集的统计信息：

• MCV（Most Common Values）：最常见的值及其出现频率
• 直方图（Histogram）：值域范围的等概率分布估计
• 相关性（Correlation）：物理顺序与逻辑顺序的相关程度
• N_distinct：不同值个数的估计

这些统计信息通过ANALYZE命令收集。过时的统计信息会导致严重的计划偏差，因此生产环境需要根据数据更新频率调整autovacuum_analyze_scale_factor。

5. 索引选择与执行策略

PostgreSQL支持多种索引类型，优化器根据查询条件选择最优索引：

• B-Tree：最通用的索引，支持=、>、<、BETWEEN、IN、前缀匹配
• GiST：地理空间数据（PostGIS）、全文搜索
• GIN：JSONB、数组、tsvector（倒排索引）
• BRIN：范围索引，适用于物理有序数据（时间序列），极小的索引空间
• <Hash：仅支持=比较，但比B-Tree更小更快（PG10+已WAL日志化）

Index Only Scan在PostgreSQL中非常重要：当索引覆盖了查询所需的所有列时，可以直接从索引中获取数据，无需回表。VACUUM维护的Visibility Map使得索引扫描能判断元组是否对所有事务可见，从而避免每次都回表验证。

6. 并行查询与执行

PostgreSQL 9.6+引入了并行顺序扫描，10.0增加了并行JOIN和并行聚合。优化器会根据表大小和查询代价决定是否启用并行，并通过Gather节点将子计划分发到多个Worker进程执行。Gather节点通过Shared Buffer传递元组，每个Worker各自扫描表的一部分（通过块号范围划分）。

7. 计划分析与调优实战

使用EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)获取完整的计划执行信息时，需要关注：

• 预估行数 vs 实际行数：差异超过10倍说明统计信息不准
• Buffers：shared hit/read显示缓存命中率
• Filter：大量被Filter过滤掉的行说明索引缺失
• Nested Loop：内表扫描次数 = 外表行数 × 内表单次扫描行数

常见的调优措施：创建扩展索引、调整work_mem允许内存中使用Hash Join、使用CTE物化中间结果（MATERIALIZED提示）、更新统计信息、调整effective_cache_size等。

结论

PostgreSQL的查询优化器是一个精密的工程系统，融合了动态规划、遗传算法、统计估计等多种算法。理解优化器的工作原理，是数据库性能调优的核心能力。随着AI4DB技术的发展，PostgreSQL正在探索机器学习驱动的索引推荐（pg_default_ai）和参数调优，但基础的代价模型和搜索策略，仍然是每个数据库从业者必须深入掌握的核心知识。