摘要： 孙子兵法云：“谋定而后动，知止而有得”，做任何事肯定要进行筹划部署，做好筹备，这样能力利于这件事的胜利，切不可鲁莽而行。同样，GaussDB（DWS）执行查问语句也会依照预约的打算来执行，给定硬件环境的状况下，执行的快慢全凭打算的好坏，那么一条查问语句的打算是如何制订的呢，本文将为大家解读打算生成中行数估算和门路生成的神秘。

本文分享自华为云社区《GaussDB（DWS）打算生成原理揭秘（一）》，原文作者：Jugg 。

GaussDB（DWS）优化器的打算生成办法有两种，一是动静布局，二是遗传算法，前者是应用最多的办法，也是本系列文章重点介绍对象。一般来说，一条SQL语句经语法树（ParseTree）生成特定构造的查问树（QueryTree）后，从QueryTree开始，才进入打算生成的外围局部，其中有一些关键步骤：

  1. 设置初始并行度（Dop）
  2. 查问重写
  3. 估算基表行数
  4. 估算关联表（JoinRel）
  5. 门路生成，生成最优Path
  6. 由最优Path创立用于执行的Plan节点
  7. 调整最优并行度

本文次要关注3、4、5，这些步骤对一个打算生成影响比拟大，其中次要波及行数估算、门路抉择办法和代价估算（或称Cost估算），Cost估算是门路抉择的根据，每个算子对应一套模型，属于较为独立的局部，后续文章再解说。Plan Hint会在3、4、5等诸多步骤中交叉烦扰打算生成，其具体的介绍读者可参阅博文：GaussDB(DWS)性能调优系列实现篇六：十八般武艺Plan hint使用。

先看一个简略的查问语句：

select count(*) from t1 join t2 on t1.c2 = t2.c2 and t1.c1 > 100 and (t1.c3 is not null or t2.c3 is not null);

GaussDB（DWS）优化器给出的执行打算如下：

postgres=# explain verbose select count(*) from t1 join t2 on t1.c2 = t2.c2 and t1.c1 > 100 and (t1.c3 is not null or t2.c3 is not null);                                                  QUERY PLAN                                                 --------------------------------------------------------------------------------------------------------------  id |                    operation                     | E-rows | E-distinct | E-memory | E-width | E-costs ----+--------------------------------------------------+--------+------------+----------+---------+---------   1 | ->  Aggregate                                    |      1 |            |          |       8 | 111.23    2 |    ->  Streaming (type: GATHER)                  |      4 |            |          |       8 | 111.23    3 |       ->  Aggregate                              |      4 |            | 1MB      |       8 | 101.23    4 |          ->  Hash Join (5,7)                     |   3838 |            | 1MB      |       0 | 98.82     5 |             ->  Streaming(type: REDISTRIBUTE)    |   1799 | 112        | 2MB      |      10 | 46.38     6 |                ->  Seq Scan on test.t1           |   1799 |            | 1MB      |      10 | 9.25      7 |             ->  Hash                             |   1001 | 25         | 16MB     |       8 | 32.95     8 |                ->  Streaming(type: REDISTRIBUTE) |   1001 |            | 2MB      |       8 | 32.95     9 |                   ->  Seq Scan on test.t2        |   1001 |            | 1MB      |       8 | 4.50               Predicate Information (identified by plan id)          -----------------------------------------------------------------   4 --Hash Join (5,7)         Hash Cond: (t1.c2 = t2.c2)         Join Filter: ((t1.c3 IS NOT NULL) OR (t2.c3 IS NOT NULL))   6 --Seq Scan on test.t1         Filter: (t1.c1 > 100)

通常一条查问语句的Plan都是从基表开始，本例中基表t1有多个过滤条件，从打算上看，局部条件下推到基表上了，局部条件没有下推，那么它的行数如何估进去的呢？咱们首先从基表的行数估算开始。

一、基表行数估算

如果基表上没有过滤条件或者过滤条件无奈下推到基表上，那么基表的行数估算就是统计信息中显示的行数，不须要非凡解决。本节思考下推到基表上的过滤条件，分单列和多列两种状况。

1、单列过滤条件估算思维

基表行数估算目前次要依赖于统计信息，统计信息是先于打算生成由Analyze触发收集的对于表的样本数据的一些统计均匀信息，如t1表的局部统计信息如下：

postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct, avg_width, most_common_vals, most_common_freqs from pg_stats where tablename = 't1'; tablename | attname | null_frac | n_distinct | n_dndistinct | avg_width | most_common_vals | most_common_freqs-----------+---------+-----------+------------+--------------+-----------+------------------+------------------- t1        | c1      |         0 |        -.5 |          -.5 |         4 |                  | t1        | c2      |         0 |       -.25 |     -.431535 |         4 |                  | t1        | c3      |        .5 |          1 |            1 |         6 | {gauss}          | {.5} t1        | c4      |        .5 |          1 |            1 |         8 | {gaussdb}        | {.5}(4 rows)

各字段含意如下：

  • null_frac：空值比例
  • n_distinct：全局distinct值，取值规定：负数时代表distinct值，正数时其绝对值代表distinct值与行数的比
  • n_dndistinct：DN1上的distinct值，取值规定与n_distinct相似
  • avg_width：该字段的均匀宽度
  • most_common_vals：高频值列表
  • most_common_freqs：高频值的占比列表，与most_common_vals对应

从下面的统计信息可大抵判断出具体的数据分布，如t1.c1列，均匀宽度是4，每个数据的均匀反复度是2，且没有空值，也没有哪个值占比显著高于其余值，即most_common_vals（简称MCV）为空，这个也能够了解为数据根本散布平均，对于这些散布平均的数据，则调配一定量的桶，按等高形式划分了这些数据，并记录了每个桶的边界，俗称直方图（Histogram），即每个桶中有等量的数据。

有了这些根本信息后，基表的行数大抵就能够估算了。如t1表上的过滤条件"t1.c1>100"，联合t1.c1列的均匀分布个性和数据分布的具体情况：

postgres=# select histogram_bounds from pg_stats where tablename = 't1' and attname = 'c1';                                                                                                                                                                                              histogram_bounds                                                                                                                                                                                             ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {1,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550,560,570,580,590,600,610,620,630,640,650,660,670,680,690,700,710,720,730,740,750,760,770,780,790,800,810,820,830,840,850,860,870,880,890,900,910,920,930,940,950,960,970,980,990,1000}(1 row)

可知，t1.c1列的数据分布在1~1000之间，而每两个边界中含有的数据量是大致相同的（这里是依据样本统计的统计边界），先找到100在这个直方图中的大略地位，在这里它是某个桶的边界（有时在桶的外部），那么t1.c1>100的数据占比大概就是边界100之后的那些桶的数量的占比，这里的占比也称为选择率，即通过这个条件后，被选中的数据占比多少，因而由“t1.c >100“过滤之后的行数就能够估算进去了。

以上就是估算基表行数的根本思维。个别地，

有统计信息：

1.等值条件
1）比照MCV，如果满足过滤条件，则选择率（即most_common_freqs）累加；
2）对Histogram数据，按distinct值个数粗略估算选择率；
2.范畴条件
1）比照MCV数据，如果满足过滤条件，则选择率累加；
2）对Histogram数据，按边界地位估算选择率；
3.不等值条件：可转化为等值条件估算

无统计信息：

  1. 等值条件：比方过滤条件是：“substr(c3, 1, 5) = 'gauss'”，c3列有统计信息，但substr(c3, 1, 5)没有统计信息。那如何估算这个条件选择率呢？一个简略的思路是，如果substr(c3, 1, 5) 的distinct值已知的话，则可粗略假如每个distinct值的反复度统一，于是选择率也能够估算进去；在GaussDB（DWS）中，可通过设置cost_model_version=1开启表达式distinct值估算性能；
  2. 范畴条件：此时仅仅晓得substr(c3, 1, 5)的distinct值是无奈预估选择率的，对于无奈估算的表达式，可通过qual_num_distinct进行设置指定相应distinct值；
  3. 不等值条件：可转化为等值条件估算

2. 多列过滤条件估算思维

比方t1表有两个过滤条件：t1.c1 = 100 and t1.c3 = 'gauss'，那么如何估算该两列的综合选择率？在GaussDB（DWS）中，一般性办法有两个：

仅有单列统计信息

该状况下，首先按单列统计信息计算每个过滤条件的选择率，而后抉择一种形式来组合这些选择率，抉择的形式可通过设置cost_param来指定。为何须要抉择组合形式呢？因为理论模型中，列与列之间是有肯定相关性的，有的场景中相关性比拟强，有的场景则比拟弱，相关性的强弱决定了最初的行数。
该参数的意义和应用介绍可参考：GaussDB(DWS)性能调优系列实战篇五：十八般武艺之门路干涉。

有多列组合统计信息

如果过滤的组合列的组合统计信息曾经收集，则优化器会优先应用组合统计信息来估算行数，估算的根本思维与单列统一，行将多列组合模式上看成“单列”，而后再拿多列的统计信息来估算。

比方，多列统计信息有：((c1, c2, c4))，((c1, c2))，双括号示意一组多列统计信息：

  1. 若条件是：c1 = 7 and c2 = 3 and c4 = 5，则应用((c1, c2, c4))
  2. 若条件是：c1 = 7 and c2 = 3，则应用((c1, c2))
  3. 若条件是：c1 = 7 and c2 = 3 and c5 = 6，则应用((c1, c2))

多列条件匹配多列统计信息的总体准则是：

  1. 多列统计信息的列组合须要被过滤条件的列组合蕴含；
  2. 所有满足“条件1”的多列统计信息中，选取“与过滤条件的列组合的交加最大“的那个多列统计信息。

对于无奈匹配多列统计信息列的过滤条件，则应用单列统计信息进行估算。

3. 值得注意的中央

  • 目前应用多列统计信息时，不反对范畴类条件；如果有多组多列条件，则每组多列条件的选择率相乘作为整体的选择率。
  • 下面说的单列条件估算和多列条件估算，适用范围是每个过滤条件中仅有表的一列，如果一个过滤条件是多列的组合，比方 “t1.c1 < t1.c2”，那么一般而言单列统计信息是无奈估算的，因为单列统计信息是互相独立的，无奈确定两个独立的统计数据是否来自一行。目前多列统计信息机制也不反对基表上的过滤条件波及多列的场景。
  • 无奈下推到基表的过滤条件，则不纳入基表行数估算的思考领域，如上述：t1.c3 is not null or t2.c3 is not null，该条件个别称为JoinFilter，会在创立JoinRel时进行估算。
  • 如果没有统计信息可用，那就给默认选择率了。

二、JoinRel行数估算

基表行数估算完，就能够进入表关联阶段的解决了。那么要关联两个表，就须要一些信息，如基表行数、关联之后的行数、关联的形式抉择（也叫Path的抉择，请看下一节），而后在这些形式中抉择代价最小的，也称之为最佳门路。对于关联条件的估算，也有单个条件和多个条件之分，优化器须要算出所有Join条件和JoinFilter的综合选择率，而后给出估算行数，先看单个关联条件的选择率如何估算。

1. 一组Join条件估算思维

与基表过滤条件估算行数相似，也是利用统计信息来估算。比方上述SQL示例中的关联条件：t1.c2 = t2.c2，先看t1.c2的统计信息：

postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct, avg_width, most_common_vals, most_common_freqsfrom pg_stats where tablename = 't1' and attname = 'c2'; tablename | attname | null_frac | n_distinct | n_dndistinct | avg_width | most_common_vals | most_common_freqs-----------+---------+-----------+------------+--------------+-----------+------------------+------------------- t1        | c2      |         0 |       -.25 |     -.431535 |         4 |                  |(1 row)

t1.c2列没有MCV值，均匀每个distinct值大概反复4次且是均匀分布，因为Histogram中保留的数据只是桶的边界，并不是理论有哪些数据（反复收集统计信息，这些边界可能会有变动），那么理论拿边界值来与t2.c2进行比拟不太理论，可能会产生比拟大的误差。此时咱们深信一点：“能关联的列与列是有雷同含意的，且数据是尽可能有重叠的”，也就是说，如果t1.c2列有500个distinct值，t2.c2列有100个distinct值，那么这100个与500个会重叠100个，即distinct值小的会全副在distinct值大的那个表中呈现。尽管这样的假如有些刻薄，但很多时候与理论状况是较吻合的。回到本例，依据统计信息，n_distinct显示负值代表占比，而t1表的估算行数是2000：

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

于是，t1.c2的distinct是0.25 * 2000 = 500，相似地，依据统计信息，t2.c2的distinct是100：

postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct from pg_stats where tablename = 't2' and attname = 'c2'; tablename | attname | null_frac | n_distinct | n_dndistinct-----------+---------+-----------+------------+-------------- t2        | c2      |         0 |        100 |      -.39834(1 row)

那么，t1.c2的distinct值是否能够间接用500呢？答案是不能。因为基表t1上还有个过滤条件"t1.c1 > 100"，以后关联是产生在基表过滤条件之后的，估算的distinct应该是过滤条件之后的distinct有多少，不应是原始表上有多少。那么此时能够采纳各种假如模型来进行估算，比方几个简略模型：Poisson模型（假如t1.c1与t1.c2相关性很弱）或齐全相干模型（假如t1.c1与t1.c2齐全相干），不同模型失去的值会有差别，在本例中，"t1.c1 > 100"的选择率是 8.995000e-01，则用不同模型失去的distinct值会有差别，如下：

  1. Poisson模型（相关性弱模型）：500 (1.0 - exp(-2000 8.995000e-01 / 500)) = 486
  2. 齐全相干模型：500 * 8.995000e-01 = 450
  3. 齐全不相干模型：500 * (1.0 - pow(1.0 - 8.995000e-01, 2000 / 500)) = 499.9，该模型可由概率办法失去，感兴趣读者可自行尝试推导
  4. 理论过滤后的distinct：500，即c2与c1列是不相干的
postgres=# select count(distinct c2) from t1 where c1 > 100; count-------   500(1 row)

估算过滤后t1.c2的distinct值，那么"t1.c2 = t2.c2"的选择率就能够估算进去了： 1 / distinct。

以上是任一表没有MCV的状况，如果t1.c2和t2.c2都有MCV，那么就先比拟它们的MCV，因为MCV中的值都是有明确占比的，间接累计匹配后果即可，而后再对Histogram中的值进行匹配。

2. 多组Join条件估算思维

表关联含有多个Join条件时，与基表过滤条件估算相似，也有两种思路，优先尝试多列统计信息进行选择率估算。当无奈应用多列统计信息时，则应用单列统计信息依照上述办法别离计算出每个Join条件的选择率。那么组合选择率的形式也由参数cost_param管制，具体参考GaussDB(DWS)性能调优系列实战篇五：十八般武艺之门路干涉。

另外，以下是非凡状况的选择率估算形式：

  • 如果Join列是表达式，没有统计信息的话，则优化器会尝试估算出distinct值，而后按没有MCV的形式来进行估算；
  • Left Join/Right Join需非凡思考以下一边补空另一边全输入的特点，以上模型进行适当的批改即可；
  • 如果关联条件是范畴类的比拟，比方"t1.c2 < t2.c2"，则目前给默认选择率：1 / 3；

3. JoinFilter的估算思维

两表关联时，如果基表上有一些无奈下推的过滤条件，则个别会变成JoinFilter，即这些条件是在Join过程中进行过滤的，因而JoinFilter会影响到JoinRel的行数，但不会影响基表扫描上来的行数。严格来说，如果把JoinRel看成一个两头表的话，那么这些JoinFilter是这个两头表的过滤条件，但JoinRel还没有产生，也没有行数和统计信息，因而无奈精确估算。然而一种简略近似的办法是，依然利用基表，粗略估算出这个JoinFilter的选择率，而后放到JoinRel最终行数估算中去。

三、门路生成

有了后面两节的行数估算的铺垫，就能够进入门路生成的流程了。何为门路生成？已知表关联的形式有多种（比方 NestLoop、HashJoin）、且GaussDB（DWS）的表是分布式的存储在集群中，那么两个表的关联形式可能就有多种了，而咱们的指标就是，从这些给定的基表登程，按要求通过一些操作（过滤条件、关联形式和条件、汇集等等），互相组合，层层递进，最初失去咱们想要的后果。这就好比从基表登程，寻求一条最佳门路，使得咱们能最快失去后果，这就是咱们的目标。本节咱们介绍Join Path和Aggregate Path的生成。

1. Join Path的生成

GaussDB（DWS）优化器抉择的基本思路是动静布局，顾名思义，从某个开始状态，通过求解中间状态最优解，逐渐往前演进，最初失去全局的最优打算。那么在动静布局中，总有一个变量，驱动着过程演进。在这里，这个变量就是表的个数。本节，咱们以如下SQL为例进行解说：

select count(*) from t1, t2 where t1.c2 = t2.c2 and t1.c1 < 800 and exists (select c1 from t3 where t1.c1 = t3.c2 and t3.c1 > 100);

该SQL语句中，有三个基表t1, t2, t3，三个表的散布键都是c1列，共有两个关联条件：

  1. t1.c2 = t2.c2， t1与t2关联
  2. t1.c1 = t3.c2， t1与t3关联

为了配合剖析，咱们联合日志来帮忙大家了解，设置如下参数，而后在执行语句：

set logging_module='on(opt_join)';set log_min_messages=debug2;

第一步，如何获取t1和t2的数据

首先，如何获取t1和t2的数据，比方 Seq Scan、Index Scan等，因为本例中，咱们没有创立Index，那抉择只有Seq Scan了。日志片段显示：

咱们先记住这三组Path名称：path_list，cheapest_startup_path，cheapest_total_path，前面两个就对应了动静布局的部分最优解，在这里是一组汇合，统称为最优门路，也是下一步的搜寻空间。path_list外面寄存了以后Rel汇合上的有价值的一组候选Path（被剪枝调的Path不会放在这里），cheapest_startup_path代表path_list中启动代价最小的那个Path，cheapest_total_path代表path_list里一组总代价最小的Path（这里用一组次要是可能存在多个维度别离对应的最优Path）。t2表和t3表相似，最优门路都是一条Seq Scan。有了所有基表的Scan最优门路，上面就能够抉择关联门路了。

第二步，求解(t1, t2)关联的最优门路

t1和t2两个表的散布键都是c1列，但Join列都是c2列，那么实践上的门路就有：（放在左边示意作为内表）

  1. Broadcast(t1) join t2
  2. t1 join Broadcast(t2)
  3. Broadcast(t2) join t1
  4. t2 join Broadcast(t1)
  5. Redistribute(t1) join Redistribute(t2)
  6. Redistribute(t2) join Redistribute(t1)

而后每一种门路又能够搭配不同的Join办法（NestLoop、HashJoin、MergeJoin），总计18种关联门路，优化器须要在这些门路中抉择最优门路，筛选的根据就是门路的代价（Cost）。优化器会给每个算子赋予代价，比方 Seq Scan，Redistribute，HashJoin都有代价，代价与数据规模、数据特色、系统资源等等都有关系，对于代价如何估算，后续文章再剖析，本节只关注由这些代价怎么选门路。因为代价与执行工夫成正比，优化器的指标是抉择代价最小的打算，因而门路抉择也是一样。门路代价的比拟思路大抵是这样，对于产生的一个新Path，一一比拟该新Path与path_list中的path，若total_cost很相近，则比拟startup cost，如果也差不多，则保留该Path到path_list中去；如果新门路的total_cost比拟大，然而startup_cost小很多，则保留该Path，此处略去具体的比拟过程，间接给出Path的比拟后果：

由此看出，总代价最小的门路是两边做重散布、t1作为内表的门路。

第三步，求解(t1, t3)关联的最优门路

t1和t3表的关联条件是：t1.c1 = t3.c2，因为t1的Join列是散布键c1列，于是t1表上不须要加Redistribute；因为t1和t3的Join形式是Semi Join，表面不能Broadcast，否者可能会产生反复后果；另外还有一类Unique Path抉择（即t3表去重），那么可用的候选门路大抵如下：

  1. t1 semi join Redistribute(t3)
  2. Redistribute(t3) right semi join t1
  3. t1 join Unique(Redistribute(t3))
  4. Unique(Redistribute(t3)) join t1

因为只有一边须要重散布且能够进行重散布，则不选Broadcast，因为雷同数据量时Broadcast的代价个别要高于重散布，提前剪枝掉。再把Join办法思考进去，于是优化器给出了最终抉择：

此时的最优打算是抉择了内表Unique Path的门路，即t3表先去重，而后在走Inner Join过程。

第四步，求解(t1，t2，t3)关联的最优门路

有了后面两步的铺垫，三个表关联的思路是相似的，模式上是分解成两个表先关联，然候在与第三个表关联，实际操作上是间接取出所有两表关联的JoinRel，而后一一减少另一个表，尝试关联，抉择的形式如下：

  • JoinRel(t1, t2) join t3：
    (t1, t2)->(cheapest_startup_path + cheapest_total_path) join t3->(cheapest_startup_path + cheapest_total_path)
  • JoinRel(t1, t3) join t2：
    (t1, t3)->(cheapest_startup_path + cheapest_total_path) join t2->(cheapest_startup_path + cheapest_total_path)
  • JoinRel(t2, t3) join t1：因为没有(t2, t3)关联，所以此种状况不存在

每取一对内表面的Path进行Join时，也会判断是否须要重散布、是否能够去重，抉择关联形式，比方JoinRel(t1, t2) join t3时，也会尝试对t3表进行去重的Path，因为这个Join实质依然是Semi Join。下图是抉择过程中产生的局部有价值的候选门路（篇幅所限，只截取了一部分）：

优化器在这些门路中，选出了如下的最优门路：

比照理论的执行打算，二者是一样的（比照第4层HashJoin的“E-costs“是一样的）：

从这个过程能够大抵感触到path_list有可能会产生一些收缩，如果path_list中门路太多了，则可能会导致cheapest_total_path有多个，那么下一级的搜寻空间也就会变的很大，最终会导致打算生成的耗时减少。对于Join Path的生成，作以下几点阐明：

  1. Join门路的抉择时，会分两个阶段计算代价，initial和final代价，initial代价疾速估算了建hash表、计算hash值以及下盘的代价，当initial代价曾经比path_list中某个path大时，就提前剪枝掉该门路；
  2. cheapest_total_path有多个起因：次要是思考到多个维度下，代价很相近的门路都有可能是下一层动静布局的最佳抉择，只留一个可能得不到整体最优打算；
  3. cheapest_startup_path记录了启动代价最小的一个，这也是预留了另一个维度，当查问语句须要的后果很少时，有一个启动代价很小的Path，但总代价可能比拟大，这个Path有可能会成为首选；
  4. 因为剪枝的起因，有些状况下，可能会提前剪枝掉某个Path，或者这个Path没有被选为cheapest_total_path或cheapest_startup_path，而这个Path是实践上最优打算的一部分，这样会导致最终的打算不是最优的，这种场景个别概率不大，如果遇到这种状况，可尝试应用Plan Hint进行调优；
  5. 门路生成与集群规模大小、系统资源、统计信息、Cost估算都有严密关系，如集群DN数影响着重散布的歪斜性和单DN的数据量，零碎内存影响下盘代价，统计信息是行数和distinct值估算的第一手数据，而Cost估算模型在整个打算生成中，是抉择和淘汰的关键因素，每个JoinRel的行数估算不准，都有可能影响着最终打算。因而，雷同的SQL语句，在不同集群或者同样的集群不同统计信息，打算都有可能不一样，如果门路产生一些变动可通过剖析Performance信息和日志来定位问题，Performance详解能够参考博文：GaussDB(DWS)的explain performance详解。
  6. 如果设置了Random Plan模式，则动静布局的每一层cheapest_startup_path和cheapest_total_path都是从path_list中随机选取的，这样保障随机性。

2. Aggregate Path的生成

一般而言，Aggregate Path生成是在表关联的Path生成之后，且有三个次要步骤（Unique Path的Aggregate在Join Path生成的时候就曾经实现了，但也会有这三个步骤）：先估算出汇集后果的行数，而后抉择Path的形式，最初创立出最优Aggregate Path。前者依赖于统计信息和Cost估算模型，后者取决于前者的估算后果、集群规模和系统资源。Aggregate行数估算次要依据汇集列的distinct值来组合，咱们重点关注Aggregate行数估算和最优Aggregate Path抉择。

2.1 Aggregate行数估算

以如下SQL为例进行阐明：

select t1.c2, t2.c2, count(*) cnt from t1, t2 where t1.c2 = t2.c2 and t1.c1 < 500 group by t1.c2, t2.c2;

该语句先是两表关联，基表上有过滤条件，而后求取两列的GROUP BY后果。这里的汇集列有两个，t1.c2和t2.c2，在看一下零碎表中给出的原始信息：

postgres=# select tablename, attname, null_frac, n_distinct, n_dndistinct from pg_stats where (tablename = 't1' or tablename = 't2') and attname = 'c2'; tablename | attname | null_frac | n_distinct | n_dndistinct-----------+---------+-----------+------------+-------------- t1        | c2      |         0 |       -.25 |     -.431535 t2        | c2      |         0 |        100 |      -.39834(2 rows)

统计信息显示t1.c2和t2.c2的原始distinct值别离是-0.25和100，-0.25转换为绝对值就是0.25 * 2000 = 500，那它们的组合distinct是不是至多应该是500呢？答案不是。因为Aggregate对JoinRel(t1, t2)的后果进行汇集，而零碎表中统计信息是原始信息（没有任何过滤）。这时须要把Join条件和过滤条件都思考进去，如何思考呢？首先看过滤条件 “t1.c1<500“可能会过滤掉一部分t1.c2，那么就会有个选择率（此时咱们称之为FilterRatio），而后Join条件"t1.c2 = t2.c2"也会有一个选择率（此时咱们称之为JoinRatio），这两个Ratio都是介于[0, 1]之间的一个数，于是估算t1.c2的distinct时这两个Ratio影响都要思考。如果不同列之间抉择Poisson模型，雷同列之间用齐全相干模型，则t1.c2的distinct大概是这样：

distinct(t1.c2) = Poisson(d0, ratio1, nRows) * ratio2

其中d0示意基表中原始distinct，ratio1代表应用Poisson模型的Ratio，ratio2代表应用齐全相干模型的Ratio，nRows是基表行数。如果须要定位剖析问题，这些Ratio能够从日志中查阅，如下设置后在运行SQL语句：

set logging_module='on(opt_card)';set log_min_messages=debug3;

本例中，咱们从日志中能够看到t1表上的两个Ratio：

在看t2.c2，这一列原始distinct是100，而从下面日志中能够看出t2表的数据全匹配上了（没有Ratio），那么Join完t2.c2的distinct也是100。此时不能间接组合t1.c2和t2.c2，因为"t1.c2 = t2.c2“暗含了这两个列的值是一样的，那就是说它们等价，于是只需思考Min(distinct(t1.c2), distinct(t2.c2))即可，下图是Performance给出的理论和估算行数：

postgres=# explain performance select t1.c2, t2.c2, count(*) cnt from t1, t2 where t1.c2 = t2.c2 and t1.c1 < 500 group by t1.c2, t2.c2;                                                                            QUERY PLAN                                                                           ------------------------------------------------------------------------------------------------------------------------------------------------------------------  id |                   operation                   |      A-time      | A-rows | E-rows | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs ----+-----------------------------------------------+------------------+--------+--------+------------+----------------+----------+---------+---------+---------   1 | ->  Streaming (type: GATHER)                  | 48.500           |     99 |    100 |            | 80KB           |          |         |      16 | 89.29     2 |    ->  HashAggregate                          | [38.286, 40.353] |     99 |    100 |            | [28KB, 31KB]   | 16MB     | [24,24] |      16 | 79.29     3 |       ->  Hash Join (4,6)                     | [37.793, 39.920] |   1980 |   2132 |            | [6KB, 6KB]     | 1MB      |         |       8 | 75.04     4 |          ->  Streaming(type: REDISTRIBUTE)    | [0.247, 0.549]   |   1001 |   1001 | 25         | [53KB, 53KB]   | 2MB      |         |       4 | 32.95     5 |             ->  Seq Scan on test.t2           | [0.157, 0.293]   |   1001 |   1001 |            | [12KB, 12KB]   | 1MB      |         |       4 | 4.50      6 |          ->  Hash                             | [36.764, 38.997] |    998 |   1000 | 62         | [291KB, 291KB] | 16MB     | [20,20] |       4 | 29.88     7 |             ->  Streaming(type: REDISTRIBUTE) | [36.220, 38.431] |    998 |    999 |            | [53KB, 61KB]   | 2MB      |         |       4 | 29.88     8 |                ->  Seq Scan on test.t1        | [0.413, 0.433]   |    998 |    999 |            | [14KB, 14KB]   | 1MB      |         |       4 | 9.25

2.2 Aggregrate Path生成

有了汇集行数，则能够依据资源状况，灵便抉择汇集形式。Aggregate形式次要有以下三种：

  1. Aggregate + Gather (+ Aggregate)
  2. Redistribute + Aggregate (+Gather)
  3. Aggregate + Redistribute + Aggregate (+Gather)

括号中的示意可能没有这一步，视具体情况而定。这些汇集形式能够了解成，两表关联时选两边Redistribute还是选一边Broadcast。优化器拿到汇集的最终行数后，会尝试每种汇集形式，并计算相应的代价，抉择最优的形式，最终生成门路。这里有两层Aggregate时，最初一层就是最终汇集行数，而第一层汇集行数是依据Poisson模型推算的。Aggregate形式抉择默认由优化器依据代价抉择，用户也能够通过参数best_agg_plan指定。三类汇集形式大抵适用范围如下：

  • 第一种，间接汇集后行数不太大，个别是DN汇集，CN收集，有时CN需进行二次汇集
  • 第二种，须要重散布且间接汇集后行数未显著缩小
  • 第三种，须要重散布且间接汇集后行数缩小显著，重散布之后，行数又能够缩小，个别是DN汇集、重散布、再汇集，俗称双层Aggregate

四、结束语

本文着眼于打算生成的外围步骤，从行数估算、到Join Path的生成、再到Aggregate Path的生成，介绍了其中最简略过程的基本原理。而理论的解决办法远远比形容的要简单，须要思考的状况很多，比方多组选择率如何组合最优、散布键怎么选、呈现歪斜如何解决、内存用多少等等。衡量整个打算生成过程，有时也不得不有所舍，这样能力有所得，而有时打算的一点劣势也能够疏忽或者通过其余能力补救上来，比方SMP开启后，并行的成果会淡化一些打算上的缺点。总而言之，打算生成是一项简单而粗疏的工作，生成全局最优打算须要继续的发现问题和优化，后续博文咱们将持续探讨打算生成的机密。

点击关注，第一工夫理解华为云陈腐技术~