PostgreSQL——代价估计

2021SC@SDUSC

概述

我负责的PostgreSQL代码部分：查询的编译与执行
此篇博客的分析内容：代价估计 在前几篇博客中，我介绍了查询优化的一些步骤和相关函数。在PostgreSQL的查询优化过程中，查询请求的不同执行方案是通过建立不同的路径（Path）来表达的。在生成了许多符合条件的路径之后，要从中选择出代价最小的路径，把它转化为一个计划，传递给执行器执行。因此，优化器的核心工作是建立多条路径，然后从中找出最优的那一条。同一个查询请求有不同路径主要是因为：表的不同访问方式，表之间不同的连接方式，表之间不同的连接顺序等因素造成的。而评价路径优劣的依据是用系统表pg_statistic中的系统统计信息估计出的不同路径的代价（Cost）
某个路径的代价要考虑CPU代价和磁盘存取代价两方面。磁盘代价以从磁盘顺序存取一个页面的代价为单位，所有其他形式的代价计算都是相对磁盘存取代价来计算的。对于估算代价的参数有：

估算代价的参数	详细
seq_page_cost	顺序存取页的代价，值为1.0
random_page_cost	非顺序存取页的代价，值为4.0
cpu_tuple_cost	典型的CPU处理一个元组的代价，值为0.01
cpu_index_tuple_cost	典型的CPU处理一个索引元组的代价，值为0.005
cpu_operator_cost	CPU处理一个典型的WHERE操作的代价，值为0.0025
effective_cache_size	用来量度PostgreSQL和OS 缓存的磁盘页的数量，值为16384

一个路径的代价由三部分组成：启动代价（Startup Cost),总代价（Toal Cost)，执行结果的排序方式（PathKeys）计算启动和总代价所用的参数如下：
（1）基本参数：
表元组数ntuples
表磁盘块数nblocks
符合选择条件的元组数nrows
（2）统计信息：
查询规划器需要估计一个查询检索的元组的数目，这样才能选择正确的查询规划，统计信息的主要内容就是每个表和索引中的元组总数，以及每个表和索引占据的磁盘块数。这个信息保存在系统表pg_class的reltuples和relpages属性中
（3）直方图信息（存储在系统表中）：各个属性值出现次数的统计信息
路径的代价估算的基本步骤都是相似的：首先根据统计信息和查询条件，估算出这次查询要进行的I/O次数以及要取出的元组个数，然后根据元组个数（分为表元组和索引元组）计算出CPU的代价，最后综合考虑CPU代价和I/O次数（磁盘代价）即可得到最后的代价。不同的路径类型有不同的代价估算方式。

代价估算公式

代价估算从I/O次数和 CPU 开销两个方面考虑，估算公式为P+W*T。其中，P表示在执行时所要访问的页面数，反映了磁盘I/O次数；工表示在执行时所要访问的元组数，反映了 CPU 开销;W 表示磁盘I/O代价和 CPU 开销间的权重因子。通过对估算公式的分析可以发现，计算代价时只需考虑访问的页面数和元组数。

选择度

选择度(selectivity）用来定量地描述前述代价估算公式中的权重因子W。选择度的计算要综合考虑以下各类参数：约束条件中的操作符、约束常量、索引中的元组数、某字段的最大值和最小
值等。下面列出了对于选择度计算需要考虑的因素。

约束条件	选择度
r. field = value	1/字段r.field 上所定义的索引关系的元组数
r. field > value	（字段r.field 的最大值-value)1(字段r.field 的最大值一字段r.field的最小值）
r. field < value	（value-字段rfield 的最小值)1（字段r.field 的最大值一字段r.field的最小值）

单个表的扫描代价

计算单个表的扫描代价时，需要将表的大小与各个约束条件所对应的选择度相乘，如下图所示。

扫描方式	P	T
顺序扫描	NumPages	NumTuples
一级索引扫描	NumPages * F	NumTuples * F
二级索引扫描	NumPages * F + ITuples * F	ITuples * F + NumTuples * F

其中，NumPages 表示表的页面数；NumTuples 表示表的元组数；ITuples 表示索引表的元组数；F表示多个约束条件组合后的选择度。

两个表的连接代价

两个表的连接代价计算方法如下图所示：

连接方式	代价计算公式
嵌套循环连接	Couter + Nouter * Cinner
归并连接	Couter+ Csortouter + Cinner + Csortinner
Hash 连接	Couter+ Ccreatehash + Nouler * Chash

其中：
Couter 表示扫描外连接表的代价。
Cinner 表示扫描内连接表的代价。
Csortouter 表示将外连接表进行排序的代价（使用临时存储空间）。
Csortinner：表示内连接表进行排序的代
价（使用临时存储空间）。
Ccreatehash 表示对内连接表进行 Hash 的
代价（使用临时存储空间）。
Chash 表示单独的Hash 的代价。
Nouter 表示外连接表的大小。

总结

通过本篇博客，了解了代价估计的方法和涉及的参数，感谢批评指正！