如何把一个报表从2分钟优化到1秒?---GaussDB数据库

报表优化系列文章----如何把一个报表从2分钟优化到1秒?
优化需求：
报表名：外部数据查询报表\EDM004-外部数据查询量业务类型_部门编号
需求说明：原报表SQL运行时长 2分钟，感觉慢，让DBA看看是否有优化的空间，给优化一下。
优化人： 4K/ weicat: wx120890945
优化日期：2022-03-03
数据库类型：GaussDB

原SQL执行耗时：120s~ 127s (2分钟) 原SQL执行耗时：1~2 s
相关表：
edm_it.edm_suc_info_wwn 数据量：17+亿
gds_d_it.d_org_io_stat_atho_crnt_wwn 数据量：3400+ 查询特征：典型的大表驱动小表关联查询，查询时间跨度长，统计类（count）查询。
查询时间跨度：1个月 示例：20220101~20220131
查询相关数据量：1.2亿

###### 原报表SQL：
SELECT t1.name
     ,t1.ywlx
     ,t1.itemname
     ,t1.department
     ,t1.org_name
     ,t1.newdep
     ,CASE
        WHEN newdep = '41991S,41990T' THEN
         '总行部门XX管理部、总行部门XX管理部'
        ELSE
         t3.atho_name
      END
     ,t1.cnt 计数
 FROM 
 (SELECT NAME
             ,ywlx
             ,itemname
             ,department
             ,org_name
             ,CASE
                WHEN department = '419907' AND ywlx = '1049' THEN
                 '41900S'
                WHEN department = '419907' AND ywlx = '1065' THEN
                 '41991E'
                WHEN department = '419908' THEN
                 '41903R'
                WHEN department = '419999' AND ywlx = '1009' THEN
                 '41903R'
                WHEN department = '419999' AND ywlx = '104204' THEN
                 '41991N'
                WHEN department = '419999' AND ywlx = '1065' THEN
                 '41991N'
                WHEN department = '41900O' THEN
                 '4199Y3'
                WHEN department = '41902F' THEN
                 '4199Y6'
                WHEN department = '41902R' THEN
                 '41991T'
                WHEN department = '41960A' THEN
                 '4199Z5'
                WHEN department = '41981A' AND ywlx = '1088' THEN
                 '4199Z5'
                WHEN department = '41990B' THEN
                 '4199Y5'
                WHEN department = '41990C' AND
                     ywlx IN ('1013', '1009', '1004', '1026', '1003') THEN
                 '41903R'
                WHEN department = '41990C' AND ywlx IN ('1065', '1059') THEN
                 '41991N'
                WHEN department = '41990N' AND
                     ywlx IN ('1023', '1032', '1036', '1045') THEN
                 '41903R'
                WHEN department = '41990N' AND
                     ywlx IN ('1020', '1052', '1066') THEN
                 '41981A'
                WHEN department = '41990N' AND ywlx = '104204' THEN
                 '41991N'
                WHEN department = '41990N' AND ywlx = '1008' THEN
                 '41991S'
                WHEN department = '41990N' AND ywlx = '1042' THEN
                 '41991S,41990T'
                WHEN department = '41990N' AND ywlx = '1086' THEN
                 '41991T'
                WHEN department = '41990N' AND
                     ywlx IN ('1024', '1038', '1024024', '1024110') THEN
                 '4199V5'
                WHEN department = '41990N' AND
                     ywlx IN
                     ('1004', '1027', '1081', '100401', '104201', '104203') THEN
                 '4199X4'
                WHEN department = '41990N' AND
                     ywlx IN
                     ('1006', '1019', '1029', '1030', '1033', '1034', '1048') THEN
                 '4199Y3'
                WHEN department = '41990N' AND ywlx = '1067' THEN
                 '4199Z5'
                WHEN department = '41990P' THEN
                 '4199Y1'
                WHEN department = '41990U' AND ywlx = '1023' THEN
                 '41903R'
                WHEN department = '41990U' AND ywlx = '1004' THEN
                 '4199X4'
                WHEN department = '41990U' AND ywlx = '1080' THEN
                 '4199Z1'
                WHEN department = '41990V' THEN
                 '4199Z1'
                WHEN department = '41991E' AND ywlx = '1013' THEN
                 '41903R'
                WHEN department = '41991N' AND ywlx = '1046' THEN
                 '41900S'
                WHEN department = '41991P' AND ywlx = '1063' THEN
                 '4199Z8'
                WHEN department = '41991S' AND ywlx = '1085' THEN
                 '41903R'
                WHEN department = '41994Z' THEN
                 '41903R'
                WHEN department = '41996A' THEN
                 '41991S'
                WHEN department = '41998C' THEN
                 '4199Y1'
                WHEN department = '41999A' AND ywlx = '1013' THEN
                 '41903R'
                WHEN department = '41999A' AND ywlx = '1041' THEN
                 '4199Y8'
                ELSE
                 department
              END AS newdep
             ,COUNT(1) AS cnt
         FROM edm_it.edm_suc_info_dtl a
        WHERE inputtime >='20220101' and  inputtime <='20220131'
        GROUP BY NAME, ywlx, department, itemname, org_name) t1
left join gds_d_it.d_org_io_stat_atho_crnt t3 
   ON t1.newdep = t3.atho_num
;

###### 优化后报表SQL：
select
a.name
,a.ywlx
,a.itemname
,a.department
,a.org_name
,CASE
        WHEN (CASE
                WHEN department = '419907' AND ywlx = '1049' THEN
                 '41900S'
                WHEN department = '419907' AND ywlx = '1065' THEN
                 '41991E'
                WHEN department = '419908' THEN
                 '41903R'
                WHEN department = '419999' AND ywlx = '1009' THEN
                 '41903R'
                WHEN department = '419999' AND ywlx = '104204' THEN
                 '41991N'
                WHEN department = '419999' AND ywlx = '1065' THEN
                 '41991N'
                WHEN department = '41900O' THEN
                 '4199Y3'
                WHEN department = '41902F' THEN
                 '4199Y6'
                WHEN department = '41902R' THEN
                 '41991T'
                WHEN department = '41960A' THEN
                 '4199Z5'
                WHEN department = '41981A' AND ywlx = '1088' THEN
                 '4199Z5'
                WHEN department = '41990B' THEN
                 '4199Y5'
                WHEN department = '41990C' AND
                     ywlx IN ('1013', '1009', '1004', '1026', '1003') THEN
                 '41903R'
                WHEN department = '41990C' AND ywlx IN ('1065', '1059') THEN
                 '41991N'
                WHEN department = '41990N' AND
                     ywlx IN ('1023', '1032', '1036', '1045') THEN
                 '41903R'
                WHEN department = '41990N' AND
                     ywlx IN ('1020', '1052', '1066') THEN
                 '41981A'
                WHEN department = '41990N' AND ywlx = '104204' THEN
                 '41991N'
                WHEN department = '41990N' AND ywlx = '1008' THEN
                 '41991S'
                WHEN department = '41990N' AND ywlx = '1042' THEN
                 '41991S,41990T'
                WHEN department = '41990N' AND ywlx = '1086' THEN
                 '41991T'
                WHEN department = '41990N' AND
                     ywlx IN ('1024', '1038', '1024024', '1024110') THEN
                 '4199V5'
                WHEN department = '41990N' AND
                     ywlx IN
                     ('1004', '1027', '1081', '100401', '104201', '104203') THEN
                 '4199X4'
                WHEN department = '41990N' AND
                     ywlx IN
                     ('1006', '1019', '1029', '1030', '1033', '1034', '1048') THEN
                 '4199Y3'
                WHEN department = '41990N' AND ywlx = '1067' THEN
                 '4199Z5'
                WHEN department = '41990P' THEN
                 '4199Y1'
                WHEN department = '41990U' AND ywlx = '1023' THEN
                 '41903R'
                WHEN department = '41990U' AND ywlx = '1004' THEN
                 '4199X4'
                WHEN department = '41990U' AND ywlx = '1080' THEN
                 '4199Z1'
                WHEN department = '41990V' THEN
                 '4199Z1'
                WHEN department = '41991E' AND ywlx = '1013' THEN
                 '41903R'
                WHEN department = '41991N' AND ywlx = '1046' THEN
                 '41900S'
                WHEN department = '41991P' AND ywlx = '1063' THEN
                 '4199Z8'
                WHEN department = '41991S' AND ywlx = '1085' THEN
                 '41903R'
                WHEN department = '41994Z' THEN
                 '41903R'
                WHEN department = '41996A' THEN
                 '41991S'
                WHEN department = '41998C' THEN
                 '4199Y1'
                WHEN department = '41999A' AND ywlx = '1013' THEN
                 '41903R'
                WHEN department = '41999A' AND ywlx = '1041' THEN
                 '4199Y8'
                ELSE
                 department
              END  )  = '41991S,41990T' THEN
         '总行部门授信管理部、总行部门信贷管理部'
        ELSE
         b.atho_name
      END	as  new_department_name
    ,count(1) as cnt
from edm_it.edm_suc_info_wwn a
left join gds_d_it.d_org_io_stat_atho_crnt b
on a.department = b.atho_num
where a.inputtime >='20220101'
     and  a.inputtime <='20220131'
GROUP BY a.name, a.ywlx, a.department, a.itemname, a.org_name,b.atho_name
;

下面是详细的优化思路及优化步骤：

一、相关表分析

本次查询只涉及2张表：
edm_it.edm_suc_info_wwn 数据量：17+亿
gds_d_it.d_org_io_stat_atho_crnt 数据量：3400+

从数据量上看，主表数据量大，并且计数统计，耗时是正常的。
我们再看表结构：
主表：edm_it.edm_suc_info_dtl 列式存储，分布键为 日期字段，并且该表是每天增量，数据量增加范围在。按日期分布有存在数据分布不均匀的情况，并且该表为1.7亿的大表，并未分区。关联表：gds_d_it.d_org_io_stat_atho_crnt 数据量：3400+ 该表为机构维表，只有3400的数据量，所以根据GaussDB的建表建议，我们建成复制表，避免后期数据的广播和重分布。
表整改：1. 分布键调整为 序列号+日期 目的是让数据均匀离散存储。
2. 按月建立分区。 可以按日期高效filter。
3. 建复制表。gds_d_it.d_org_io_stat_atho_crnt建复制表。

调整完毕后。再进行原SQL测试。这时执行时间已经能下降到： Total runtime: 20878.693 ms 即20s 左右。

执行计划：

     ====== Query Summary =====                            
---------------------------------------------------------------------------------


 Datanode executor start time [dn_6093_6094, dn_6021_6022]: [7.609 ms,71.862 ms]
 Datanode executor end time [dn_6105_6106, dn_6091_6092]: [1.524 ms,5.115 ms]
 System available mem: 47688908KB
 Query Max mem: 48968499KB
 Query estimated mem: 131072KB
 Coordinator executor start time: 1.143 ms
 Coordinator executor run time: 20876.876 ms
 Coordinator executor end time: 0.214 ms
 Planner runtime: 1.713 ms
 Query Id: 221239331889535610
 Total runtime: 20878.693 ms

二、SQL语法分析

从用户提供的SQL语句分析，消耗算力有3个地方。

1.left join   正常
2.Count      正常
3. 子查询     正常

首先，查询中2个表关联，大表驱动小表，属于正确的写法，所以正常。
其次，按照需求分组统计，也属于正常的业务正确的写法，所以正常。
最后，子查询，从写法是一个结果集的表，放到from后面也正确，所以正常。
从用户简单的需求写法看，无异常。

三、SQL业务分析

从业务SQL理解，子查询中，主要是对机构和进行条件判断处理，处理后统计。统计完毕后再关联机构表，进行输出结果。这里输出结果时又进行了一个机构的条件判断处理。
也就是其实用户为什么要写个子查询？就是需要对条件判断的机构再进行判断。
所有应该是想要的是一个 嵌套case when。
于是调整为嵌套case when ,经验证结果完全一致。883行记录完全对上。
调整的SQL参考优化后报表SQL内容。

验证业务需求完全符合要求后，我们测试对比执行时间，此时耗时降低到：1721.040 ms即1s左右。

执行计划：

  ====== Query Summary =====                            
----------------------------------------------------------------------------------
 Datanode executor start time [dn_6069_6070, dn_6001_6002]: [10.360 ms,33.457 ms]
 Datanode executor end time [dn_6063_6064, dn_6089_6090]: [1.459 ms,7.791 ms]
 System available mem: 47714304KB
 Query Max mem: 48968499KB
 Query estimated mem: 791389KB
 Coordinator executor start time: 18.827 ms
 Coordinator executor run time: 1701.543 ms
 Coordinator executor end time: 0.302 ms
 Planner runtime: 5.760 ms
 Query Id: 150589112762423306
 Total runtime: 1721.040 ms

四、测试验证

1. 数据结果完全一致。
2. 执行时间提升120倍。由原来的2分钟提升到1s 。

五、结果总结

总结一下慢查询优化的思路，概括起来分4部分。
1.相关表分析。
1. 确定相关表的数据量大小。
2. 确定相关表的表结构是否合理。
是否数据倾斜。
分布键是否能调整为提升性能的join key
3. 表类型。行存表，列存表，复制表。
2.SQL语法分析
SQL语法分析有很多可参考的优化建议。例如：对应同一列进行or判断时，使用in代替or，in可以更有效使用索引，or极少使用到索引。(mysql数据库)
再如：索引列在where条件中禁止使用函数或表达式，使用函数索引失效。（Oracle数据库）
三如：禁止多嵌套查询，嵌套深度[2, 3]层，子查询不得超过两层，可多创建临时表，临时表以join列做分布键。（GaussDB数据库）
每个数据库均有通用型的高性能写法和技巧，对纯开发稍有难度，但是大家可以先有这样一个意识，然后再去寻求解决方案。
3.SQL业务分析
这个SQL业务分析，是稍有难度，要理解业务的本质，有的SQL特别简单，这个我之前遇到过很多次，每一段SQL都是最简单的，表结构，索引，写法都没问题，已经无法从SQL上优化，然而要支撑业务的一个功能，就是简短的SQL组成一个很长的带业务逻辑判断的SQL。这个我们要有一个概括汇总的意识。同时，我们是否可以让业务逻辑回归业务，让数据库回归存储、检索、统计的本质？《管子·形势解》:"明主之官物也,任其所长,不任其所短,故事无不成,而功无不立。
综上所述，慢SQL调优，一看表，二看SQL语法，三看业务本质。以上三点能满足其二优化性能提升30%到80%，达到第三者则有一个质的提升。仅以此文献给有优化需求的同学们。

有同学好奇为什么能提升这么多，想了解更多的请参考具体的执行计划。
原SQL执行计划（复制表）：

 id |                              operation                              |        A-time         |  A-rows   |  E-rows   |  Peak Memory  |   E-memory    |  A-width  | E-width |  E-costs  
----+---------------------------------------------------------------------+-----------------------+-----------+-----------+---------------+---------------+-----------+---------+-----------
  1 | ->  Row Adapter                                                     | 20876.781             |       883 |  12415592 | 72KB          |               |           |     110 | 941065.54
  2 |    ->  Vector Streaming (type: GATHER)                              | 20876.628             |       883 |  12415592 | 474KB         |               |           |     110 | 941065.54
  3 |       ->  Vector Hash Left Join (4, 8)                              | [20365.141,20477.039] |       883 |  12415592 | [645KB,645KB] | 16MB          |           |     110 | 546149.95
  4 |          ->  Vector Hash Aggregate                                  | [20363.293,20475.841] |       883 |  12415592 | [5MB,6MB]     | 47MB(47815MB) | [161,170] |      56 | 539536.93
  5 |             ->  Vector Streaming(type: REDISTRIBUTE)                | [13658.957,20443.851] | 122172173 | 101291003 | [1MB,1MB]     | 2MB           |           |      48 | 438973.81
  6 |                ->  Vector Partition Iterator                        | [639.729,904.649]     | 122172173 | 101291003 | [17KB,17KB]   | 1MB           |           |      48 | 191894.73
  7 |                   ->  Partitioned CStore Scan on edm_suc_info_dtl a | [637.311,902.679]     | 122172173 | 101291003 | [1MB,1MB]     | 1MB           |           |      48 | 191894.73
  8 |          ->  CStore Scan on d_org_io_stat_atho_crnt_dmf t3          | [0.096,0.657]         |    191688 |    191688 | [516KB,541KB] | 1MB           | [67,67]   |      29 | 750.42
(8 rows)

                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                     Predicate Information (identified by plan
 id)                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   3 --Vector Hash Left Join (4, 8)
         Hash Cond: (((CASE WHEN (((a.department)::text = '419907'::text) AND ((a.ywlx)::text = '1049'::text)) THEN '41900S'::character varying WHEN (((a.department)::text = '419907'::text) AND ((a.ywlx)::text = '1065'::text)) THEN '41991E'::character varying WHEN ((a.d
epartment)::text = '419908'::text) THEN '41903R'::character varying WHEN (((a.department)::text = '419999'::text) AND ((a.ywlx)::text = '1009'::text)) THEN '41903R'::character varying WHEN (((a.department)::text = '419999'::text) AND ((a.ywlx)::text = '104204'::text)) T
HEN '41991N'::character varying WHEN (((a.department)::text = '419999'::text) AND ((a.ywlx)::text = '1065'::text)) THEN '41991N'::character varying WHEN ((a.department)::text = '41900O'::text) THEN '4199Y3'::character varying WHEN ((a.department)::text = '41902F'::text)
 THEN '4199Y6'::character varying WHEN ((a.department)::text = '41902R'::text) THEN '41991T'::character varying WHEN ((a.department)::text = '41960A'::text) THEN '4199Z5'::character varying WHEN (((a.department)::text = '41981A'::text) AND ((a.ywlx)::text = '1088'::text
)) THEN '4199Z5'::character varying WHEN ((a.department)::text = '41990B'::text) THEN '4199Y5'::character varying WHEN (((a.department)::text = '41990C'::text) AND ((a.ywlx)::text = ANY ('{1013,1009,1004,1026,1003}'::text[]))) THEN '41903R'::character varying WHEN (((a.
department)::text = '41990C'::text) AND ((a.ywlx)::text = ANY ('{1065,1059}'::text[]))) THEN '41991N'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.ywlx)::text = ANY ('{1023,1032,1036,1045}'::text[]))) THEN '41903R'::character varying WHEN (((
a.department)::text = '41990N'::text) AND ((a.ywlx)::text = ANY ('{1020,1052,1066}'::text[]))) THEN '41981A'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.ywlx)::text = '104204'::text)) THEN '41991N'::character varying WHEN (((a.department)::t
ext = '41990N'::text) AND ((a.ywlx)::text = '1008'::text)) THEN '41991S'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.ywlx)::text = '1042'::text)) THEN '41991S,41990T'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.y
wlx)::text = '1086'::text)) THEN '41991T'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.ywlx)::text = ANY ('{1024,1038,1024024,1024110}'::text[]))) THEN '4199V5'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.ywlx)::t
ext = ANY ('{1004,1027,1081,100401,104201,104203}'::text[]))) THEN '4199X4'::character varying WHEN (((a.department)::text = '41990N'::text) AND ((a.ywlx)::text = ANY ('{1006,1019,1029,1030,1033,1034,1048}'::text[]))) THEN '4199Y3'::character varying WHEN (((a.departmen
t)::text = '41990N'::text) AND ((a.ywlx)::text = '1067'::text)) THEN '4199Z5'::character varying WHEN ((a.department)::text = '41990P'::text) THEN '4199Y1'::character varying WHEN (((a.department)::text = '41990U'::text) AND ((a.ywlx)::text = '1023'::text)) THEN '41903R
'::character varying WHEN (((a.department)::text = '41990U'::text) AND ((a.ywlx)::text = '1004'::text)) THEN '4199X4'::character varying WHEN (((a.department)::text = '41990U'::text) AND ((a.ywlx)::text = '1080'::text)) THEN '4199Z1'::character varying WHEN ((a.departme
nt)::text = '41990V'::text) THEN '4199Z1'::character varying WHEN (((a.department)::text = '41991E'::text) AND ((a.ywlx)::text = '1013'::text)) THEN '41903R'::character varying WHEN (((a.department)::text = '41991N'::text) AND ((a.ywlx)::text = '1046'::text)) THEN '4190
0S'::character varying WHEN (((a.department)::text = '41991P'::text) AND ((a.ywlx)::text = '1063'::text)) THEN '4199Z8'::character varying WHEN (((a.department)::text = '41991S'::text) AND ((a.ywlx)::text = '1085'::text)) THEN '41903R'::character varying WHEN ((a.depart
ment)::text = '41994Z'::text) THEN '41903R'::character varying WHEN ((a.department)::text = '41996A'::text) THEN '41991S'::character varying WHEN ((a.department)::text = '41998C'::text) THEN '4199Y1'::character varying WHEN (((a.department)::text = '41999A'::text) AND (
(a.ywlx)::text = '1013'::text)) THEN '41903R'::character varying WHEN (((a.department)::text = '41999A'::text) AND ((a.ywlx)::text = '1041'::text)) THEN '4199Y8'::character varying ELSE a.department END))::text = (t3.atho_num)::text)
   4 --Vector Hash Aggregate
         Skew Agg Optimized by Statistic
   6 --Vector Partition Iterator
         Iterations: 1
   7 --Partitioned CStore Scan on edm_suc_info_dtl a
         Filter: (((inputtime)::text >= '20220101'::text) AND ((inputtime)::text <= '20220131'::text))
         Selected Partitions:  51
(9 rows)

 Memory Information (identified by plan id) 
--------------------------------------------
 Coordinator Query Peak Memory:
         Query Peak Memory: 8MB
 Datanode:
         Max Query Peak Memory: 16MB
         Min Query Peak Memory: 12MB
   3 --Vector Hash Left Join (4, 8)
         Max Memory Used : 414kB
         Min Memory Used : 414kB
(8 rows)

                      User Define Profiling                       
------------------------------------------------------------------
 Segment Id: 3  Track name: Datanode build connection
  (actual time=[0.521, 2.196], calls=[1, 1])
 Plan Node id: 2  Track name: coordinator get datanode connection
  (actual time=[0.089, 0.089], calls=[1, 1])
 Plan Node id: 7  Track name: load CU description
  (actual time=[0.500, 0.767], calls=[2173, 2194])
 Plan Node id: 7  Track name: min/max check
  (actual time=[0.157, 0.238], calls=[2173, 2194])
 Plan Node id: 7  Track name: fill vector batch
  (actual time=[145.757, 188.623], calls=[2172, 2193])
 Plan Node id: 7  Track name: get CU data
  (actual time=[121.125, 160.044], calls=[2160, 2193])
 Plan Node id: 7  Track name: uncompress CU data
  (actual time=[166.676, 370.909], calls=[83, 222])
 Plan Node id: 7  Track name: apply projection and filter
  (actual time=[483.663, 714.404], calls=[2172, 2193])
 Plan Node id: 7  Track name: fill later vector batch
  (actual time=[247.305, 459.444], calls=[2172, 2193])
 Plan Node id: 7  Track name: get cu data for later read
  (actual time=[61.113, 247.146], calls=[10860, 10965])
 Plan Node id: 8  Track name: load CU description
  (actual time=[0.040, 0.133], calls=[5, 5])
 Plan Node id: 8  Track name: min/max check
  (actual time=[0.000, 0.002], calls=[5, 5])
 Plan Node id: 8  Track name: fill vector batch
  (actual time=[0.040, 0.492], calls=[4, 4])
 Plan Node id: 8  Track name: get CU data
  (actual time=[0.004, 0.437], calls=[8, 8])
 Plan Node id: 8  Track name: uncompress CU data
  (actual time=[0.090, 0.300], calls=[2, 2])
 Plan Node id: 8  Track name: apply projection and filter
  (actual time=[0.000, 0.004], calls=[4, 4])
 Plan Node id: 8  Track name: fill later vector batch
  (actual time=[0.000, 0.002], calls=[4, 4])
(34 rows)

                           ====== Query Summary =====                            
---------------------------------------------------------------------------------
 Datanode executor start time [dn_6093_6094, dn_6021_6022]: [7.609 ms,71.862 ms]
 Datanode executor end time [dn_6105_6106, dn_6091_6092]: [1.524 ms,5.115 ms]
 System available mem: 47688908KB
 Query Max mem: 48968499KB
 Query estimated mem: 131072KB
 Coordinator executor start time: 1.143 ms
 Coordinator executor run time: 20876.876 ms
 Coordinator executor end time: 0.214 ms
 Planner runtime: 1.713 ms
 Query Id: 221239331889535610
 Total runtime: 20878.693 ms

新SQL执行计划（复制表）

create table gds_d_it.d_org_io_stat_atho_crnt_dmf (
data_dt         character varying(10)       ,
atho_num        character varying(200)      ,
atho_name       character varying(200)      ,
atho_abbr       character varying(200)      ,
atho_levl       character varying(4)        ,
supr_atho_num   character varying(200)      ,
dist_flag       character varying(10)       ,
town_flag       character varying(10)       ,
site_flag       character varying(10)       ,
del_ind         character varying(10)       ,
sore_sys        character varying(10)       ,
sore_tabl       character varying(30)  
) with (orientation=column, compression=low) 
Distribute By replication
;
新SQL（复制表）
 id |                                 operation                                 |       A-time        |  A-rows   |  E-rows   |  Peak Memory  |    E-memory    |  A-width  | E-width |  E-costs   
----+---------------------------------------------------------------------------+---------------------+-----------+-----------+---------------+----------------+-----------+---------+------------
  1 | ->  Row Adapter                                                           | 1701.456            |       883 | 101291008 | 82KB          |                |           |      78 | 4498162.71
  2 |    ->  Vector Streaming (type: GATHER)                                    | 1701.326            |       883 | 101291008 | 8256KB        |                |           |      78 | 4498162.71
  3 |       ->  Vector Sort                                                     | [1141.764,1310.271] |       883 | 101291008 | [334KB,635KB] | 463MB(47434MB) | [0,251]   |      78 | 1276294.71
  4 |          ->  Vector Hash Aggregate                                        | [1135.730,1304.422] |       883 | 101290992 | [32MB,32MB]   | 460MB(47431MB) | [0,314]   |      78 | 1083782.33
  5 |             ->  Vector Streaming(type: REDISTRIBUTE)                      | [1105.821,1276.262] |     43253 | 100391984 | [569KB,1MB]   | 2MB            |           |      78 | 1034322.15
  6 |                ->  Vector Hash Aggregate                                  | [937.804,1172.748]  |     43253 | 100391984 | [36MB,36MB]   | 456MB(47428MB) | [206,206] |      78 | 930190.68
  7 |                   ->  Vector Hash Left Join (8, 10)                       | [686.427,848.205]   | 122172173 | 101290992 | [565KB,565KB] | 16MB           |           |      70 | 217305.92
  8 |                      ->  Vector Partition Iterator                        | [523.005,667.709]   | 122172173 | 101290992 | [17KB,17KB]   | 1MB            |           |      48 | 191894.73
  9 |                         ->  Partitioned CStore Scan on edm_suc_info_dtl a | [522.193,666.854]   | 122172173 | 101290992 | [1MB,1MB]     | 1MB            |           |      48 | 191894.73
 10 |                      ->  CStore Scan on d_org_io_stat_atho_crnt_dmf b     | [0.274,0.778]       |    191688 |    191688 | [516KB,541KB] | 1MB            | [67,67]   |      29 | 750.42
(10 rows)

                             Predicate Information (identified by plan id)                             
-------------------------------------------------------------------------------------------------------
   4 --Vector Hash Aggregate
         Skew Agg Optimized by Rule
   7 --Vector Hash Left Join (8, 10)
         Hash Cond: ((a.department)::text = (b.atho_num)::text)
   8 --Vector Partition Iterator
         Iterations: 1
   9 --Partitioned CStore Scan on edm_suc_info_dtl a
         Filter: (((inputtime)::text >= '20220101'::text) AND ((inputtime)::text <= '20220131'::text))
         Selected Partitions:  51
(9 rows)

     Memory Information (identified by plan id)      
-----------------------------------------------------
 Coordinator Query Peak Memory:
         Query Peak Memory: 20MB
 Datanode:
         Max Query Peak Memory: 84MB
         Min Query Peak Memory: 84MB
   3 --Vector Sort
         Sort Method: quicksort  Memory: 2kB ~ 173kB
 Sort Method: quicksort  Disk: 1024kB ~ 0kB
   7 --Vector Hash Left Join (8, 10)
         Max Memory Used : 414kB
         Min Memory Used : 414kB
(11 rows)

                      User Define Profiling                       
------------------------------------------------------------------
 Segment Id: 3  Track name: Datanode build connection
  (actual time=[0.574, 4.848], calls=[1, 1])
 Plan Node id: 2  Track name: coordinator get datanode connection
  (actual time=[0.158, 0.158], calls=[1, 1])
 Plan Node id: 9  Track name: load CU description
  (actual time=[0.285, 0.637], calls=[2173, 2194])
 Plan Node id: 9  Track name: min/max check
  (actual time=[0.080, 0.135], calls=[2173, 2194])
 Plan Node id: 9  Track name: fill vector batch
  (actual time=[100.667, 171.063], calls=[2172, 2193])
 Plan Node id: 9  Track name: get CU data
  (actual time=[87.371, 157.894], calls=[2160, 2193])
 Plan Node id: 9  Track name: uncompress CU data
  (actual time=[227.292, 347.516], calls=[221, 222])
 Plan Node id: 9  Track name: apply projection and filter
  (actual time=[417.988, 527.091], calls=[2172, 2193])
 Plan Node id: 9  Track name: fill later vector batch
  (actual time=[276.292, 362.554], calls=[2172, 2193])
 Plan Node id: 9  Track name: get cu data for later read
  (actual time=[159.466, 231.508], calls=[10860, 10965])
 Plan Node id: 10  Track name: load CU description
  (actual time=[0.050, 0.193], calls=[5, 5])
 Plan Node id: 10  Track name: min/max check
  (actual time=[0.000, 0.004], calls=[5, 5])
 Plan Node id: 10  Track name: fill vector batch
  (actual time=[0.201, 0.586], calls=[4, 4])
 Plan Node id: 10  Track name: get CU data
  (actual time=[0.160, 0.510], calls=[8, 8])
 Plan Node id: 10  Track name: uncompress CU data
  (actual time=[0.092, 0.361], calls=[2, 2])
 Plan Node id: 10  Track name: apply projection and filter
  (actual time=[0.000, 0.004], calls=[4, 4])
 Plan Node id: 10  Track name: fill later vector batch
  (actual time=[0.000, 0.003], calls=[4, 4])
(34 rows)

                            ====== Query Summary =====                            
----------------------------------------------------------------------------------
 Datanode executor start time [dn_6069_6070, dn_6001_6002]: [10.360 ms,33.457 ms]
 Datanode executor end time [dn_6063_6064, dn_6089_6090]: [1.459 ms,7.791 ms]
 System available mem: 47714304KB
 Query Max mem: 48968499KB
 Query estimated mem: 791389KB
 Coordinator executor start time: 18.827 ms
 Coordinator executor run time: 1701.543 ms
 Coordinator executor end time: 0.302 ms
 Planner runtime: 5.760 ms
 Query Id: 150589112762423306
 Total runtime: 1721.040 ms```