PostgreSQL查询优化器基于代价模型的执行计划生成

PostgreSQL在收到一条SQL查询时，会经过一系列处理步骤。其中，查询优化器负责决定如何最高效地执行这条查询，它会为同一查询生成多种可能的执行方案，并选择估算成本最低的方案来执行。这个选择过程的核心就是代价模型。本文将引导你理解这个过程，并学会查看和分析优化器做出的选择。

第一阶段：理解代价模型与执行计划

代价模型是数据库内部的一套数学公式和评估体系。它不直接衡量真实的执行时间（这依赖于当时的硬件负载和并发情况），而是根据数据库目录中的统计信息（如表的大小、列中不同值的数量、数据分布等）来估算各种操作的“代价”。

执行计划是优化器选定的具体实施方案，它详细描述了数据库将如何访问数据、以何种顺序连接表、使用何种算法等。理解执行计划是进行查询性能调优的第一步。

获取一条查询的执行计划
你可以使用 EXPLAIN 命令来查看PostgreSQL为一条查询生成的执行计划。

1. 启动 一个PostgreSQL客户端（如 psql）。
2. 运行 以下SQL查询来查看执行计划：

EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;

输出的结果可能类似于以下格式：

Seq Scan on orders  (cost=0.00..1234.00 rows=50 width=48)
  Filter: (customer_id = 1234)

解读关键字段：

• Seq Scan on orders：计划使用顺序扫描（全表扫描）访问 orders 表。
• cost=0.00..1234.00：估算的代价。格式是 启动代价..总代价。0.00 是启动代价（这里几乎为零，因为顺序扫描一开始就能返回行），1234.00 是处理完所有返回行的总估算代价。代价的单位是抽象的，没有具体意义，仅供比较。
• rows=50：优化器估算该操作会返回约 50 行数据。
• width=48：估算返回行的平均宽度（字节数）。

第二阶段：分析代价计算的关键参数

优化器的代价估算主要基于以下几类参数，这些参数的值会影响它对不同执行路径的选择。

1. 查看 当前的代价参数设置：

SHOW cpu_tuple_cost;
SHOW seq_page_cost;
SHOW random_page_cost;

这些参数定义了执行不同操作的基本“单价”。例如，random_page_cost 通常远高于 seq_page_cost，这反映了随机磁盘I/O比顺序I/O昂贵得多，从而引导优化器在可能时优先选择索引扫描（需要随机I/O）而不是全表扫描。

2. 理解 代价公式的组成部分。一个操作的总代价通常由以下部分构成：
   • I/O代价：读取磁盘页面的成本，与读取的页面数和访问方式（顺序或随机）相关。
   • CPU代价：处理元组（行）的成本，如评估 WHERE 条件、执行函数、排序等。
   • 一条执行计划的总代价是其所有子操作代价的汇总。

调整代价参数（谨慎操作）
在某些特殊硬件环境下（如全SSD存储），默认的 random_page_cost 可能过高。你可以临时调整 参数来观察优化器是否会选择更合适的计划：

SET random_page_cost = 1.1; -- 将随机I/O代价设置得更接近顺序I/O
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
SET RESET random_page_cost; -- 重置为默认值

第三阶段：深入查看与对比执行计划

为了做出更准确的判断，优化器需要更详细的信息。

1. 添加 ANALYZE 选项来获得实际执行时间和行数。这会真实执行 查询，因此对于耗时查询要谨慎使用：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1234;

输出会在每个操作节点后增加 actual time 和 rows 等字段，将估算值（rows）与实际值（loops 相乘后）进行对比。如果两者差异巨大，说明统计信息可能过时。

2. 添加 VERBOSE 选项来查看输出列和函数表达式等额外信息：

EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM orders WHERE customer_id = 1234;

3. 添加 FORMAT 选项来获得结构化的输出（如JSON、XML或YAML），便于程序解析：

EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE customer_id = 1234;

对比不同查询或索引的执行计划
一个强大的分析技巧是强制使用或禁用某种扫描方式，然后对比代价。

-- 查看使用索引的估算代价
SET enable_seqscan = off; -- 临时禁用顺序扫描
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
SET enable_seqscan = on;  -- 恢复默认

-- 查看顺序扫描的估算代价
SET enable_indexscan = off;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
SET enable_indexscan = on;

通过对比两种情况的 cost 值，你可以理解优化器在当时的配置下为什么选择了A方案而不是B方案。

第四阶段：统计信息是代价模型的基石

代价模型的准确性高度依赖于 pg_statistic 系统表中的统计信息。这些信息由 ANALYZE 命令（通常由自动清理守护进程或手动触发）收集和更新。

1. 手动触发 统计信息更新，确保优化器拥有最新的数据分布信息：

ANALYZE orders; -- 更新 `orders` 表的统计信息

2. 查看 特定表的统计信息：

SELECT * FROM pg_stats WHERE tablename = 'orders' AND attname = 'customer_id';

你会看到诸如 n_distinct（不同值的数量）、null_frac（空值比例）、most_common_vals（最常见的值）、histogram_bounds（直方图边界）等字段。优化器利用这些信息来估算 WHERE customer_id = 1234 这样的条件会选择出多少行数据（rows 估计值）。

当估算值严重失真时
如果 EXPLAIN ANALYZE 显示估算行数与实际行数差了几个数量级，那么首先检查 相关表的统计信息是否最新。如果已经是最新但仍不准确，你可能需要通过调整 default_statistics_target 参数来增加 统计信息的采样量，从而获得更精确的直方图和相关性数据。

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000; -- 增加此列的统计目标
ANALYZE orders; -- 重新收集统计信息

第五阶段：解读连接与多表查询的代价

对于连接查询，优化器需要决定连接的顺序（哪张表作为外表，哪张作为内表）和连接算法（嵌套循环、哈希连接、合并连接）。

EXPLAIN SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'US';

观察执行计划，重点看：

• 连接顺序：优化器是先扫描 customers 表筛选出美国客户，再通过索引去 orders 表中查找对应的订单，还是反过来？优化器会尝试不同的顺序，并估算每种顺序的总代价。
• 连接算法：
  • Hash Join：通常用于等值连接，其中一个表（较小或已过滤）被用来构建哈希表，另一个表进行探测。如果看到 Hash 步骤，其 Batches 和 Memory Usage 字段显示了哈希表是否溢出到磁盘。
  • Merge Join：要求两个输入已按连接键排序。它最适用于已排序数据或结果集很大的情况。
  • Nested Loop：对外表的每一行，在内表中查找匹配行。如果内表上有索引且外表较小，效率很高。

优化器会基于表的大小、过滤后剩余行数、索引是否存在等因素，为每种连接算法和顺序组合估算代价，并选择总代价最小的那个方案。

关键总结与行动步骤

要掌握PostgreSQL的查询优化，遵循以下流程：

1. 获取：始终使用 EXPLAIN 或 EXPLAIN ANALYZE 来获取执行计划。
2. 对比：对比估算行数（rows）和实际行数（EXPLAIN ANALYZE 中的 Actual Rows），差异过大意味着统计信息不准。
3. 更新：对相关表运行 ANALYZE 命令以更新统计信息。
4. 解读：重点关注总 cost、访问方法（Seq Scan, Index Scan 等）、连接算法（Hash Join, Nested Loop 等）和连接顺序。
5. 调优：如果计划不理想，首先考虑创建合适的索引。如果索引存在但仍不被使用，检查统计信息或查询条件是否导致优化器认为全表扫描更快。