PostgreSQL 一个可以调整查询代价的数据库

卧疾丰暇豫,翰墨时间作。这篇文章主要讲述PostgreSQL 一个可以调整查询代价的数据库相关的知识,希望能为你提供帮助。

大部分数据库对于查询中的Cost 评估的代价指标是不能进行变更的,假设如果我的系统从10000转的磁盘,变换为每秒能提供 1366MB/S 的SSD 查询评估的方法还是老的方法,这样对于数据库系统的查询性能有多少帮助? 


那到底PG 在这方面有什么特异功能,我们往下看,在这之前我们也需要知道PG 也是这些数据库中唯一的一个不能在语句中强制添加,并强制让他走索引
或不走索引的数据库。(pg_hint_plan可以解决这个问题)


下面就是一个查询中查看cost 的方法

下面我们更深入一点,从下面的两个图我看可以看出些什么,第一个图我们可以看到查询执行计划中Starup cost 是 0

下边这个查询的查询计划startup cost 中整体的cost 和 startup cost 是差不多的。



实际上 total cost 等于启动cost + 运行cost 



另外以第一个列子为例,顺序扫描是没有 startup cost的,仅仅有 operation cost 

总体的cost 是 2235 到底这个 2235 是怎么来的

我看一下bloom_table 的表行数和占用的PAGE 数。
通过以下公式    运行cost = (cpu 运行的cost  + 磁盘的运行cost ) * 多少行 + 顺序扫描的每页消耗 * 多少页面


(0.01 + 0.0025) *  100000  + 1.0 * 1235  =1250 +1235 = 2235 
其中 0.01  0.0025  1  分别是从上面图中的
seq_page_cost = 1.0 
cpu_tuple_cost = 0.01                   
cpu_operator_cost = 0.0025   
获得的,这也就说明一个语句的cost 是可以通过调整系统中的参数而进行变化的,其他的数据库在这方面基本上是不开放的。
下面我们在看看如果走了索引会怎么算cost   

走索引的cost 会包含启动成本,从读取索引的第一个tuple 开始,
开始的代价(走索引) = 取整log(2)(走了多少索引的行) +( Hindex  + 1) * 50 * CPU 运行的消耗

相关的消耗= 取整 (log2 100000 + (2+1)* 50)* 0.0025  = 0.42 (约等于实际是0.4175)


这里面有两个问题,1 HINDEX 到底是这么来的,这里面指的是索引的树高,其实可以通过这个公式来推出你的索引树有多高


运行的代价  (索引使用的CPU 代价 + 表使用CPU的代价) + (index_io 代价 + 表的io 代价)


在计算索引的代价中会涉及到选择率的问题,意思就是查询的谓词的频率的估计。
下面就是通过SQL 语句来给出每行的值来计算一个“采样率”的东西,也就是告诉你,这个行的值在整体的表中的占比。

这里由于计算比较麻烦,就不进行计算了,但这里需要注意的是
random_page_cost = 4.0 ,这个是在查询中使用索引计算 index_io_cost的一个标量,通过选择率 * index的page的数量 * random_page_cost 就可以得出索引的io cost ,而到底是走索引还是走全表扫描,执行计划会进行比对,如果走全表扫描会计算最小和最大的io cost,例如最大的 io_cost = 页面的数量 * random_page_cost 
所以调整 random_page_cost 的值会影响到底是走索引还是走全表扫描的选择性。 
下面可以举一个例子,我将配置文件中的random_page_cost 和 cpu_index_tuple_cost 进行调整,一个调小 一个调大,可以看到下图的结果,就算我有10万条记录,并且我查询的条件中的字段10万条那条都和那条不一样,并且也建立了相关的索引,最终的结果还是进行了全表扫描。



在将两个参数还原后,还是继续走原来的索引



说了这么多其实回到我开头说的问题,如果你的磁盘系统已经更改到SSD 磁盘则你的某些值是需要改变,否则可能会出现一些明明索引很好,但他选择全表扫描的情况。相关的例子请参见下面的链接
??https://www2014.aspxhtml.com/post-5768??


部分内容来自postgresql 书籍



【PostgreSQL 一个可以调整查询代价的数据库】


    推荐阅读