PostgreSQL查询优化器如何利用统计信息

PostgreSQL 查询优化器基础

在深入探讨 PostgreSQL 查询优化器如何利用统计信息之前，我们先来了解一下查询优化器的基础概念。查询优化器在整个数据库系统中扮演着至关重要的角色，它的主要职责是将用户提交的 SQL 查询语句转换为高效的执行计划。

PostgreSQL 的查询优化器采用基于代价的优化（Cost - Based Optimization，CBO）策略。这意味着优化器会根据不同执行计划的预估代价来选择最优的执行方案。代价模型是基于多个因素构建的，其中统计信息起着核心作用。

查询优化器的工作流程

1. 解析与分析：首先，查询语句会被解析器分解成语法树，这个语法树会被进一步分析，检查查询的语义是否正确，例如表和列的存在性、数据类型的兼容性等。
2. 生成逻辑查询计划：基于分析后的语法树，优化器会生成多个逻辑查询计划。这些逻辑计划描述了查询操作的顺序和方式，但不涉及具体的物理实现，例如连接操作可以是嵌套循环连接、哈希连接或者排序合并连接等不同的逻辑形式。
3. 转换为物理查询计划：将逻辑查询计划转换为物理查询计划，这一步会考虑实际的存储结构、索引使用情况等物理因素。每个逻辑操作都有多种物理实现方式，例如扫描表可以是顺序扫描或者索引扫描，优化器需要为每个操作选择最合适的物理实现。
4. 代价评估与选择：对生成的多个物理查询计划，优化器使用代价模型来评估每个计划的执行代价。代价模型会考虑诸如磁盘 I/O、CPU 处理时间等因素。最终，选择代价最小的物理查询计划作为执行计划。

统计信息概述

统计信息是关于数据库中数据分布的元数据，它为查询优化器提供了关键的输入，以便优化器能够更准确地评估执行计划的代价。

统计信息的类型

1. 表统计信息
   • 行数：表中的总行数是一个基本且重要的统计信息。优化器可以根据表的行数来估计扫描表所需的 I/O 和 CPU 代价。例如，如果一个表只有几百行，那么顺序扫描可能是一个高效的操作；但如果表有几百万行，索引扫描可能更合适，而表行数的统计信息有助于优化器做出这样的判断。
   • 平均行大小：了解表中每行数据的平均大小对于估计 I/O 代价很有帮助。如果平均行大小较大，那么读取相同数量的行需要更多的 I/O 操作。例如，在计算表扫描的代价时，优化器会结合行数和平均行大小来预估需要读取的数据量。
2. 列统计信息
   • 唯一值数量：列中的唯一值数量（也称为基数）能帮助优化器判断选择性。选择性高（即唯一值数量多）的列在过滤条件中使用时，更有可能显著减少结果集的大小。例如，如果一个列的唯一值数量接近表的行数，那么在该列上使用 WHERE 子句过滤条件会大大缩小结果集，优化器可能会选择基于该列的索引扫描。
   • 数据分布直方图：直方图提供了列数据值的分布信息。它将列的值域划分为多个桶（bucket），并记录每个桶中的值的数量。通过直方图，优化器可以更准确地估计过滤条件的选择性。例如，对于一个数值列，如果直方图显示大部分值集中在某个区间，那么在该区间的过滤条件会有不同的选择性估计，与均匀分布的情况不同。
3. 索引统计信息
   • 索引基数：索引中的唯一键值数量。与列的唯一值数量类似，但索引基数考虑了索引的构建方式，例如复合索引。如果索引基数高，说明索引能够有效地缩小搜索空间，优化器可能会倾向于使用该索引。
   • 索引叶块数：索引的物理存储结构中，叶块的数量反映了索引的大小和层次结构。优化器可以根据索引叶块数来估计索引扫描的 I/O 代价。例如，如果一个索引有较少的叶块，那么读取索引数据所需的 I/O 操作就会相对较少。

统计信息的收集

PostgreSQL 使用 ANALYZE 命令来收集统计信息。

-- 对整个表进行统计信息收集
ANALYZE table_name;
-- 对特定列进行统计信息收集
ANALYZE VERBOSE table_name (column_name);

ANALYZE 命令会扫描表的数据，并更新系统目录中的统计信息。VERBOSE 选项可以提供关于统计信息收集过程的详细输出。

查询优化器如何利用表统计信息

估计表扫描代价

1. 顺序扫描代价估计：当优化器考虑对表进行顺序扫描时，它会根据表的行数和平均行大小来估计 I/O 代价。假设磁盘 I/O 代价模型中，每次磁盘块读取的代价为 seq_page_cost（PostgreSQL 配置参数，默认值为 1.0），而表的总行数为 n_rows，平均行大小为 avg_row_size，磁盘块大小为 block_size。那么顺序扫描的 I/O 代价可以大致估计为： [ \text{seq_scan_io_cost}=\frac{n_rows\times avg_row_size}{block_size}\times seq_page_cost ] 例如，假设表有 10000 行，平均行大小为 100 字节，磁盘块大小为 8192 字节，seq_page_cost 为 1.0。则顺序扫描的 I/O 代价为： [ \frac{10000\times100}{8192}\times1.0\approx122.07 ]
2. 索引扫描代价估计：索引扫描的代价估计更为复杂，它不仅依赖于表的行数，还与索引结构相关。优化器会考虑索引的层数、叶块数等因素。假设索引层数为 index_levels，每次索引块读取的代价为 idx_page_cost（PostgreSQL 配置参数，默认值为 1.0），索引叶块数为 index_leaf_blocks。则索引扫描的 I/O 代价可以大致估计为： [ \text{index_scan_io_cost}=index_levels + \frac{index_leaf_blocks}{index_fanout}\times idx_page_cost ] 其中 index_fanout 是索引扇出（即每个非叶节点指向的子节点数量）。例如，一个索引有 3 层，叶块数为 100，索引扇出为 100，idx_page_cost 为 1.0。则索引扫描的 I/O 代价为： [ 3+\frac{100}{100}\times1.0 = 4 ]

连接操作代价估计

在多表连接时，表统计信息用于估计连接结果集的大小，进而估计连接操作的代价。

1. 嵌套循环连接：对于嵌套循环连接，优化器会假设外层循环的每一行都与内层循环的所有行进行匹配。假设表 A 有 n_A 行，表 B 有 n_B 行。如果连接条件没有选择性（即所有行都匹配），那么连接结果集的行数估计为 n_A * n_B。连接的代价主要包括外层表的扫描代价以及对内层表的多次扫描代价。
   • 例如，表 A 顺序扫描代价为 cost_A，表 B 顺序扫描代价为 cost_B。嵌套循环连接的代价可以估计为： [ \text{nested_loop_cost}=cost_A+(n_A\times cost_B) ]
2. 哈希连接：哈希连接首先构建一个哈希表，通常是对较小的表构建哈希表。优化器需要估计构建哈希表的代价以及探测哈希表的代价。构建哈希表的代价与表的行数和平均行大小有关，探测哈希表的代价与另一个表的行数有关。假设表 A 较小，用于构建哈希表，其行数为 n_A，平均行大小为 avg_row_size_A，表 B 用于探测，行数为 n_B。构建哈希表的代价可以大致估计为与 n_A * avg_row_size_A 相关的一个值，探测哈希表的代价与 n_B 相关。
   • 例如，构建哈希表的代价为 build_hash_cost，探测哈希表的代价为 probe_hash_cost，则哈希连接的代价为： [ \text{hash_join_cost}=build_hash_cost + probe_hash_cost ]

查询优化器如何利用列统计信息

过滤条件选择性估计

1. 简单比较条件：对于简单的比较条件，如 column_name = value，优化器使用列的唯一值数量（基数）来估计选择性。假设列 column_name 的唯一值数量为 n_distinct，表的总行数为 n_rows。如果数据是均匀分布的，那么条件 column_name = value 的选择性可以估计为： [ \text{selectivity}=\frac{1}{n_distinct} ] 例如，表有 1000 行，列 column_name 有 100 个唯一值，则条件 column_name = 'value' 的选择性估计为 \frac{1}{100}=0.01，即结果集预计为 1000 * 0.01 = 10 行。
2. 范围条件：对于范围条件，如 column_name BETWEEN value1 AND value2，优化器使用直方图来估计选择性。假设直方图将列的值域划分为 n_buckets 个桶，每个桶覆盖一定的范围。优化器可以根据 value1 和 value2 所在的桶以及桶内的值的分布来估计选择性。
   • 例如，直方图有 10 个桶，value1 落在第 3 个桶，value2 落在第 5 个桶。如果第 3 个桶包含 100 个值，第 4 个桶包含 150 个值，第 5 个桶包含 120 个值，且表总行数为 1000。那么范围条件的选择性可以估计为： [ \text{selectivity}=\frac{100 + 150+120}{1000}=0.37 ]

索引选择

1. 单列索引：列统计信息中的基数对于索引选择至关重要。如果列的基数较高，且在查询中经常用于过滤条件，那么在该列上创建的单列索引更有可能被优化器选择。例如，对于一个客户表，customer_id 列通常是唯一的（基数高），如果查询经常根据 customer_id 进行过滤，如 SELECT * FROM customers WHERE customer_id = 123;，优化器很可能会选择 customer_id 列上的索引进行扫描。
2. 复合索引：在复合索引的情况下，列的顺序和基数都很重要。优化器会根据查询条件中列的使用顺序和选择性来决定是否使用复合索引。例如，有一个复合索引 (col1, col2)，如果查询条件是 WHERE col1 = 'value1' AND col2 = 'value2'，且 col1 的基数较高，优化器可能会选择该复合索引。但如果查询条件只有 col2 = 'value2'，由于复合索引的最左前缀原则，优化器可能不会选择该复合索引，除非 col2 单独有很高的选择性。

查询优化器如何利用索引统计信息

索引扫描选择

1. 索引基数与选择性：索引基数直接影响优化器对索引扫描的选择。如果索引基数接近表的总行数，说明该索引的选择性较低，优化器可能更倾向于顺序扫描。例如，假设表有 10000 行，某个索引的基数只有 100，这意味着该索引只能区分 1%的数据，顺序扫描可能更高效。反之，如果索引基数较高，如 9000，优化器会更有可能选择索引扫描。
2. 索引叶块数与 I/O 代价：索引叶块数决定了索引扫描的 I/O 代价。如前面提到的索引扫描 I/O 代价估计公式，叶块数越少，索引扫描的 I/O 代价越低。优化器在评估索引扫描和其他扫描方式（如顺序扫描）时，会考虑索引叶块数。例如，一个索引有 100 个叶块，另一个索引有 500 个叶块，在其他条件相同的情况下，优化器会更倾向于选择叶块数少的索引进行扫描，因为其 I/O 代价更低。

索引与连接操作

1. 嵌套循环连接中的索引使用：在嵌套循环连接中，如果内层表在连接条件的列上有合适的索引，优化器可以利用索引来减少内层表的扫描次数。例如，对于连接 SELECT * FROM A JOIN B ON A.id = B.a_id;，如果表 B 在 a_id 列上有索引，那么对于表 A 的每一行，优化器可以通过索引快速定位表 B 中匹配的行，而不是全表扫描表 B。这样可以大大降低连接的代价。
2. 排序合并连接中的索引使用：排序合并连接要求连接的列上有排序顺序。如果相关列上有索引，优化器可以利用索引的排序特性来满足排序合并连接的要求。例如，有两个表 A 和 B，连接条件为 A.sort_col = B.sort_col，如果表 A 和表 B 在 sort_col 列上都有索引，优化器可以使用索引扫描来获取按 sort_col 排序的数据，从而有效地执行排序合并连接。

示例分析

示例表与查询

我们创建两个示例表 employees 和 departments，并插入一些数据。

-- 创建 departments 表
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
-- 创建 employees 表
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- 插入数据
INSERT INTO departments (department_name) VALUES ('HR'), ('Engineering'), ('Sales');
INSERT INTO employees (employee_name, department_id, salary) VALUES
('Alice', 1, 5000.00),
('Bob', 2, 6000.00),
('Charlie', 1, 5500.00),
('David', 3, 4800.00);

假设我们有一个查询：

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

统计信息对查询优化的影响

1. 表统计信息：优化器会根据 employees 表和 departments 表的行数、平均行大小等统计信息来估计表扫描和连接操作的代价。如果 employees 表行数较多，平均行大小较大，顺序扫描的 I/O 代价就会较高，优化器可能会考虑其他扫描方式，如索引扫描。
2. 列统计信息：对于 employees.salary 列，优化器会根据其基数和直方图来估计 e.salary > 5000 条件的选择性。如果 salary 列的唯一值数量较多，且直方图显示大部分值小于 5000，那么该条件的选择性较高，结果集预计较小。这会影响优化器对后续连接操作的估计，因为连接的输入数据量减少了。
3. 索引统计信息：假设在 employees.salary 列上有一个索引，优化器会根据该索引的基数和叶块数来决定是否使用该索引进行扫描。如果索引基数较高，叶块数较少，索引扫描的代价可能较低，优化器会选择索引扫描来获取满足 salary > 5000 条件的行，然后再与 departments 表进行连接。

通过上述分析可以看出，统计信息在 PostgreSQL 查询优化器生成高效执行计划的过程中起着关键作用，准确的统计信息能够帮助优化器做出更合理的决策，从而提高查询性能。