PostgreSQL CBO与统计信息

当数据库执行一条SQL时，从发起SQL到返回结果集，数据库引擎通常有2种方式用来优化执行计划(执行计划详见《窥探PostgreSQL执行计划》),从而降低资源开销提升响应时间。具体如下：

1. 基于规则的优化器(rbo，rule based optimizer),在上古版本中的数据库较为常见,比如过去常听DBA说的SQL语句驱动表要放前面，被驱动表要放后面之类的云云，听的好像很深奥，究其本质为数据库引擎按照固定规则去优化执行计划;这样的规则往往较为简单比如是否有索引、驱动表是否在前等等。举个例子，汽车导航中始发地到目的地之间有多种路线,rbo中就是按距离规则来计算，不考虑实际路况比较傻瓜化,现在基本都已经淘汰。

2. 基于成本的优化器(cbo，cost based optimizer),同样的汽车导航例子,cbo中还需要参考路况是否存在堵车,是否存在限速等等,通过对比各种行程的耗时从而选择最合适的路线。在数据库中就是通过对比各种不同计划(是采用hashjoin 还是nestloop,采用全表扫描还是索引扫描等等)的估算开销(cost), 从中选择开销(cost)最低的计划,相对RBO的呆板,CBO显得较为智能，目前主流数据库均采用CBO模式。

在CBO模式下，由于开销都是估算值，所以精确性严重依赖于统计信息(比如表大小,行数,字段数据分布情况,是否存在索引),当表的统计信息越接近表的真实数据时,CBO优化器的估算值则越准确,产生的执行计划也更佳准确 ; 当统计信息与真实数据差异过大时,CBO的估算值精确性将无法得到保证，这时往往产生错误的执行计划，从而引发SQL性能类故障。

了解了CBO优化器以及统计信息的概述后，接下来我们将PostgreSQL为例进行实际案例演示：

先看一个简单的执行计划：

由于这个查询没有WHERE子句，它必须扫描表中的所有行，因此计划器只能选择使用一个简单的顺序扫描计划。被包含在圆括号中的数字是（从左至右）：

1. 0.00,估计的启动开销。在输出阶段可以开始之前消耗的时间。

2.11173.00,估计的总开销。这个估计值基于的假设是计划结点会被运行到完成，即所有可用的行都被检索。

3.rows=500000,这个计划结点输出行数的估计值。同样，也假定该结点能运行到完成。

4.width=70,预计这个计划结点输出的行平均宽度（以字节计算）。

注意各个版本计算公式可能存在稍许差异，大致可以参考如下公式

总成本=seq_page_cost*relpages+cpu_tuple_cost*reltuples

这里涉及了postgresql几个参数：

当前实例参数默认设置：

当前表统计信息:

带入公式计算:

1*6173+0.01*500000 =11173;与前文Cost值11173对应。

再来看一个索引扫描的执行计划：

这里是索引扫描，注意各个版本计算公式可能存在稍许差异，大致可以参考如下公式：

cost= 2*random_page_cost + cpu_tuple_cost +cpu_index_tuple_cost +100* cpu_operator_cost

其中random_page_cost *2 为先读取indexpage, 再回表 datapage从而进行了2次随机IO。

我们在来看一个统计信息差异过大导致的执行计划错误的案例：

可以看到在行数较少时，虽然tid字段有索引，但是CBO考虑到索引的随机扫描(random_page_cost)且需要加上回表(再次random_page_cost)的总Cost大于全表的seq_page_cost,则执行计划选择了错误的全表扫描方式。

注：

Cost全表=1* 4+ 59*0.01 = 4.59,与图中估算值1.74存在些许差异

Cost索引=2*4+0.01+0.25=8.26,与图中估算值8.44存在些许差异

由于全表Cost比索引Cost低，则CBO优化器将选择全表扫描方式.

在我们发起analyzetable更新统计信息后,CBO根据page以及tuple重新进行Cost计算并及时更新执行计划为索引扫描。

注：

Cost全表=3703*4 + 299908*0.01 =15111;大致估算值

Cost索引=2*4+0.01+0.25=8.26,与图中估算值8.44存在些许差异

由于索引Cost比全表Cost低，则CBO优化器将选择索引扫描方式.

由于统计信息的准确性将直接影响到CBOCost计算的准确性，可以确认统计信息的维护工作将是DBA日常工作中的重中之重。下面我们在来看一下PostgreSQL中的统计信息知识点。

 PostgreSQL的统计收集器是一个支持收集和报告服务器活动信息的子系统。目前这个收集器可以对表和索引的访问计数，计数可以按磁盘块和个体行来进行。它还跟踪每个表中的总行数、每个表的清理和分析动作的信息。它也统计调用用户定义函数的次数以及在每次调用中花费的总时间。因为统计信息的收集给系统增加了一些额外负荷，系统可以被配置为自动收集或部分收集或不收集信息。这由配置参数控制，如下：

这里我们将只对表级统计信息收集进行介绍:

语法如图：

Verbose选项输出详细的INFO信息,包含正常行，已删除的行,数据page等等信息。

注:由于pg的mvcc采用tuple模式,删除的行只是标记为delete状态，数据仍然保留在表中，通过deadrows可用用来作为vacuum的判断依据,这里vacuum不在展开介绍。

表的统计信息收集采样比例可以在表列级指定或使用系统参数default_statistics_target值;该值范围在1-10000，可以理解为值越大采样比例越高，耗时越长,默认值为100。日常运维过程中当发现采样比例不够时可在表列级扩大该值。

一旦指定列的statistics值后，PostgreSQL就不再参考默认的default_statistics_target值，它会先去系统表pg_attribute的对应表对应字段的attstattarget值，如果是-1，表示的是该列的取样颗粒度是采用默认的值（default_statistics_target），如果是大于0的，那么就表示是使用着自己手动定义的，比如我们修改t_user.tid通过STATISTICS150，查看attstattarget值的变化：

图中tid列的statisticstarget值已更新为150.以上就是一个从CBO优化器到统计信息的一个完整了解过程，本文就到此为止，咱们下回见。