报表优化系列文章----如何把一个报表从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四、测试验证
- 数据结果完全一致。
- 执行时间提升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```