首页 > 其他分享 >2个场景实例讲解GaussDB(DWS)基表统计信息估算不准的处理方案

2个场景实例讲解GaussDB(DWS)基表统计信息估算不准的处理方案

时间:2023-06-02 14:31:41浏览次数:40  
标签:DWS 基表 text GaussDB period mca rate numeric id

摘要:通过2个实例场景讲解GaussDB(DWS)运维解决方案。

本文分享自华为云社区《GaussDB(DWS)运维 -- 基表统计信息估算不准的常见场景及处理方案》,作者:譡里个檔。

场景1:基表过滤字段存在的隐式类型时,基表行数估算偏小

这种场景绝大部分场景DWS能够处理,但是如果隐式类型转后的结果与统计信息中的字段枚举值的表达式不一样,就会导致估算的严重偏差

原始SQL如下

SELECT * FROM dmgrpdi.dwl_inv_res_rpt_ci_grp_f WHERE period_id=202212 AND source_flag=1;

对应的执行计划

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  id |                            operation                             | E-rows | E-memory | E-width |  E-costs
 ----+------------------------------------------------------------------+--------+----------+---------+-----------
 1 | -> Row Adapter                                                  | 14160 | | 717 | 680025.43
 2 | ->  Vector Streaming (type: GATHER) | 14160 | | 717 | 680025.43
 3 | ->  Vector Partition Iterator                              | 14160 | 1MB      | 717 | 678241.33
 4 | ->  Partitioned CStore Scan on dwl_inv_res_rpt_ci_grp_f | 14160 | 1MB      | 717 | 678241.33
                  Predicate Information (identified by plan id)
 -------------------------------------------------------------------------------
 3 --Vector Partition Iterator
         Iterations: 1
 4 --Partitioned CStore Scan on dwl_inv_res_rpt_ci_grp_f
         Filter: ((period_id = 202212::numeric) AND ((source_flag)::bigint = 1))
         Pushdown Predicate Filter: (period_id = 202212::numeric)
         Partitions Selected by Static Prune: 36

发现source_flag字段上存在隐式类型转换,查询字段source_flag的统计信息

postgres=# SELECT most_common_vals,most_common_freqs, histogram_bounds  FROM pg_stats WHERE tablename = 'dwl_inv_res_rpt_ci_grp_f' AND attname = 'source_flag';
 most_common_vals | most_common_freqs | histogram_bounds
------------------+-----------------------------------+------------------
 {01,02,04,03}    | {.440034,.241349,.217413,.101089} | {05,06}
(1 row)

发现隐式类型转后的结果(1)与统计信息中的字段枚举值('01')的表达式不一样

处理方案:修改过滤条件,禁止类型转换,并且使用正确的常量值书写过滤条件

如上SQL语句中的source_flag=1修改为source_flag='01',修改后SQL语句如下

SELECT * FROM dmgrpdi.dwl_inv_res_rpt_ci_grp_f WHERE period_id=202212 AND source_flag='01';

查询新语句的执行计划

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
  id |                            operation                             |  E-rows | E-memory | E-width |  E-costs
 ----+------------------------------------------------------------------+-----------+----------+---------+-----------
 1 | -> Row Adapter                                                  | 108359075 | | 717 | 480542.98
 2 | ->  Vector Streaming (type: GATHER) | 108359075 | | 717 | 480542.98
 3 | ->  Vector Partition Iterator                              | 108359075 | 1MB      | 717 | 478758.88
 4 | ->  Partitioned CStore Scan on dwl_inv_res_rpt_ci_grp_f | 108359075 | 1MB      | 717 | 478758.88
                           Predicate Information (identified by plan id)
 -------------------------------------------------------------------------------------------------
 3 --Vector Partition Iterator
         Iterations: 1
 4 --Partitioned CStore Scan on dwl_inv_res_rpt_ci_grp_f
         Filter: ((period_id = 202212::numeric) AND (source_flag = '01'::text))
         Pushdown Predicate Filter: ((period_id = 202212::numeric) AND (source_flag = '01'::text))
         Partitions Selected by Static Prune: 36

场景2:基表在多列组合主键上过滤时,基表行数估算偏大

这种场景是因为DWS对基表上多个过滤条件之间采取弱相关性处理,当多个过滤条件是主键时,可能导致结果集估算偏大。

原始SQL如下

SELECT * FROM mca.mca_period_rate_t mca_rate2
WHERE period_number = '202208' AND from_currency_code = 'RMB' AND to_currency_code = 'USD'

执行信息如下

id |                      operation                       |       A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs  
----+------------------------------------------------------+--------------------+--------+--------+-------------+----------+---------+---------+----------
 1 | -> Row Adapter                                      | 444.735 | 1 | 2033 | 227KB       | | | 321 | 22601.41 
 2 | ->  Vector Streaming (type: GATHER) | 444.720 | 1 | 2033 | 873KB       | | | 321 | 22601.41 
 3 | -> CStore Scan on mca_period_rate_t mca_rate2 | [435.167, 435.167] | 1 | 2033 | [5MB, 5MB] | 1MB      | | 321 | 22427.41 
                                                              Predicate Information (identified by plan id) 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 3 --CStore Scan on mca_period_rate_t mca_rate2
        Filter: (((period_number)::text = '202208'::text) AND ((from_currency_code)::text = 'RMB'::text) AND ((to_currency_code)::text = 'USD'::text))
 Rows Removed by Filter: 425812
        Pushdown Predicate Filter: (((period_number)::text = '202208'::text) AND ((from_currency_code)::text = 'RMB'::text) AND ((to_currency_code)::text = 'USD'::text))

可以发现基表mca.mca_period_rate_t的行数估算严重偏大。

使用如下SQL语句查看表mca.mca_period_rate_t的定义

SELECT pg_get_tabledef('mca.mca_period_rate_t'::regclass);

查询表mca.mca_period_rate_t定义

SELECT pg_get_tabledef('mca.mca_period_rate_t');
SET search_path = mca;
CREATE TABLE mca_period_rate_t (
seq numeric NOT NULL,
period_number character varying(10) NOT NULL,
from_currency_code character varying(20) NOT NULL,
to_currency_code character varying(20) NOT NULL,
begin_rate numeric(35,18),
end_rate numeric(35,18),
avg_rate numeric(35,18),
creation_date timestamp(0) without time zone NOT NULL,
created_by numeric NOT NULL,
last_update_date timestamp(0) without time zone,
last_updated_by numeric,
rmb_begin_rate numeric(35,18),
usd_begin_rate numeric(35,18),
rmb_end_rate numeric(35,18),
usd_end_rate numeric(35,18),
rmb_avg_rate numeric(35,18),
usd_avg_rate numeric(35,18),
crt_cycle_id numeric,
crt_job_instance_id numeric,
last_upd_cycle_id numeric,
upd_job_instance_id numeric,
cdc_key_id character varying(128) DEFAULT sys_guid(),
end_rate2 numeric(35,18),
avg_rate2 numeric(35,18),
last_period_end_rate numeric(35,18)
)
WITH (orientation=column, compression=low, colversion=2.0, enable_delta=false)
DISTRIBUTE BY REPLICATION
TO GROUP group_version1;
CREATE UNIQUE INDEX mca_period_rate_u1 ON mca.mca_period_rate_t USING cbtree (period_number, from_currency_code, to_currency_code) TABLESPACE pg_default;

发现 (period_number, from_currency_code, to_currency_code) 为组合的唯一索引。

处理方案:对组合索引列收多列统计信息

注意此种方案只适用在基表比较小的情况下。因为多列统计信息需要使用百分比采样的方式计算统计信息,当表比较大时,统计信息计算耗时回很长。

针对如上查询语句执行如下语句收集(period_number, from_currency_code, to_currency_code) 多列统计信息

ANALYZE mca.mca_period_rate_t((period_number, from_currency_code, to_currency_code));

收集多列统计信息之后,基表的行数估算恢复正产

id |                                      operation                                      |       A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs 
----+-------------------------------------------------------------------------------------+--------------------+--------+--------+-------------+---------+---------+---------
 1 | -> Row Adapter                                                                     | 195.504 | 1 | 1 | 227KB       | | 321 | 675.14 
 2 | ->  Vector Streaming (type: GATHER) | 195.491 | 1 | 1 | 873KB       | | 321 | 675.14 
 3 | -> CStore Index Scan using mca_period_rate_u1 on mca_period_rate_t mca_rate2 | [164.344, 164.344] | 1 | 1 | [5MB, 5MB] | | 321 | 501.14 
                                                      Predicate Information (identified by plan id) 
----------------------------------------------------------------------------------------------------------------------------------------------------------
 3 --CStore Index Scan using mca_period_rate_u1 on mca_period_rate_t mca_rate2
 Index Cond: (((period_number)::text = '202208'::text) AND ((from_currency_code)::text = 'RMB'::text) AND ((to_currency_code)::text = 'USD'::text))


点击关注,第一时间了解华为云新鲜技术~

标签:DWS,基表,text,GaussDB,period,mca,rate,numeric,id
From: https://blog.51cto.com/u_15214399/6402378

相关文章

  • GaussDB(DWS)迁移实践丨row_number输出结果不一致
    摘要:迁移前后结果集row_number字段值前后不一致,前在DWS上运行不一致。本文分享自华为云社区《GaussDB(DWS)迁移-oracle兼容--row_number输出结果不一致》,作者:譡里个檔。【问题表现】迁移前后结果集row_number字段值前后不一致,前在DWS上运行不一致。【问题分析】这种问题......
  • GaussDB(DWS)条件表达式函数返回错误结果集排查
    摘要:条件表达式函数中出现结果集不一致问题,我们首先要考虑是否入参数据类型不一致导致出参不一致。本文分享自华为云社区《GaussDB(DWS)条件表达式函数返回错误结果集排查》,作者:yd_211369925。(一)案例背景客户使用greatest获取并返回参数列表中值最大的表达式的值,子查询中查询......
  • 详解GaussDB(DWS)用户监控原理及应用
    摘要:本文将聚焦于用户监控的原理及应用进行介绍。本文分享自华为云社区《GaussDB(DWS)监控工具指南(二)用户级监控》,作者:幕后小黑爪。前言资源监控是整个运维乃至整个产品生命周期重要的一环,事前及时语句发现故障,事后提供详实的数据用于追查定位问题。GaussDB(DWS)整个资源监控......
  • 看完这篇,DWS故障修复不再愁
    摘要:本文详细梳理分析了DWS服务面临软硬件故障场景和对应的修复原理,希望借此能够让你对DWS的集群故障修复有个全面深入的了解。本文分享自华为云社区《GaussDB(DWS)故障修复系统性介绍》,作者:闻鲜生。DWS是一个分布式架构的MPP集群,物理部署上涉及数百数千台主机和对应的磁盘,以及......
  • 一种DWS迁移Oracle的CONNECT BY语法的方案
    摘要:本文提供一种GaussDBDWS迁移CONNECTBY语法方案。本文分享自华为云社区《GaussDB(DWS)迁移-oracle兼容--CONNECTBY迁移》,作者:譡里个檔。CONNECTBYNOCYCLE1)预置对象定义DROPSCHEMAIFEXISTS"sdifin"CASCADE;CREATESCHEMA"sdifin";DROPTABLEIFEXIS......
  • 宝兰德应用服务器软件与华为云GaussDB完成兼容互认证
    摘要:北京宝兰德软件股份有限公司携手华为云完成宝兰德应用服务器软件9.5与GaussDB数据库兼容性测试,并获得华为云授予的技术认证书。本文分享自华为云社区《宝兰德应用服务器软件与华为云GaussDB完成兼容互认证》,作者:GaussDB数据库。近日,北京宝兰德软件股份有限公司(简称:宝兰德)携......
  • 共筑数字化未来,金山办公携手华为云完成文档中心和GaussDB适配
    摘要:金山办公携手华为云完成金山办公自主研发的“WPS文档中心系统”与华为云GaussDB相互兼容性测试认证,并获得华为云授予的《技术认证书》。本文分享自华为云社区《共筑数字化未来金山办公携手华为云完成文档中心和GaussDB适配》,作者:GaussDB数据库。近日,金山办公携手华为云完......
  • GaussDB(DWS)字符串处理函数返回错误结果集排查
    摘要:在使用字符串处理函数时,有时会出现非预期结果的场景。在排除使用问题后,应该从encoding和数据本身开始排查。本文分享自华为云社区《GaussDB(DWS)字符串处理函数返回错误结果集排查》,作者:-CHEN111-。在使用字符串处理函数时,有时会出现非预期结果的场景。在排除使用问题后,应......
  • GaussDB(DWS)网络流控与管控效果
    摘要:本文主要介绍GaussDB(DWS)网络流控能力,并对其管控效果进行验证。本文分享自华为云社区《GaussDB(DWS)网络流控与管控效果》,作者:门前一棵葡萄树。上一篇博文GaussDB(DWS)网络调度与隔离管控能力,我们详细介绍了GaussDB网络调度逻辑,并简单介绍了如何应用网络隔离管控能力。本篇......
  • 华为云GaussDB坚持技术引领,以数字化转型激活金融科技新动能
    摘要:“银行业数字化转型实践交流会”杭州站顺利收官。由华为与北京先进数通联合主办的“银行业数字化转型实践交流会”杭州站顺利收官,会议邀请了金融科技先锋企业、机构和多位资深专家,一起深入交流银行业数字化转型业务场景的探索和实践。其中,华为云数据库专家在现场分享了华为云Ga......