PostgreSQL CBO与统计信息
点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！当数据库执行一条SQL时，从发起SQL到返回结果集，数据库引擎通常有2种方式用来优化执行计划(执行计划详见《窥探PostgreSQL执行计划》),从而降低资源开销提升响应时间。具体如下：在CBO模式下，由于开销都是估算值，所以精确性严重依赖于统计信息(比如表大小,行数,字段数据分布情况,是否存在索引),当表的统计信息越接近表的真实数据时,CBO优化器的估算值则越准确,产生的执行计划也更佳准确 ; 当统计信息与真实数据差异过大时,CBO的估算值精确性将无法得到保证，这时往往产生错误的执行计划，从而引发SQL性能类故障。了解了CBO优化器以及统计信息的概述后，接下来我们将PostgreSQL为例进行实际案例演示：由于这个查询没有WHERE子句，它必须扫描表中的所有行，因此计划器只能选择使用一个简单的顺序扫描计划。被包含在圆括号中的数字是（从左至右）：
- 0.00,估计的启动开销。在输出阶段可以开始之前消耗的时间。
- 11173.00,估计的总开销。这个估计值基于的假设是计划结点会被运行到完成，即所有可用的行都被检索。
- rows=500000,这个计划结点输出行数的估计值。同样，也假定该结点能运行到完成。
- width=70,预计这个计划结点输出的行平均宽度（以字节计算）。
注意各个版本计算公式可能存在稍许差异，大致可以参考如下公式：总成本=seq_page_cost*relpages+cpu_tuple_cost*reltuples这里涉及了postgresql几个参数：
- seq_page_cost：连续块扫描操作的单个块的cost. 例如全表扫描.
- random_page_cost：随机块扫描操作的单个块的cost. 例如索引扫描.
- cpu_tuple_cost：处理每条记录的CPU开销（tuple：关系中的一行记录）.
- cpu_index_tuple_cost：扫描每个索引条目带来的CPU开销.
- cpu_operator_cost：操作符或函数带来的CPU开销.
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的统计收集器是一个支持收集和报告服务器活动信息的子系统。目前这个收集器可以对表和索引的访问计数，计数可以按磁盘块和个体行来进行。它还跟踪每个表中的总行数、每个表的清理和分析动作的信息。它也统计调用用户定义函数的次数以及在每次调用中花费的总时间。因为统计信息的收集给系统增加了一些额外负荷，系统可以被配置为自动收集或部分收集或不收集信息。这由配置参数控制，如下：
- track_activities允许监控当前被任意服务器进程执行的命令。
- track_counts 控制是否收集关于表和索引访问的统计信息。
- track_functions启用对用户定义函数使用的跟踪。
- track_io_timing启用对块读写次数的监控。
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优化器到统计信息的一个完整了解过程，本文就到此为止。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129247.html
