GaussDB(DWS)性能调优系列实战篇五：十八般武艺之路径干预

GaussDB DWS

华为云数仓GaussDB(DWS)论坛打卡活动进行中：免费学习大师课，更有华为手环、蓝牙音箱、华为云定制T-shirt等多重好礼等您来拿，戳此链接了解详情>>>【打卡有礼】免费学习大师课，打卡互动赢好礼~

摘要：

路径生成是表关联方式确定的主要阶段，本文介绍了几个影响路径生成的要素：cost_param, scan方式，join方式，stream方式，并从原理上分析如何干预路径的生成。从另外一个角度看，即路径生成，是从这些底层的选择开始，从行数的估算、到scan的选择、再到join方式以及stream的选择，构成一条简单路径，然后多条路径根据代价选择，再逐层关联更多的表，最终形成一个完整的执行路径。

cost模型选择

顾名思义，cost_param是控制cost相关的一个参数。在了解cost_param前，先回顾一下选择率的概念，GaussDB优化器中的选择率是指，当一个表有一个过滤或关联条件时，通过该条件能被选中的行数占总行数的比例，是介于0~1之间的一个实数。选择率在优化器中是一个重要的概念，主要应用于行数和distinct值的估算，行数和distinct值是计划生成中的基本要素。

首先，我们来看带有过滤条件的基表行数如何估算。如果一个表只有一个过滤条件，那么以选择率乘以表的行数，即可得到过滤完的行数；如果有多个过滤条件，那么就需要算出一个综合的选择率，如何计算？方式有二：一是通过多列统计信息直接计算，二是通过组合单列的选择率。那么组合的方式就由参数cost_param决定了，具体地：

取值	描述	适用场景
cost_param = 0	选择率按乘积方式组合	完全不相关场景
cost_param & 2 != 0	取最小的选择率作为综合选择率	完全相关场景

举一个例子，TPC-H 1x的part表，过滤条件是：p_brand = 'Brand#45' and p_container = 'WRAP CASE'，查看不同cost_param下的过滤后行数。

（1）cost_param=0

://bbs-img.huaweicloud.com/blogs/img/1606044267330020243.png

（2）cost_param=2

://bbs-img.huaweicloud.com/blogs/img/1606044372025088894.png

从估算出的行数（E-rows）和实际的行数（A-rows）对比可以看出，cost_param=0的不相关模型适合part表的p_brand和p_container列。

其次，Join的行数怎么估算的呢？原理跟过滤条件的行数估算是类似的，如果没有多列统计信息可以使用，则也需要单独计算每个条件的选择率，然后计算出综合选择率，得出行数。例如 TPC-H 1x lineitem和orders关联，关联条件是：l_orderkey = o_orderkey and o_custkey = l_suppkey，不同cost_param的执行情况如下：

（1）cost_param=0

://bbs-img.huaweicloud.com/blogs/img/1606046149725005535.png

（2）cost_param=2

://bbs-img.huaweicloud.com/blogs/img/1606046229595056211.png

此例中，Join的列之间也适合完全相关模型，这与l_orderkey和l_suppkey的分布是吻合的。

由于TPC-H的模型接近完全不相关模型，因此cost_param=0模型可以较好的描述场景，实际应用中，用户可以根据具体业务场景来调整模型，行数估算的准确性是计划生成的重要保证，在调优中检查估算的最直接的地方。GaussDB会在后续版本中新增更多模型供业务需求选择。

Scan方式的选择

GaussDB中扫描方式主要分顺序扫描和索引扫描，每种扫描方式都对应若干扫描算子，顺序扫描在行列存中对应的扫描算子分别是Seq Scan和CStore Scan算子（下面我们讨论中不加区分）。这些扫描算子大部分都可以通过开关来进行调控，例如Seq Scan，如果设置enable_seqscan=off，则表示不会优先选择Seq Scan，而不是一定不会选。扫描方式的选择，很大程度上决定了获取基表数据的路径。我们以如下的例子来说明：

select l_orderkey, o_custkey from lineitem, orders where l_orderkey = o_orderkey;

lineitem分布键是l_orderkey，并且在l_orderkey上有index，orders分布键是o_orderkey。默认情况下，Scan的方式如下：

://bbs-img.huaweicloud.com/blogs/img/1606047669288048658.png

两个表都是顺序扫描的路径，关联方式选择了Hash Join。如果把Seq Scan关掉（enable_seqscan=off）,计划如下：

://bbs-img.huaweicloud.com/blogs/img/1606047982482047772.png

lineitem的扫描变成了Index Only Scan（因为l_orderkey的类型是int），而在orders表上仍然选择Seq Scan（因为没有其他路径），同时关联方式也变为了Nest Loop，因为Hash Join需要全表扫描数据（lineitem的Seq Scan已经被关掉了）。优化器的选择方式我们从代价（E-costs）一栏中也可以看出。再把Index Only Scan关掉，看看计划如何变化：

://bbs-img.huaweicloud.com/blogs/img/1606053182138066589.png

扫描路径都变为了Seq Scan，而且Seq Scan的代价都很大。此时既然都走了Seq Scan，为什么不选Hash Join呢，把Nest Loop关掉，看看Hash Join计划的代价：

://bbs-img.huaweicloud.com/blogs/img/1606053371656091262.png

从代价上看出Hash Join的总代价比Nest Loop的小，但优化器没有选择Hash Join，这是因为优化器比较路径代价时，会比较Startup和Total代价，即启动代价和总代价，综合考虑，E-costs栏中显示的是总代价。把explain_perf_mode设置为normal，查看原Nest Loop的启动代价：

://bbs-img.huaweicloud.com/blogs/img/1606053681624040643.png

红框中的两个cost，分别是启动代价和总代价，在看Hash Join的cost，明显Hash Join的启动代价比Nest Loop的大很多（启动代价代表了输出第一条数据的代价），优化器在比较路径时，综合了这两个代价，最终推荐了Nest Loop的路径。

://bbs-img.huaweicloud.com/blogs/img/1606053918955085261.png

从上面的例子可以看出，扫描路径的调控，可以改变路径生成，合理的搭配是生成最优计划的前提，默认情况下，GaussDB优化器可以根据现有的路径选择（如上面的lineitem有两条扫描路径，orders只有一条扫描路径），最后确定出最优的一条。两条路径代价比较时，总代价不是唯一要素，但总代价越小，一般也会越容易被选中。

关联方式的选择

GaussDB优化器中表关联的主要方式有：Nest Loop，Hash Join和Merge Join，分别可以通过enable_nestloop、enable_hashjoin、enable_mergejoin进行控制，这种控制也不是绝对的，可以理解为是否优先选择。大部分场景下，三种路径的代价关系：Hash Join < Merge Join < Nest Loop。我们以一个简单的关联示例说明，store_returns和store_sales是TPC-DS 1x中两个表，SQL如下：

select count(*) from store_returns, store_sales where sr_customer_sk = ss_customer_sk;

默认情况下，优化器推荐Hash Join路径，计划如下：

://bbs-img.huaweicloud.com/blogs/img/1606056314621000615.png

如果把Hash Join关掉，则优化器选择了Merge Join路径：

://bbs-img.huaweicloud.com/blogs/img/1606056525514094012.png

如果再把Merge Join路径关掉，可能就会选择Nest Loop路径。关联方式的控制开关一般用于调优或规避问题，但具体是否能够起作用要看具体的语句，除了当前关联方式，还有没有其他方式。实际场景中，一个语句中关联的算子较多，一般很难用参数enable_hashjoin或enable_nestloop或enable_mergejoin来控制某两个表的Join方式，GaussDB中更细致的语句级别的调优手段是Plan Hint，感兴趣的可以参考产品手册。

Stream方式的选择

Stream算子是GaussDB分布式执行的关键算子之一，主要起到网络传输的作用，概要介绍可以参考：GaussDB(DWS)性能调优系列实战篇一：十八般武艺之总体调优策略。Stream算子由参数enable_stream_operator控制，如果关掉Stream算子，则可能导致生成不下推的计划，例如：

://bbs-img.huaweicloud.com/blogs/img/1606058184573055790.png

因为lineitem表关联的键l_partkey不是lineitem的分布键，需要添加Stream算子，但Stream功能被禁，于是只能生成不下推计划。

GaussDB计划中常见的主要Stream算子包括Redistribute、Broadcast和Gather。Gather一般是分布式计划中，CN用于收集DN的数据进行最后的处理，除非最后收集的行数非常多，这个算子涉及性能问题一般较少。Redistribute和Broadcast一是对“互补”的算子，前者用于重分布，后者用于广播，生成计划时，优化器会根据代价大小来选择。当Join Key没有包含表的分布键的时候，一般会添加Redistribute路径，能选择Redistribute路径理论上也可选择Broadcast路径，最终选择哪条路径要看优化器估算的代价是多少。这两个算子可以通过参数enable_redistribute和enable_broadcast进行控制。

在SMP开启的情况下，当并行度（dop）大于1时，一般还会有Local Redistribute、Split Redistribute、Local Broadcast和Split Broadcast；当倾斜优化开启时，还有PART REDISTRIBUTE PART ROUNDROBIN、PART_REDISTRIBUTE_PART_BROADCAST、PART_REDISTERIBUTE_PART_LOCAL等等，这些也是Stream算子，主要就是重分布、广播、RoundRobin的一些扩展形式，这里我们不一一介绍了，感兴趣的读者可以参考GaussDB DWS 产品手册。

我们考虑两个表的简单关联，store_sales和sr_tbl，它们的分布键分别是ss_item_sk 和sr_returned_date_sk，Join 条件是store_sales.ss_customer_sk =sr_tbl. sr_customer_sk，执行结果如下：

://bbs-img.huaweicloud.com/blogs/img/1606059079021002307.png

由于两个表的分布键都不是Join Key，因此走Hash Join路径的话需要有一个表做Broadcast或者两个表都做Redistribute，但是store_sales表比较大（E-rows显示28.7亿行），而sr_tbl表行数估算比较少（E-rows显示100行），优化器认为适合做Broadcast。于是最终选择了一边Broadcast的计划。

对于这个计划，由于sr_tbl表统计信息不准确（如果是中间结果集，则表示中间结果集估算不准），一种调优的方法是，将sr_tbl的表统计信息重新收集准确一些（如果sr_tbl是中间结果集，则无法收集），另一种方法是让sr_tbl走Redistribute路径，而后者我们又有两种方式来实现，一是用Plan Hint，即在生成计划时，告诉优化器走Redistribute路径，二是把Broadcast关掉。禁用Broadcast后，执行计划如下：

://bbs-img.huaweicloud.com/blogs/img/1606059201236091353.png

本列中，开启了SMP自适应，即优化器会根据系统资源和当前Active SQL数量来自行决定并行度（dop），如果Redistribute和Broadcast选择不当，则可能导致：

（1）Broadcast计划会出现下盘

（2）两个计划的并行度不一样，最终执行时间可能会差异比较大。

对于Stream方式的控制，一般的调优方式有Plan Hint、GUC参数、改善统计信息或估算信息。Plan Hint的详细介绍可以参考产品手册或者：GaussDB(DWS)性能调优系列实战篇六：十八般武艺Plan hint运用。

结束语

本文介绍的cost_param属于cost底层参数，建议对数据特征和使用场景比较熟悉的DBA慎重使用。Scan、Join、Stream调控的基本依据也是代价，代价一般体现在执行耗时上，调优时可从Performance中识别出性能的瓶颈点，分析选择的算子是否与代价匹配。另外，除了本文介绍的Session级别的控制参数外，还有基表、中间结果的行数，也可以通过Plan Hint进行语句级别的调控，感兴趣读者可关注我们，了解更多知识。

【推荐阅读】