大部分数据库对于查询中的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 磁盘则你的某些值是需要改变,否则可能会出现一些明明索引很好,但他选择全表扫描的情况。
本文分享自 AustinDatabases 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!