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

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 本文为DBAPlus投稿文章, 原文链接: http://dbaplus.cn/news-19-1514-1.html 一次PostgreSQL行估算偏差导致的慢查询分析 问题 最近某业务系统上线了新功能,然后我们就发现PostgreSQL日志中多了很多慢查询。

本文为DBAPlus投稿文章, 原文链接: http://dbaplus.cn/news-19-1514-1.html

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

  • 无where条件

    postgres=# explain select * from bi_dm.tdm_wh_zl057_rt;
                                    QUERY PLAN                                 
    ---------------------------------------------------------------------------
     Seq Scan on tdm_wh_zl057_rt  (cost=0.00..81318.21 rows=2026121 width=154)
    (1 row) 

    全表数据的估算值来自pg_class

    postgres=# select reltuples from pg_class where relname='tdm_wh_zl057_rt';
     reltuples 
    -----------
       2026121
    (1 row) 

    估算值和实际值的误差只有5%左右

    postgres=# select count(*) from bi_dm.tdm_wh_zl057_rt;
      count  
    ---------
     2103966
    (1 row)
  • 带等值where条件

    postgres=# explain select * from bi_dm.tdm_wh_zl057_rt where tmall_flg = 1;
                                    QUERY PLAN                                
    --------------------------------------------------------------------------
     Seq Scan on tdm_wh_zl057_rt  (cost=0.00..86403.32 rows=523129 width=154)
       Filter: (tmall_flg = 1)
    (2 rows) 

    带where条件后,PG根据pg_stats收集的列值分布信息估算出where条件的选择率。tmall_flg = 1属于频繁值,most_common_freqs中直接记录了其选择率为0.258133322

    postgres=# select * from pg_stats where tablename='tdm_wh_zl057_rt' and attname='tmall_flg';
    -[ RECORD 1 ]----------+--------------------------------------
    schemaname             | bi_dm
    tablename              | tdm_wh_zl057_rt
    attname                | tmall_flg
    inherited              | f
    null_frac              | 0.00033333333
    avg_width              | 4
    n_distinct             | 5
    most_common_vals       | {0,1,2}
    most_common_freqs      | {0.626866639,0.258133322,0.114566669}
    histogram_bounds       | {3,4}
    correlation            | 0.491312951
    most_common_elems      | 
    most_common_elem_freqs | 
    elem_count_histogram   | 

    结合总记录数,可以算出估算结果行数。

    postgres=# select 2026121*0.258133322;
         ?column?     
    ------------------
     523009.344503962
    (1 row) 

    估算值和实际值的误差只有1%左右

    postgres=# select count(*) from bi_dm.tdm_wh_zl057_rt where tmall_flg = 1;
     count  
    --------
     532630
    (1 row) 
  • 带等值where条件,且条件列带类型转换

    postgres=# explain select * from bi_dm.tdm_wh_zl057_rt where tmall_flg::text = '1';
                                   QUERY PLAN                                
    -------------------------------------------------------------------------
     Seq Scan on tdm_wh_zl057_rt  (cost=0.00..96561.46 rows=10131 width=155)
       Filter: ((tmall_flg)::text = '1'::text)
    (2 rows) 

    一旦在条件列上引入包括类型转换,函数调用之类的计算,PG就无法通过pg_stats计算选择率了,于是笼统的采用了一个0.005的默认值。通过这个默认的选择率计算的结果行数可能会和实际结果行数有巨大的偏差。如果where条件中这样的列不止一个,偏差会被进一步放大。

    postgres=# select 2026121*0.005;
     ?column?  
    -----------
     10130.605
    (1 row) 

相关代码

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 
    			

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

    where c2 ~ '^ZC' 

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
关系型数据库 物联网 PostgreSQL
沉浸式学习PostgreSQL|PolarDB 11: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析
物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库. 监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库. 应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库. 以上数据具有时序特征, 对数据库的关键能力要求如下: 数据高速写入 高速按时间区间读取和分析, 目的是发现异常, 分析规律. 尽量节省存储空间
752 1
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
195 2
|
关系型数据库 定位技术 分布式数据库
沉浸式学习PostgreSQL|PolarDB 18: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
本文主要教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核.
1301 1
|
6月前
|
存储 关系型数据库 MySQL
TiDB与MySQL、PostgreSQL等数据库的比较分析
【2月更文挑战第25天】本文将对TiDB、MySQL和PostgreSQL等数据库进行详细的比较分析,探讨它们各自的优势和劣势。TiDB作为一款分布式关系型数据库,在扩展性、并发性能等方面表现突出;MySQL以其易用性和成熟性受到广泛应用;PostgreSQL则在数据完整性、扩展性等方面具有优势。通过对比这些数据库的特点和适用场景,帮助企业更好地选择适合自己业务需求的数据库系统。
1013 4
|
6月前
|
SQL 关系型数据库 MySQL
PostgreSQL【异常 01】java.io.IOException:Tried to send an out-of-range integer as a 2-byte value 分析+解决
PostgreSQL【异常 01】java.io.IOException:Tried to send an out-of-range integer as a 2-byte value 分析+解决
410 1
|
存储 SQL 关系型数据库
PostgreSQL技术大讲堂 - 第29讲:执行计划与成本估算
从零开始学PostgreSQL技术大讲堂 - 第29讲:执行计划与成本估算
187 1
|
SQL 关系型数据库 MySQL
《PostgreSQL与MySQL:详细对比与分析》
《PostgreSQL与MySQL:详细对比与分析》
533 0
|
存储 关系型数据库 PostgreSQL
Postgresql内核源码分析-heapam分析
Postgresql内核源码分析-heapam分析
176 1
|
SQL 存储 自然语言处理
玩转阿里云PostgreSQL,通过pg_jieba对豆瓣影评进行热评分析
在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。 本文通过针对kaggle数据集中的豆瓣影评的中文评论数据,通过阿里云的PostgreSQL中的pg_jieba插件进行分词(可自定义多个词典,并且切换自定义词典进行分词),基于分词的结果进行统计分析。
|
关系型数据库 分布式数据库 PolarDB
沉浸式学习PostgreSQL|PolarDB 15: 企业ERP软件、网站、分析型业务场景、营销场景人群圈选, 任意字段组合条件数据筛选
本篇文章目标学习如何快速在任意字段组合条件输入搜索到满足条件的数据.
604 0

热门文章

最新文章