PostgreSQL笔记36：执行计划基础解读与优化器成本模型

纲要

• EXPLAIN 与 EXPLAIN ANALYZE —— 执行计划的生成与解读
• 执行计划的树形结构 —— 自底向上、从左往右的阅读原则
• 成本模型（Cost Model） —— cost、rows、width 的含义
• 成本参数 —— seq_page_cost、random_page_cost、cpu_tuple_cost、cpu_index_tuple_cost、cpu_operator_cost
• 统计信息（Statistics） —— pg_stats 视图、histogram_bounds、most_common_vals、most_common_freqs
• 行数估算（Row Estimation） —— 选择率（Selectivity）的计算原理
• 多元统计信息（Extended Statistics） —— CREATE STATISTICS 解决关联列估算失真
• 执行计划可视化工具 —— PEV2、Depesz、PGBadger

执行计划：理解 PostgreSQL 如何执行查询

在关系型数据库中，理解执行计划是 SQL 性能优化的基础。PostgreSQL 的查询优化器（Planner）为每一个查询生成一个执行计划（Execution Plan），该计划定义了数据库访问数据的具体方式[reference:0][reference:1]。掌握执行计划的阅读方法，是排查慢查询、进行 SQL 调优的必备技能。

执行计划的结构与阅读规则

PostgreSQL 的执行计划是一个树形结构（Tree of Plan Nodes）[reference:2]。每个节点代表一个具体的操作——扫描表、排序、聚合、连接等。阅读执行计划需要遵循两条核心原则：

1. 自底向上（Bottom-Up）：下层节点的输出作为上层节点的输入。
2. 从左往右（Left-to-Right）：同一层级中，先执行左边的节点。

以一个典型的执行计划为例：

Sort  (cost=163.67..168.14 rows=1787 width=14)
  ->  HashAggregate  (cost=67.23..85.10 rows=1787 width=14)
        ->  Seq Scan on customers  (cost=0.00..34.19 rows=1719 width=14)

在这个计划中，Seq Scan on customers 是最底层节点，它顺序扫描 customers 表并将结果行传递给上层 HashAggregate 节点进行哈希聚合，最终由 Sort 节点完成排序[reference:3]。

EXPLAIN 输出字段详解

一个标准的 EXPLAIN 输出包含以下关键字段：

字段	含义
cost	预估成本，格式为 startup_cost..total_cost
rows	预估返回的行数
width	预估每行的平均字节数
actual time（EXPLAIN ANALYZE）	实际执行时间（毫秒）
actual rows（EXPLAIN ANALYZE）	实际返回的行数
loops（EXPLAIN ANALYZE）	节点被执行的次数
Buffers（EXPLAIN (BUFFERS)）	缓冲区命中与读取情况
Planning Time	规划阶段耗时
Execution Time	执行阶段总耗时

EXPLAIN 输出中的 cost 是优化器选择执行计划的核心依据[reference:4]。startup_cost 表示返回第一行之前的启动成本，total_cost 表示返回所有行的总成本[reference:5]。对于大多数查询，优化器以最小化 total_cost 为目标；但在 EXISTS 子查询等场景中，优化器会优先选择 startup_cost 最小的计划[reference:6]。

成本模型：优化器的决策依据

PostgreSQL 的优化器通过成本模型（Cost Model） 来评估不同执行路径的代价，并选择成本最低的方案[reference:7]。成本是一个无量纲的相对值（measured in arbitrary units），其绝对值没有物理意义，只有相对值影响优化器的决策[reference:8]。

核心成本参数

PostgreSQL 提供了一系列 GUC 参数来控制成本计算[reference:9]。以下是最关键的几个：

参数	默认值	含义
seq_page_cost	1.0	顺序读取一个数据页的成本[reference:10]
random_page_cost	4.0	随机读取一个数据页的成本[reference:11]
cpu_tuple_cost	0.01	处理每一行的 CPU 成本[reference:12]
cpu_index_tuple_cost	0.005	索引扫描中处理每个索引条目的 CPU 成本[reference:13]
cpu_operator_cost	0.0025	执行每个操作符或函数的 CPU 成本[reference:14]

默认情况下，random_page_cost 是 seq_page_cost 的 4 倍[reference:15]。这一设定基于传统机械硬盘（HDD）的物理特性——随机 I/O 远慢于顺序 I/O。

成本参数的调优实践

随着存储技术的发展（SSD、NVMe），随机 I/O 与顺序 I/O 的性能差距已大幅缩小。因此，在生产环境中调优 random_page_cost 是常见的优化手段：

• SSD：建议设置为 1.5 ～ 2.0
• NVMe：建议设置为 1.1 ～ 1.3

-- 查看当前成本参数
SELECT name, setting, unit 
FROM pg_settings 
WHERE name LIKE '%cost%';

-- 调整 random_page_cost（需 superuser 权限）
ALTER SYSTEM SET random_page_cost = 1.5;
SELECT pg_reload_conf();

需要注意的是，random_page_cost 不应低于 seq_page_cost[reference:16]。如果数据库完全缓存于 RAM 中，可将两者设为相等[reference:17]。

统计信息：行数估算的数据基础

优化器要做出准确的成本估算，必须依赖统计信息（Statistics）。PostgreSQL 通过 ANALYZE 命令（或后台 autovacuum 进程）收集表和列的统计信息[reference:18]。

核心统计信息视图：pg_stats

pg_stats 视图提供了对 pg_statistic 系统表中统计信息的可读访问[reference:19]。关键字段包括：

字段	含义
null_frac	列中 NULL 值的比例
avg_width	列值的平均字节宽度
n_distinct	估算的不同值数量
most_common_vals	最常见值的列表
most_common_freqs	最常见值对应的频率
histogram_bounds	直方图边界值

-- 查看表的统计信息
SELECT 
    attname,
    null_frac,
    avg_width,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats 
WHERE tablename = 'your_table' 
  AND attname = 'your_column';

选择率的计算

选择率（Selectivity） 是 WHERE 条件过滤后返回的行数占总行数的比例[reference:20]。PostgreSQL 根据统计信息计算选择率，再用选择率乘以表的总行数（reltuples，来自 pg_class）得出估算行数[reference:21]。

等值条件（column = value） ：如果该值出现在 most_common_vals 中，直接取对应的 most_common_freqs 作为选择率[reference:22]。

范围条件（column < value） ：通过 histogram_bounds 直方图进行插值计算[reference:23]。例如，直方图将数据分为 100 个等频桶，目标值所在的桶位置决定了选择率。

统计信息的时效性

过期的统计信息会导致优化器做出错误决策。可通过 pg_stat_all_tables 视图检查统计信息的收集时间：

SELECT 
    schemaname,
    tablename,
    last_analyze,
    last_autoanalyze
FROM pg_stat_all_tables 
WHERE tablename = 'your_table';

如果 last_analyze 或 last_autoanalyze 时间过早，说明统计信息可能已过时，建议手动执行 ANALYZE。

多元统计信息：解决关联列估算失真

PostgreSQL 优化器默认假设不同列之间的条件是相互独立的[reference:24]。对于多列条件（如 WHERE col1 = a AND col2 = b），优化器会将各列的选择率相乘得出总选择率。

-- 假设两列的选择率均为 0.08
-- 总选择率 = 0.08 × 0.08 = 0.0064
-- 若表有 10000 行，估算行数 = 64
-- 但实际满足两条件的有 8000 行 → 严重低估！

当列之间存在函数依赖（Functional Dependency）时，这种独立性假设会导致估算严重失真[reference:25]。PostgreSQL 从 10 版本开始引入了多元统计信息（Extended Statistics） 来解决此问题[reference:26][reference:27]。

CREATE STATISTICS

CREATE STATISTICS 命令用于创建扩展统计信息对象[reference:28]。支持的统计类型包括[reference:29][reference:30]：

• ndistinct —— 多列组合的不同值数量统计
• dependencies —— 函数依赖统计
• mcv —— 多列最常见值列表

-- 创建函数依赖统计信息
CREATE STATISTICS stat_place_community (dependencies) 
ON place_name, community_name FROM your_table;

-- 分析表以生成统计信息
ANALYZE your_table;

-- 查看已创建的统计信息对象
SELECT * FROM pg_statistic_ext;

创建多元统计信息后，优化器在进行多列条件估算时会使用更精确的选择率，避免将两个相关列的选择率简单相乘[reference:31]。

版本支持说明

功能	引入版本
多元统计信息（ndistinct、dependencies、mcv）	PostgreSQL 10
表达式统计信息（Expression Statistics）	PostgreSQL 14

在 PostgreSQL 14 及更高版本中，CREATE STATISTICS 还支持基于表达式的统计信息收集[reference:32]。

执行计划可视化工具

当执行计划非常复杂（数百行）时，纯文本阅读效率低下。以下工具可将执行计划可视化，帮助快速定位高消耗节点：

PEV2（PostgreSQL Explain Visualizer 2）

PEV2 是一个基于 Vue.js 的执行计划可视化组件[reference:33]。

• 在线版：https://explain.dalibo.com/ [reference:34]
• GitHub：https://github.com/dalibo/pev2 [reference:35]

PEV2 的核心价值在于：

1. 图形化展示：将树形结构以节点图呈现，点击节点可查看详情
2. 耗时占比：直观显示每个节点占总执行时间的百分比
3. 估算偏差标注：自动标记 underestimate（低估）和 overestimate（高估）的节点

其他工具

• Depesz（https://explain.depesz.com/）：老牌执行计划分析工具，提供索引建议
• PGBadger：日志分析工具，可汇总执行计划信息

API 速览

EXPLAIN

所属：PostgreSQL SQL 命令

语法：

EXPLAIN [ ( option [, ...] ) ] statement

常用选项：

• ANALYZE：实际执行语句并显示真实执行统计[reference:36]
• BUFFERS：显示缓冲区使用情况[reference:37]
• COSTS：显示成本估算（默认开启）[reference:38]
• VERBOSE：显示额外详细信息[reference:39]
• FORMAT { TEXT | XML | JSON | YAML }：输出格式[reference:40]

示例：

EXPLAIN (ANALYZE, BUFFERS, COSTS) 
SELECT * FROM orders WHERE customer_id = 12345;

CREATE STATISTICS

所属：PostgreSQL DDL 命令

语法：

CREATE STATISTICS [ IF NOT EXISTS ] statistics_name 
    [ ( statistics_kind [, ...] ) ] 
    ON column_name, column_name [, ...] 
    FROM table_name;

statistics_kind 取值：

• ndistinct：多列组合的不同值数量
• dependencies：函数依赖
• mcv：多列最常见值列表[reference:41]

示例：

CREATE STATISTICS stat_customer_city (dependencies) 
ON customer_id, city FROM customers;

ANALYZE customers;

pg_settings

所属：PostgreSQL 系统视图

用途：查看和修改配置参数

示例：

-- 查看成本相关参数
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE name LIKE '%cost%' 
ORDER BY name;

pg_stats

所属：PostgreSQL 系统视图

用途：查看列级别的统计信息[reference:42]

示例：

SELECT 
    schemaname,
    tablename,
    attname,
    null_frac,
    avg_width,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats 
WHERE tablename = 'products' 
  AND attname = 'price';

pg_stat_all_tables

所属：PostgreSQL 系统视图

用途：查看表的统计信息收集时间

示例：

SELECT 
    relname,
    last_analyze,
    last_autoanalyze,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_all_tables 
WHERE relname = 'orders';

Demo 简单示例

本 Demo 演示如何通过 EXPLAIN ANALYZE 分析查询性能，并通过 CREATE STATISTICS 解决多列估算失真问题。

环境准备

-- 创建测试表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_category VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2)
);

-- 插入测试数据（10000 行）
INSERT INTO orders (customer_id, product_category, order_date, amount)
SELECT 
    (random() * 100)::INTEGER,
    (ARRAY['Electronics', 'Clothing', 'Books', 'Food', 'Toys'])[(random() * 4 + 1)::INTEGER],
    CURRENT_DATE - (random() * 365)::INTEGER,
    (random() * 1000)::NUMERIC(10,2)
FROM generate_series(1, 10000);

-- 收集统计信息
ANALYZE orders;

问题查询

-- 查询特定客户在特定类别的订单
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE customer_id = 42 
  AND product_category = 'Electronics';

在未创建多元统计信息时，优化器可能严重低估满足两个条件的行数。

创建多元统计信息

-- 创建函数依赖统计
CREATE STATISTICS stat_orders_customer_category (dependencies) 
ON customer_id, product_category FROM orders;

-- 重新收集统计信息
ANALYZE orders;

-- 再次执行查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders 
WHERE customer_id = 42 
  AND product_category = 'Electronics';

运行说明

1. 在 PostgreSQL 10+ 数据库中执行上述 SQL
2. 对比创建多元统计信息前后的 EXPLAIN ANALYZE 输出中的 rows 估算值
3. 观察估算行数是否更接近实际行数

技术点总结

• EXPLAIN ANALYZE 输出执行计划的估算值与实际值
• 多列条件在默认情况下存在估算失真
• CREATE STATISTICS 创建多元统计信息可显著提升估算准确性
• pg_stats 和 pg_stat_all_tables 用于查看统计信息状态

项目难点与解决方案

核心难点

多列关联导致的估算失真：PostgreSQL 优化器默认假设不同列的条件相互独立，将各列选择率相乘得出总选择率。当列之间存在函数依赖时，这种假设会导致行数严重低估，进而引发错误的连接方法选择（如 Nest Loop 替代 Hash Join），最终导致查询性能急剧下降[reference:43]。

解决方案

使用 CREATE STATISTICS 创建多元统计信息，具体包括：

1. 函数依赖统计（dependencies） ：捕捉列之间的函数依赖关系
2. 多列 MCV 统计（mcv） ：记录多列组合的最常见值及其频率
3. 多列 NDISTINCT 统计（ndistinct） ：记录多列组合的不同值数量

创建后执行 ANALYZE 使统计信息生效，优化器将使用更精确的选择率进行估算[reference:44]。

广度

该问题影响所有涉及多列条件的查询，包括：

• 多列 WHERE 条件
• 多列 GROUP BY
• 多列 JOIN 条件
• 多列 DISTINCT

深度

解决该问题需要理解：

• PostgreSQL 优化器的成本模型与统计信息机制
• 选择率的计算原理与独立性假设的局限性
• 多元统计信息的不同类型及其适用场景
• ANALYZE 的执行时机与统计信息更新策略

复杂度

• 诊断复杂度：需要通过 EXPLAIN ANALYZE 对比估算行数与实际行数，识别估算失真节点
• 实施复杂度：CREATE STATISTICS 语法简单，但需要选择正确的统计类型（dependencies、mcv 或 ndistinct）
• 维护复杂度：统计信息会随数据变更而老化，需确保 autovacuum 正常运作或定期执行 ANALYZE

官方文档

• EXPLAIN — PostgreSQL Documentation
• Using EXPLAIN — PostgreSQL Documentation
• CREATE STATISTICS — PostgreSQL Documentation
• pg_stats — PostgreSQL Documentation
• Planner Cost Constants — PostgreSQL Documentation

参考链接

• PEV2 — PostgreSQL Explain Visualizer
• PEV2 Online Demo
• Depesz Execution Plan Analyzer
• Multivariate Statistics Examples — PostgreSQL Documentation

总结

本文系统梳理了 PostgreSQL 执行计划的核心概念与优化器的工作原理。执行计划作为树形结构，遵循自底向上、从左往右的阅读规则。优化器基于成本模型选择执行路径，其中 seq_page_cost 与 random_page_cost 是影响索引选择的关键参数，尤其在 SSD/NVMe 时代需要进行针对性调优。

统计信息是行数估算的数据基础，pg_stats 视图提供了 histogram_bounds、most_common_vals 等关键信息。针对多列关联导致的估算失真问题，PostgreSQL 10+ 提供了 CREATE STATISTICS 多元统计信息功能，可有效提升优化器估算精度。在实际调优中，结合 EXPLAIN ANALYZE 与 PEV2 等可视化工具，可快速定位高消耗节点与估算偏差，实现高效的 SQL 性能优化。