梧桐数据库（WuTongDB）：postgresql 12的CBO（Cost-Based Optimizer）优化器
PostgreSQL 12 的 CBO（基于代价的优化器，Cost-Based Optimizer）是其查询优化的核心组件，用于生成高效的查询执行计划。PostgreSQL 的 CBO 通过估算各种执行路径的代价，选择最优的路径来执行 SQL 查询。
PostgreSQL 12 CBO 的主要特点
-
基于代价的优化：
PostgreSQL 的 CBO 通过计算不同执行计划的代价来决定查询的执行方式。这种代价模型会考虑 CPU、I/O、内存消耗，并基于这些资源估算每种查询执行方案的成本，选择成本最低的方案。 -
丰富的统计信息支持：
CBO 依赖于数据库的统计信息来估算查询的代价，PostgreSQL 12 中的ANALYZE命令可以收集表、列和索引的统计信息，帮助优化器做出更准确的决策。 -
复杂查询优化：
PostgreSQL 12 CBO 能处理复杂的 SQL 查询，包括：- 多表连接查询（Joins）
- 子查询、CTE（公用表表达式）
- 聚合查询
- 窗口函数等
-
增量分析：
PostgreSQL 12 引入了一些新的查询优化功能，包括增量统计分析，通过结合更准确的列统计，特别是在多列有复杂依赖关系时，能生成更高效的执行计划。
PostgreSQL 12 CBO 的工作流程
1. 查询解析与重写
当 PostgreSQL 收到一个 SQL 查询时，它首先会将查询解析为语法树。之后，查询优化器会对这个语法树进行查询重写，比如将子查询转化为 JOIN 或者合并一些可以简化的操作。
2. 生成候选执行计划
PostgreSQL 的 CBO 会根据查询的结构，生成一系列的候选执行计划。对于一个简单的查询，可能有多种执行方式可供选择，比如：
- 顺序扫描（Sequential Scan）：依次扫描整个表。
- 索引扫描（Index Scan）：通过索引来定位特定的行。
- 哈希连接（Hash Join）：对于表连接，使用哈希连接来减少计算代价。
- 嵌套循环连接（Nested Loop Join）：适合小规模数据集的表连接。
- 合并连接（Merge Join）：适合排序好的数据。
3. 代价估算
每个候选计划都会进行代价评估，CBO 使用的代价模型包括以下几个方面：
- CPU代价：处理每一行数据所需的CPU资源。
- I/O代价：读取数据所需的磁盘I/O操作资源。
- 内存代价：内存操作，特别是涉及排序、哈希等操作时。
PostgreSQL 使用统计信息来估算每种计划的代价。统计信息包括：
- 表的行数。
- 列的基数（Cardinality），即列中不同值的数量。
- 数据分布情况（直方图）。
- 索引选择性（即使用索引可以减少多少扫描行数）。
4. 选择最优执行计划
在所有生成的执行计划中，CBO 会选择代价最小的执行计划作为最终的执行方案。这一步决定了查询执行的具体步骤，比如是否使用索引、如何连接多个表等。
关键优化技术
PostgreSQL 12 的 CBO 在执行查询时，使用了多种优化技术来提高性能：
1. 索引扫描与顺序扫描
- 顺序扫描（Seq Scan）：当表的大小较小或查询需要扫描大部分数据时，PostgreSQL 会选择顺序扫描。
- 索引扫描（Index Scan）：当索引能够显著减少查询中的扫描行数时，优化器会选择索引扫描。
2. 表连接优化
PostgreSQL 12 支持多种表连接方式，CBO 根据表的大小和数据分布来选择最优的连接方式：
- 嵌套循环连接（Nested Loop Join）：适用于小表或索引辅助连接。
- 哈希连接（Hash Join）：适用于大表连接和无索引的情况。
- 合并连接（Merge Join）：适用于已经排序好的数据。
3. 子查询优化
PostgreSQL 12 的 CBO 可以将一些子查询转化为更高效的等效操作。例如，将子查询中的 IN 操作符转化为表连接（JOIN），减少嵌套循环带来的开销。
4. CTE（公用表表达式）优化
在 PostgreSQL 12 之前，CTE 是一种"优化屏障"，即CBO不会进一步优化其查询计划。然而，从 PostgreSQL 12 开始，优化器可以内联 CTE，从而对其进行进一步的优化，这大大提高了复杂查询的性能。
5. 增量统计信息
PostgreSQL 12 引入了对增量统计信息的支持，用于处理多个列之间的依赖关系。传统的列统计通常假设列之间是独立的，但在实际场景中，列间可能存在某种关联性（如性别和名字）。增量统计信息能够帮助优化器更好地评估查询的选择性，并为复杂的查询生成更准确的代价估算。
6. 并行查询优化
PostgreSQL 12 对并行查询的支持进一步增强，CBO 可以评估并行执行的代价，决定是否将查询分解为多个子任务并行执行。并行执行能够显著加快大规模数据查询，尤其是对于扫描大表或执行复杂聚合的场景。
统计信息的重要性
PostgreSQL CBO 的工作效率高度依赖于准确的统计信息。统计信息不准确或过时可能导致优化器选择非最优的执行计划。常用的统计信息包括：
- ANALYZE：用于收集表、列和索引的统计信息。执行
ANALYZE命令能够帮助优化器做出更好的决策。 - pg_statistic 系统表：存储统计信息，优化器会使用这些信息估算表的行数、列的基数、数据分布等。
PostgreSQL 12 CBO 优化示例
假设我们有一个包含大量行的表 employees，其中包含如下结构：
CREATE TABLE employees (
emp_id serial PRIMARY KEY,
name varchar(100),
department varchar(50),
salary int
);
我们需要执行以下查询：
SELECT * FROM employees WHERE department = 'Sales';
在这个查询中，PostgreSQL 12 的 CBO 将执行以下步骤：
- 查询解析与重写：PostgreSQL 首先解析查询，识别出
WHERE子句中的条件department = 'Sales'。 - 生成候选执行计划：
- 顺序扫描：扫描整个
employees表，检查每一行的department是否为Sales。 - 索引扫描：如果
department列上有索引，CBO 可能选择索引扫描来减少扫描行数。
- 顺序扫描：扫描整个
- 代价估算：
- 对顺序扫描，代价包括读取整个表的磁盘 I/O 代价和逐行检查的 CPU 代价。
- 对索引扫描，代价包括扫描索引的代价以及通过索引查找到匹配行的成本。
- 选择执行计划：如果
department列上的索引能够显著减少扫描的行数，CBO 会选择索引扫描，否则选择顺序扫描。
执行计划可以通过 EXPLAIN 语句查看：
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';
这个查询会显示优化器选择的执行计划以及每种操作的估算代价。
总结
PostgreSQL 12 的 CBO 是一个功能强大、灵活性极高的优化器，它通过代价估算来生成最优的查询执行计划。在PostgreSQL 12中，CBO 不仅能处理复杂的查询优化任务，还能更好地利用并行查询、增量统计等新特性，显著提高数据库性能。要确保 CBO 选择正确的执行计划，准确、最新的统计信息至关重要。因此，定期运行 ANALYZE 命令，保持统计信息的准确性，是确保数据库查询效率的最佳实践。
产品简介
- 梧桐数据库（WuTongDB）是基于 Apache HAWQ 打造的一款分布式 OLAP 数据库。产品通过存算分离架构提供高可用、高可靠、高扩展能力，实现了向量化计算引擎提供极速数据分析能力，通过多异构存储关联查询实现湖仓融合能力，可以帮助企业用户轻松构建核心数仓和湖仓一体数据平台。
- 2023年6月，梧桐数据库（WuTongDB）产品通过信通院可信数据库分布式分析型数据库基础能力测评，在基础能力、运维能力、兼容性、安全性、高可用、高扩展方面获得认可。
点击访问：
梧桐数据库（WuTongDB）相关文章
梧桐数据库（WuTongDB）产品宣传材料
梧桐数据库（WuTongDB）百科