锐客网

  1. 首页 > 睿知 > it技术 > >

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

IT知识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 一个可以调整查询代价的数据库】


    推荐阅读


    上一篇：可曾听闻大话二字—统计学R语言

    下一篇：MYSQL怎么发现处理没有commit 留下的“大”麻烦（）