一次PostgreSQL行估算偏差导致的慢查询分析

1970阅读 1评论2017-10-02 skykiker
分类:Mysql/postgreSQL

本文为DBAPlus投稿文章, 原文链接: 

一次PostgreSQL行估算偏差导致的慢查询分析

问题

最近某业务系统上线了新功能,然后我们就发现PostgreSQL日志中多了很多慢查询。这些SQL语句都比较相似,下面是其中一个SQL的explain analyze执行计划输出。

这个SQL执行了18秒,从上面的执行计划不难看出,时间主要耗在两次嵌套join时对子表的顺序扫描(图中蓝线部分)。乘以5429的循环次数,每个join都要顺序扫描2000多万条记录。

分析

既然是顺序扫描惹的祸,那么在join列上加个索引是不是就可以了呢?

但是查看相关表定义后,发现在相关的表上已经有索引了;而且即使没有索引,PG也应该可以通过Hash join回避大量的顺序扫描。

再仔细看下执行计划里的cost估算,发现PG估算出的rows只有1行,而实际是5429(图中红线部分)。看来是行数估算的巨大偏差导致PG选错了执行计划。

为什么估算行数偏差这么大?

通过尝试,发现问题出在下面的过滤条件上。不加这个过滤条件估算行数和实际行数是基本吻合的,一加就相差的离谱。

Filter: (((zsize)::text = '2'::text) AND ((tmall_flg)::text = '1'::text)) 

而上面的zsite的数据类型是char(10),tmall_flg的数据类型是int,难道是类型转换惹的祸? 在测试环境把尝试去掉SQL里的类型转换,发现执行时间立刻从10几秒降到1秒以内。看来原因就是它了。

zsize::text = '2' AND tmall_flg::text = '1' 

==》

zsize = '2' AND tmall_flg = 1 

生产环境下,因为修改应用的SQL需要时间,临时采用下面的回避措施

alter table bi_dm.tdm_wh_zl057_rt alter zsize type varchar(10); 

即把zsize的类型从char(10)改成varchar(10)(varchar到text的类型转换不会影响结果行估算)。由于没有改tmall_flg,修改之后,估算的行数是79行,依然不准确。但是这带来的cost计算值的变化已经足以让PG选择索引扫描而不是顺序扫描了。修改之后的执行时间只有311毫秒。

原理

PG如何估算结果行数

PG通过收集的统计信息估算结果行数,并且收集的统计信息也很全面,包括唯一值数量,频繁值分布,柱状图和相关性,正常情况下应该是比较准确的。看下面的例子

相关代码

src/include/utils/selfuncs.h:

/* default selectivity estimate for equalities such as "A = b" */
#define DEFAULT_EQ_SEL  0.005 

src/backend/utils/adt/selfuncs.c:

Datum
eqsel(PG_FUNCTION_ARGS)
{
...

    /*
     * If expression is not variable = something or something = variable, then
     * punt and return a default estimate.
     */
    if (!get_restriction_variable(root, args, varRelid,
                                  &vardata, &other, &varonleft))
        PG_RETURN_FLOAT8(DEFAULT_EQ_SEL); 

总结

在条件列上引入计算带来的危害:

  1. 该列无法使用索引(除非专门定义与查询SQL匹配的表达式索引)
  2. 无法准确评估where条件匹配的结果行数,可能会引发连锁反应进而生成糟糕的执行计划

回避方法:

  1. 规范表的数据类型定义,避免不必要的类型转换
  2. 将计算从列转移到常量上

    比如:

    where c1 + 1 = 1000 

    改成

    where c1 = 1000 - 1 
  3. 改成其它等价的写法

    比如:

    where substring(c2,2) = 'ZC' 

    改成

    where c2 >= 'ZC' and c2 < 'ZD' 

    也可以改成更简洁的正则表达式

    where c2 ~ '^ZC' 

但是,正则表达式中如果带了类似^$*这样的内容,行数估算准确性也受一定的影响

上一篇:PostgreSQL字符类型长度变更的性能
下一篇:MySQL Utilities 高可用工具体验

文章评论