GaussDB(DWS)性能调优：表扫描慢——大表字段数据类型转换导致瓶颈案例分析及改写
【摘要】 表扫描是SQL执行中的重要步骤，也常常出现表扫描慢成为SQL瓶颈的情况；为防止这种瓶颈，我们除了要建立一个完善的表模型，还需要在消费数据时使用较好的方案。本篇通过分析一典型案例，展示了对表字段做类型转换可能存在的性能风险，并给出了调优方法。
1、【问题描述】
存在大表扫描，SQL绝大多数时间消耗在表扫描上。
2、【原始SQL】
SELECT
C.REGION_CUSTCATG_CODE AS REGION_CUSTCATG_CODE,
C.REGION_CUSTCATG_CN_NAME AS REGION_CUSTCATG_CN_NAME,
C.REGION_CUSTCATG_EN_NAME AS REGION_CUSTCATG_EN_NAME,
/* c.contact_id,
c.kad_code,
c.photo_url, */
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.CONTACT_ID
END AS CONTACT_ID,
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.KAD_CODE
END AS KAD_CODE,
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.PHOTO_URL
END AS PHOTO_URL,
C.ACTIVITY_QTY,
C.COMPLETE_ACTIVITY_QTY,
C.CONTACT_QTY,
C.COVERED_CONTACT_QTY,
C.CONTACT_QTY - C.COVERED_CONTACT_QTY AS COVERED_NOT_CONTACT_QTY,
C.COVERED_CONTACT_QTY / DECODE(C.CONTACT_QTY, 0, NULL, C.CONTACT_QTY) AS KC_COVERED_RATE,
C.PY_ACTIVITY_QTY
,
Q.LAST_VOC_TIME
FROM
(
SELECT
/* C.CONTACT_ID,
C.KAD_CODE,
C.PHOTO_URL, */
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.CONTACT_ID
END AS CONTACT_ID,
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.KAD_CODE
END AS KAD_CODE,
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.PHOTO_URL
END AS PHOTO_URL,
C.REGION_CUSTCATG_CODE AS REGION_CUSTCATG_CODE,
C.REGION_CUSTCATG_CN_NAME AS REGION_CUSTCATG_CN_NAME,
C.REGION_CUSTCATG_EN_NAME AS REGION_CUSTCATG_EN_NAME,
COUNT(DISTINCT t.activity_id) AS ACTIVITY_QTY,
COUNT(
DISTINCT CASE
WHEN T.ACTIVITY_STATUS = 'Completed' THEN t.activity_id
end
) AS COMPLETE_ACTIVITY_QTY,
COUNT(
DISTINCT CASE
WHEN C.STATUS = 'On Duty' THEN C.CONTACT_ID
END
) as CONTACT_QTY,
COUNT(
DISTINCT CASE
WHEN C.STATUS = 'On Duty'
AND T.ACTIVITY_STATUS = 'Completed' THEN T.CONT_ID
END
) AS COVERED_CONTACT_QTY,
COUNT(DISTINCT T1.ACTIVITY_ID) AS PY_ACTIVITY_QTY
FROM
dmsalesw.DM_MCR_CONTACT_PERIOD_D c --LTC.DWR_MCR_CONTACT_PERIOD_V_S C --"_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DWR_MCR_CONTACT_PERIOD_V" LTC.DM_MCR_CONTACT_PERIOD_D
LEFT JOIN (
--活动信息
select
T.PERIOD_ID_Y,
case
when TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'MM') IN('01', '02', '03') THEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'YYYY') || 'Q1'
WHEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'MM') IN('04', '05', '06') THEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'YYYY') || 'Q2'
WHEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'MM') IN('07', '08', '09') THEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'YYYY') || 'Q3'
WHEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'MM') IN('10', '11', '12') THEN TO_CHAR(to_date(T.PERIOD_ID_D, 'yyyymmdd'), 'YYYY') || 'Q4'
END AS PERIOD_Q,
--活动季度
T.PERIOD_ID_M,
T.PERIOD_ID_D,
T.ACTIVITY_ID,
T.ACTIVITY_DATE,
T.ACTIVITY_SUBJECT,
T.ACTIVITY_TYPE,
T.ACTIVITY_TYPE_CN_NAME,
T.ACTIVITY_STATUS,
T.CONT_ID,
--T.KAD_CODE,
row_number() over(
partition by t.cont_id
order by
t.activity_date desc
) as rn
FROM
dmsalesw.dm_sale_mcr_pc_activity_f t --LTC.DWR_SALE_MCR_PC_ACTIVITY_V_S T --LTC.DWR_SALE_MCR_PC_ACTIVITY_F "_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DWR_SALE_MCR_PC_ACTIVITY_V"
where
T.DATA_TYPE = 'CUST' --活动时间
AND T.PERIOD_ID_M >= 202310
AND T.PERIOD_ID_M <= 202310 --活动创建时间
AND UPPER(T.ACTIVITY_STATUS) IN ('COMPLETED')
) T ON C.CONTACT_ID = T.CONT_ID --and C.KAD_CODE = T.KAD_CODE --不限制联系人客户群与活动客户群
AND TO_CHAR(C.PERIOD_ID) = case
when '202310' = '1' then to_char(current_date, 'YYYYMM')
else '202310'
end
LEFT JOIN (
SELECT
T.CONT_ID,
T.ACTIVITY_ID
FROM
DMSALESW.DM_SALE_MCR_PC_ACTIVITY_F T
/*LTC.DWR_SALE_MCR_PC_ACTIVITY_V_S*/
WHERE
T.DATA_TYPE = 'CUST'
AND '1' = 'Y'
AND UPPER(T.ACTIVITY_STATUS) IN ('COMPLETED')
) T1 ON C.CONTACT_ID = T1.CONT_ID --and C.KAD_CODE = T.KAD_CODE --不限制联系人客户群与活动客户群
AND TO_CHAR(C.PERIOD_ID) = case
when '202310' = '1' then to_char(current_date, 'YYYYMM')
else '202310'
end --zwx1018216联系人所属年月
LEFT JOIN dmsalesw.dm_mcr_object_tag_f f --"_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DWR_MCR_OBJECT_TAG_V" F
ON t.ACTIVITY_ID = F.PRIMARY_ID
AND F.OBJECT_TYPE = 'ActivityTag'
LEFT JOIN (
select
A.CONTACT_ID as CONT_ID,
--A.kad_code,
'Y' as NO_TOUCH_FLAG
from
(
select
F.CONTACT_ID,
DATEDIFF(NOW(), TO_DATE(max(t.ACTIVITY_DATE))) as NO_TOUCH_DAYS --联系人最近一次活动到当前的天数
FROM
DMSALESW.DM_MCR_ACTIVITY_F T
INNER JOIN dmsalesw.dm_mcr_act_cust_attendee_rel F --LTC.DWR_MCR_ACT_CUST_ATTENDEE_REL_V_S t
ON T.ACTIVITY_ID = F.ACTIVITY_ID
WHERE
1 = 1
AND t.ACTIVITY_ID IS not NULL --三个页签不同传值,不传为预警长期无接触
AND t.ACTIVITY_STATUS = 'Released'
GROUP BY
F.CONTACT_ID
) A
GROUP BY
A.CONTACT_ID --,A.kad_code
) act on C.CONTACT_ID = ACT.CONT_ID
WHERE
C.DEL_FLAG = 'N'
AND C.REGION_CODE NOT IN (
'026700',
'025718',
'045222',
'048625',
'049682',
'038185',
'SNULL',
'024387',
'072133'
) --zwx1018216 剔除A类、B类 地区部/*组织调整072133 A类区域管理部 20230422*/
AND C.REPOFFICE_CODE NOT IN ('037370', '026679') --2022-11-06 剔除俄罗斯，白俄罗斯
AND C.OFFICE_CODE NOT IN ('026700', '045222') --剔除A类办事处
AND CASE
WHEN C.REGION_CUSTCATG_CODE = 'Others'
AND (
C.REGION_CODE = '022471'
OR C.OFFICE_CODE = '027425'
) THEN 'N'
ELSE 'Y'
END = 'Y'
AND C.KC_FLAG = 'Y'
AND C.STATUS = 'On Duty' --联系人状态
AND C.REPOFFICE_CODE IN ('026219') --zwx1018216 P_ROLE 用户角色入参 20210330
AND TO_CHAR(C.PERIOD_ID) = case
when '202310' = '1' then to_char(current_date, 'YYYYMM')
else '202310'
end --zwx1018216联系人所属年月
AND EXISTS (
SELECT
1
FROM
dmsalesw.dm_mcr_object_tag_f f --"_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DWR_MCR_OBJECT_TAG_V" F --LTC.DWR_MCR_OBJECT_TAG_F
WHERE
1 = 1
AND C.CONTACT_ID = F.PRIMARY_ID
AND F.OBJECT_TYPE = 'ContactTag'
AND F.TAG_VALUE IN ('4. 明日之星 / Future Star')
)
AND C.PERIOD_ID <= 202310
GROUP BY
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.CONTACT_ID
END,
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.KAD_CODE
END,
CASE
WHEN 'N' = 'N' THEN NULL
WHEN 1 = 1 THEN C.PHOTO_URL
END,
C.REGION_CUSTCATG_CODE,
C.REGION_CUSTCATG_CN_NAME,
C.REGION_CUSTCATG_EN_NAME
) C --联系人、活动信息
LEFT JOIN (
select
T.RAISED_BY_ID,
T.KAD_CODE,
MAX(T.CREATED_DT_WID) as LAST_VOC_TIME,
--最后一次提出声音时间 2021-07-31 zwx914764
-- COUNT(*) AS VOC_TIMES,
SUM(
CASE
WHEN UPPER(T.STATUS) <> 'CANCELLED' THEN 1
ELSE 0
END
) as VOC_TIMES --客户声音数量【时间范围内】
FROM
dmsalesw.dm_mcr_action_plan_f t --LTC.DM_MCR_ACTION_PLAN_V_S T --LTC.DM_MCR_ACTION_PLAN_F "_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DM_MCR_ACTION_PLAN_V"
WHERE
T.ACTION_TYPE = 'VOC' --2021.02.25 zwx1018216 声音表限制
AND UPPER(T.STATUS) NOT IN ('CANCELLED', 'CANCELLED-APPROVING')
and T.REPOFFICE_CODE in('026219')
group by
1,
T.RAISED_BY_ID,
T.KAD_CODE
) Q --声音信息
ON C.CONTACT_ID = Q.RAISED_BY_ID
and C.KAD_CODE = Q.KAD_CODE -- zwx1018216 客户群关联
LEFT JOIN (
SELECT
CON.CONTACT_ID,
count(distinct f.plan_id) AS PLAN_QTY --计划总数，剔除取消状态
FROM
DMSALESW.BAS_MCR_CR_PLAN_F F --LTC.DWR_MCR_CR_PLAN_V_S F --dwrsales.DWR_MCR_CR_PLAN_F
INNER JOIN dmsalesw.dm_mcr_attendee_t con --LTC.MCR_ATTENDEE_T CON --SDILTC.MCR_ATTENDEE_T_2820
ON 1 = 1
and f.plan_id = con.PRIMARY_ID
and con.ATTENDEE_TYPE = 'CRPlanCusAttendee'
where
1 = 1
AND 'N' = 'Y'
AND CON.CONTACT_ID is not null
AND (
F.STATUS = 'Released'
AND F.PROGRESS IN ('Preparing', 'Postponed', 'Completed')
)
GROUP BY
CON.CONTACT_ID
) L ON C.CONTACT_ID = L.CONTACT_ID
WHERE
1 = 1
;3、【性能分析】
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
-----+----------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+--------+--------+------------+----------------+----------+-----------+---------+-----------
1 | -> Row Adapter | 23535.302 | 3 | 16 | | 131KB | | | 129 | 768148.39
2 | -> Vector Streaming (type: GATHER) | 23535.276 | 3 | 16 | | 296KB | | | 129 | 768148.39
3 | -> Vector Hash Left Join (4, 129) | [23481.189, 23484.040] | 3 | 16 | | [580KB, 1MB] | 16MB | | 129 | 768114.39
4 | -> Vector Streaming(type: REDISTRIBUTE) | [23479.207, 23481.669] | 3 | 16 | 1 | [264KB, 312KB] | 2MB | | 161 | 765912.17
5 | -> Vector Subquery Scan on c | [23405.558, 23478.490] | 3 | 16 | | [248KB, 248KB] | 1MB | | 161 | 765911.09
6 | -> Vector Sonic Hash Join (7,105) | [23405.555, 23478.484] | 3 | 16 | | [2MB, 2MB] | 16MB | | 193 | 765911.08
7 | -> Vector Sonic Hash Join (8,81) | [70.488, 72.356] | 3 | 2 | 1 | [2MB, 2MB] | 16MB | | 644 | 631900.84
8 | -> Vector Sonic Hash Join (9,57) | [2.104, 4.020] | 3 | 16 | 1 | [1MB, 1MB] | 16MB | | 483 | 497890.57
9 | -> Vector Sonic Hash Join (10,33) | [1.458, 2.601] | 3 | 2 | 1 | [1MB, 1MB] | 16MB | | 322 | 363880.32
10 | -> Vector Sonic Hash Aggregate | [0.931, 1.249] | 3 | 14 | 1 | [1MB, 1MB] | 16MB | [139,145] | 83 | 229870.06
11 | -> Vector Sonic Hash Aggregate | [0.548, 0.688] | 63 | 14 | | [1MB, 1MB] | 16MB | [154,159] | 75 | 229870.03
12 | -> Vector Streaming(type: REDISTRIBUTE) | [0.108, 0.134] | 82 | 14 | | [328KB, 328KB] | 2MB | | 67 | 229870.00
13 | -> Vector Hash Left Join (14, 31) | [23302.202, 23304.827] | 82 | 14 | | [580KB, 585KB] | 16MB | | 67 | 229869.91
14 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [23300.122, 23300.545] | 64 | 10 | 1 | [264KB, 328KB] | 2MB | | 67 | 228659.41
15 | -> Vector Hash Left Join (16, 28) | [23301.862, 23304.564] | 64 | 10 | | [580KB, 580KB] | 16MB | | 67 | 228659.33
16 | -> Vector Streaming(type: REDISTRIBUTE) | [23299.830, 23302.789] | 64 | 10 | 1 | [264KB, 360KB] | 2MB | | 84 | 133583.17
17 | -> Vector Hash Right Join (18, 21) | [23300.917, 23303.830] | 64 | 10 | | [580KB, 588KB] | 16MB | | 84 | 133583.09
18 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.233, 0.425] | 27303 | 17248 | 1078 | [296KB, 296KB] | 2MB | | 20 | 102051.31
19 | -> Vector Subquery Scan on t | [29.937, 59.184] | 39781 | 17248 | | [120KB, 120KB] | 1MB | | 20 | 101962.37
20 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [29.619, 58.649] | 39781 | 17248 | | [3MB, 3MB] | 1MB | | 28 | 101951.59
21 | -> Vector Streaming(type: REDISTRIBUTE) | [23300.226, 23303.270] | 18 | 10 | 1 | [264KB, 344KB] | 2MB | [0,198] | 74 | 31529.05
22 | -> Vector Nest Loop (23,27) | [22287.656, 23299.157] | 18 | 10 | | [512KB, 512KB] | 1MB | | 74 | 31528.97
23 | -> Vector Sonic Hash Aggregate | [0.405, 2.542] | 1600 | 160 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4201.51
24 | -> Vector Result | [0.122, 2.249] | 1632 | 69 | | [120KB, 120KB] | 1MB | | 10 | 4201.38
25 | -> Vector Streaming(type: BROADCAST) | [0.083, 2.179] | 1632 | 160 | | [280KB, 280KB] | 2MB | | 10 | 4201.38
26 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.416, 0.890] | 102 | 10 | | [1MB, 1MB] | 1MB | | 10 | 4198.86
27 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [22280.465, 23292.142] | 18 | 1 | | [6MB, 6MB] | 16MB | | 74 | 2732.74
28 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.005, 2.558] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95076.13
29 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95076.13
30 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95076.13
31 | -> Vector Streaming(type: REDISTRIBUTE) | [1.202, 3.703] | 78523 | 78753 | 4766 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4585.82
32 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.441, 0.817] | 78523 | 78753 | | [1MB, 1MB] | 1MB | | 10 | 4179.75
33 | -> Vector Subquery Scan on subquery | [0.331, 0.864] | 3 | 14 | 1 | [120KB, 120KB] | 1MB | | 161 | 229870.08
34 | -> Vector Sonic Hash Aggregate | [0.327, 0.858] | 3 | 14 | | [1MB, 1MB] | 16MB | [139,145] | 93 | 229870.07
35 | -> Vector Hash Aggregate | [0.173, 0.282] | 63 | 14 | | [584KB, 584KB] | 16MB | [195,200] | 85 | 229870.04
36 | -> Vector Streaming(type: REDISTRIBUTE) | [0.108, 0.142] | 82 | 14 | | [360KB, 360KB] | 2MB | | 77 | 229870.01
37 | -> Vector Hash Left Join (38, 55) | [23166.184, 23173.506] | 82 | 14 | | [838KB, 849KB] | 16MB | | 77 | 229869.91
38 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [23165.157, 23169.218] | 64 | 10 | 1 | [264KB, 344KB] | 2MB | | 77 | 228659.41
39 | -> Vector Hash Left Join (40, 52) | [23169.436, 23173.633] | 64 | 10 | | [580KB, 580KB] | 16MB | | 77 | 228659.33
40 | -> Vector Streaming(type: REDISTRIBUTE) | [23167.857, 23171.554] | 64 | 10 | 1 | [264KB, 376KB] | 2MB | | 94 | 133583.17
41 | -> Vector Hash Right Join (42, 45) | [23169.482, 23172.872] | 64 | 10 | | [580KB, 588KB] | 16MB | | 94 | 133583.09
42 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.474, 0.779] | 27303 | 17248 | 1078 | [312KB, 376KB] | 2MB | | 30 | 102051.31
43 | -> Vector Subquery Scan on t | [30.021, 60.793] | 39781 | 17248 | | [120KB, 120KB] | 1MB | | 30 | 101962.37
44 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [29.681, 60.226] | 39781 | 17248 | | [3MB, 3MB] | 1MB | | 38 | 101951.59
45 | -> Vector Streaming(type: REDISTRIBUTE) | [23168.563, 23171.953] | 18 | 10 | 1 | [264KB, 344KB] | 2MB | [0,198] | 74 | 31529.05
46 | -> Vector Nest Loop (47,51) | [22332.564, 23168.675] | 18 | 10 | | [512KB, 512KB] | 1MB | | 74 | 31528.97
47 | -> Vector Sonic Hash Aggregate | [0.325, 3.179] | 1600 | 160 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4201.51
48 | -> Vector Result | [0.117, 2.995] | 1632 | 69 | | [120KB, 120KB] | 1MB | | 10 | 4201.38
49 | -> Vector Streaming(type: BROADCAST) | [0.072, 2.919] | 1632 | 160 | | [280KB, 280KB] | 2MB | | 10 | 4201.38
50 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.410, 0.903] | 102 | 10 | | [1MB, 1MB] | 1MB | | 10 | 4198.86
51 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [22325.622, 23160.580] | 18 | 1 | | [6MB, 6MB] | 16MB | | 74 | 2732.74
52 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.007, 4.107] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95076.13
53 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95076.13
54 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95076.13
55 | -> Vector Streaming(type: REDISTRIBUTE) | [0.397, 4.969] | 78523 | 78753 | 4766 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4585.82
56 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.398, 0.761] | 78523 | 78753 | | [1MB, 1MB] | 1MB | | 10 | 4179.75
57 | -> Vector Subquery Scan on subquery | [0.420, 0.867] | 3 | 14 | 1 | [120KB, 120KB] | 1MB | | 161 | 229870.08
58 | -> Vector Sonic Hash Aggregate | [0.415, 0.861] | 3 | 14 | | [1MB, 1MB] | 16MB | [139,145] | 91 | 229870.07
59 | -> Vector Hash Aggregate | [0.206, 0.331] | 18 | 14 | | [584KB, 584KB] | 16MB | [194,200] | 83 | 229870.04
60 | -> Vector Streaming(type: REDISTRIBUTE) | [0.111, 0.160] | 82 | 14 | | [360KB, 360KB] | 2MB | | 75 | 229870.00
61 | -> Vector Hash Left Join (62, 79) | [23417.986, 23422.415] | 82 | 14 | | [838KB, 849KB] | 16MB | | 75 | 229869.91
62 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [23415.725, 23416.014] | 64 | 10 | 1 | [264KB, 360KB] | 2MB | | 85 | 228659.41
63 | -> Vector Hash Left Join (64, 76) | [23417.554, 23421.479] | 64 | 10 | | [580KB, 580KB] | 16MB | | 85 | 228659.33
64 | -> Vector Streaming(type: REDISTRIBUTE) | [23415.370, 23417.988] | 64 | 10 | 1 | [264KB, 376KB] | 2MB | | 92 | 133583.17
65 | -> Vector Hash Right Join (66, 69) | [23417.641, 23420.767] | 64 | 10 | | [580KB, 588KB] | 16MB | | 92 | 133583.09
66 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.236, 0.327] | 27303 | 17248 | 1078 | [296KB, 296KB] | 2MB | | 20 | 102051.31
67 | -> Vector Subquery Scan on t | [29.226, 61.655] | 39781 | 17248 | | [120KB, 120KB] | 1MB | | 20 | 101962.37
68 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [28.910, 61.115] | 39781 | 17248 | | [3MB, 3MB] | 1MB | | 28 | 101951.59
69 | -> Vector Streaming(type: REDISTRIBUTE) | [23417.375, 23420.541] | 18 | 10 | 1 | [264KB, 360KB] | 2MB | [0,225] | 82 | 31529.05
70 | -> Vector Nest Loop (71,75) | [22058.124, 23417.222] | 18 | 10 | | [512KB, 512KB] | 1MB | | 82 | 31528.97
71 | -> Vector Sonic Hash Aggregate | [0.350, 3.599] | 1600 | 160 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4201.51
72 | -> Vector Result | [0.104, 3.366] | 1632 | 69 | | [120KB, 120KB] | 1MB | | 10 | 4201.38
73 | -> Vector Streaming(type: BROADCAST) | [0.068, 3.273] | 1632 | 160 | | [280KB, 280KB] | 2MB | | 10 | 4201.38
74 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.414, 0.854] | 102 | 10 | | [1MB, 1MB] | 1MB | | 10 | 4198.86
75 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [22049.161, 23407.240] | 18 | 1 | | [6MB, 6MB] | 16MB | | 82 | 2732.74
76 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.009, 3.553] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95076.13
77 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95076.13
78 | -> Result | [0.000, 0.000] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95076.13
79 | -> Vector Streaming(type: REDISTRIBUTE) | [1.469, 5.951] | 78523 | 78753 | 4766 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4585.82
80 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.435, 1.093] | 78523 | 78753 | | [1MB, 1MB] | 1MB | | 10 | 4179.75
81 | -> Vector Subquery Scan on subquery | [67.710, 68.124] | 3 | 14 | 1 | [120KB, 120KB] | 1MB | | 161 | 229870.09
82 | -> Vector Sonic Hash Aggregate | [67.703, 68.118] | 3 | 14 | | [1MB, 1MB] | 16MB | [139,145] | 101 | 229870.08
83 | -> Vector Hash Aggregate | [67.038, 67.931] | 17 | 14 | | [584KB, 584KB] | 16MB | [194,198] | 93 | 229870.05
84 | -> Vector Streaming(type: REDISTRIBUTE) | [66.862, 67.856] | 82 | 14 | | [376KB, 376KB] | 2MB | | 85 | 229870.01
85 | -> Vector Hash Left Join (86, 103) | [23475.758, 23480.289] | 82 | 14 | | [838KB, 849KB] | 16MB | | 85 | 229869.91
86 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [23473.030, 23475.611] | 64 | 10 | 1 | [264KB, 376KB] | 2MB | | 95 | 228659.41
87 | -> Vector Hash Left Join (88, 100) | [23476.383, 23479.481] | 64 | 10 | | [580KB, 580KB] | 16MB | | 95 | 228659.33
88 | -> Vector Streaming(type: REDISTRIBUTE) | [23473.139, 23479.171] | 64 | 10 | 1 | [264KB, 408KB] | 2MB | | 112 | 133583.17
89 | -> Vector Hash Right Join (90, 93) | [23475.495, 23478.942] | 64 | 10 | | [580KB, 588KB] | 16MB | | 112 | 133583.09
90 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.596, 0.911] | 27303 | 17248 | 1078 | [312KB, 376KB] | 2MB | | 30 | 102051.31
91 | -> Vector Subquery Scan on t | [30.489, 61.424] | 39781 | 17248 | | [120KB, 120KB] | 1MB | | 30 | 101962.37
92 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [30.189, 60.880] | 39781 | 17248 | | [3MB, 3MB] | 1MB | | 38 | 101951.59
93 | -> Vector Streaming(type: REDISTRIBUTE) | [23474.969, 23477.943] | 18 | 10 | 1 | [264KB, 360KB] | 2MB | [0,225] | 82 | 31529.05
94 | -> Vector Nest Loop (95,99) | [22177.840, 23474.376] | 18 | 10 | | [512KB, 512KB] | 1MB | | 82 | 31528.97
95 | -> Vector Sonic Hash Aggregate | [0.428, 3.821] | 1600 | 160 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4201.51
96 | -> Vector Result | [0.141, 3.557] | 1632 | 69 | | [120KB, 120KB] | 1MB | | 10 | 4201.38
97 | -> Vector Streaming(type: BROADCAST) | [0.082, 3.473] | 1632 | 160 | | [280KB, 280KB] | 2MB | | 10 | 4201.38
98 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.440, 0.765] | 102 | 10 | | [1MB, 1MB] | 1MB | | 10 | 4198.86
99 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [22170.957, 23465.578] | 18 | 1 | | [6MB, 6MB] | 16MB | | 82 | 2732.74
100 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.008, 5.328] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95076.13
101 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95076.13
102 | -> Result | [0.000, 0.000] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95076.13
103 | -> Vector Streaming(type: REDISTRIBUTE) | [0.401, 5.523] | 78523 | 78753 | 4766 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4585.82
104 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.424, 0.746] | 78523 | 78753 | | [1MB, 1MB] | 1MB | | 10 | 4179.75
105 | -> Vector Subquery Scan on subquery | [23404.697, 23407.604] | 3 | 14 | 1 | [120KB, 120KB] | 1MB | | 161 | 229870.07
106 | -> Vector Sonic Hash Aggregate | [23404.695, 23407.601] | 3 | 14 | | [1MB, 1MB] | 16MB | [0,145] | 83 | 229870.06
107 | -> Vector Sonic Hash Aggregate | [23404.147, 23406.896] | 3 | 14 | | [1MB, 1MB] | 16MB | [0,146] | 75 | 229870.03
108 | -> Vector Streaming(type: REDISTRIBUTE) | [23403.632, 23406.524] | 82 | 14 | | [264KB, 312KB] | 2MB | | 67 | 229870.00
109 | -> Vector Hash Left Join (110, 127) | [23407.315, 23412.491] | 82 | 14 | | [580KB, 585KB] | 16MB | | 67 | 229869.91
110 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [23405.127, 23408.768] | 64 | 10 | 1 | [264KB, 328KB] | 2MB | | 77 | 228659.41
111 | -> Vector Hash Left Join (112, 124) | [23410.124, 23412.017] | 64 | 10 | | [580KB, 580KB] | 16MB | | 77 | 228659.33
112 | -> Vector Streaming(type: REDISTRIBUTE) | [23406.685, 23409.714] | 64 | 10 | 1 | [264KB, 360KB] | 2MB | | 84 | 133583.17
113 | -> Vector Hash Right Join (114, 117) | [23406.522, 23411.418] | 64 | 10 | | [580KB, 588KB] | 16MB | | 84 | 133583.09
114 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.230, 0.448] | 27303 | 17248 | 1078 | [296KB, 296KB] | 2MB | | 20 | 102051.31
115 | -> Vector Subquery Scan on t | [30.631, 62.369] | 39781 | 17248 | | [120KB, 120KB] | 1MB | | 20 | 101962.37
116 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [30.297, 61.830] | 39781 | 17248 | | [3MB, 3MB] | 1MB | | 28 | 101951.59
117 | -> Vector Streaming(type: REDISTRIBUTE) | [23405.923, 23410.704] | 18 | 10 | 1 | [264KB, 344KB] | 2MB | [0,198] | 74 | 31529.05
118 | -> Vector Nest Loop (119,123) | [21985.239, 23407.404] | 18 | 10 | | [512KB, 512KB] | 1MB | | 74 | 31528.97
119 | -> Vector Sonic Hash Aggregate | [0.438, 3.216] | 1600 | 160 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4201.51
120 | -> Vector Result | [0.160, 2.781] | 1632 | 69 | | [120KB, 120KB] | 1MB | | 10 | 4201.38
121 | -> Vector Streaming(type: BROADCAST) | [0.097, 2.706] | 1632 | 160 | | [280KB, 280KB] | 2MB | | 10 | 4201.38
122 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.415, 0.890] | 102 | 10 | | [1MB, 1MB] | 1MB | | 10 | 4198.86
123 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [21978.367, 23400.624] | 18 | 1 | | [6MB, 6MB] | 16MB | | 74 | 2732.74
124 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.510, 4.600] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 20 | 95076.13
125 | -> Vector Adapter | [0.001, 0.001] | 0 | 1 | | [56KB, 56KB] | 1MB | | 0 | 95076.13
126 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95076.13
127 | -> Vector Streaming(type: REDISTRIBUTE) | [0.362, 4.752] | 78523 | 78753 | 4766 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4585.82
128 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.437, 0.858] | 78523 | 78753 | | [1MB, 1MB] | 1MB | | 10 | 4179.75
129 | -> Vector Subquery Scan on q | [0.215, 2.712] | 139 | 448 | 28 | [120KB, 120KB] | 1MB | [91,94] | 55 | 8806.54
130 | -> Vector Sonic Hash Aggregate | [0.213, 2.707] | 139 | 448 | | [948KB, 948KB] | 16MB | [63,66] | 62 | 8806.26
131 | -> Vector Streaming(type: REDISTRIBUTE) | [0.048, 2.419] | 435 | 451 | | [312KB, 312KB] | 2MB | | 30 | 8805.77
132 | -> CStore Scan on dmsalesw.dm_mcr_action_plan_f t | [0.989, 2.298] | 435 | 451 | | [3MB, 3MB] | 1MB | | 30 | 8804.80
从计划中可以看出，三个扫描物理表c的算子运行时间都非常长，占了整个SQL的绝大部分时间。在SQL中寻找对这个表的扫描，可以看到三处表扫描的SQL是这部分相似的语句：
...
AND TO_CHAR(C.PERIOD_ID) = case
when '202310' = '1' then to_char(current_date, 'YYYYMM')
else '202310'
end
...这部分对物理表c做了对PERIOD_ID字段过滤的语句，使用了TO_CHAR来转换PERIOD_ID字段的类型。对字段做类型转换效率是比较低的。通过pg_get_tabledef()查看物理表c的PERIOD_ID字段类型是numeric。我们试试把这个从numeric到text的转换转移到等号右边的效果如何。
4、【改写SQL】
改写后部分SQL：
...
AND C.PERIOD_ID = case
when '202310' = '1' then to_char(current_date, 'YYYYMM')::numeric
else 202310
end
...改写后只用了300ms就执行完了。执行计划如下：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
-----+----------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+--------+--------+------------+----------------+----------+-----------+---------+-----------
1 | -> Row Adapter | 142.550 | 3 | 16 | | 131KB | | | 129 | 768359.84
2 | -> Vector Streaming (type: GATHER) | 142.541 | 3 | 16 | | 296KB | | | 129 | 768359.84
3 | -> Vector Hash Left Join (4, 129) | [74.319, 77.300] | 3 | 16 | | [580KB, 1MB] | 16MB | | 129 | 768325.84
4 | -> Vector Streaming(type: REDISTRIBUTE) | [69.298, 71.748] | 3 | 16 | 1 | [264KB, 312KB] | 2MB | | 161 | 765992.41
5 | -> Vector Subquery Scan on c | [64.348, 71.748] | 3 | 16 | | [248KB, 248KB] | 1MB | | 161 | 765991.33
6 | -> Vector Sonic Hash Join (7,105) | [64.340, 71.744] | 3 | 16 | | [2MB, 2MB] | 16MB | | 193 | 765991.32
7 | -> Vector Sonic Hash Join (8,81) | [4.539, 6.084] | 3 | 2 | 1 | [2MB, 2MB] | 16MB | | 644 | 631745.80
8 | -> Vector Sonic Hash Join (9,57) | [3.600, 4.327] | 3 | 16 | 1 | [1MB, 1MB] | 16MB | | 483 | 497500.26
9 | -> Vector Sonic Hash Join (10,33) | [2.239, 2.539] | 3 | 2 | 1 | [1MB, 1MB] | 16MB | | 322 | 363254.73
10 | -> Vector Sonic Hash Aggregate | [1.046, 1.177] | 3 | 15 | 1 | [1MB, 1MB] | 16MB | [139,145] | 83 | 229009.19
11 | -> Vector Sonic Hash Aggregate | [0.607, 0.639] | 75 | 15 | | [1MB, 1MB] | 16MB | [154,160] | 75 | 229009.17
12 | -> Vector Streaming(type: REDISTRIBUTE) | [0.096, 0.149] | 96 | 15 | | [328KB, 328KB] | 2MB | | 67 | 229009.14
13 | -> Vector Hash Left Join (14, 31) | [66.391, 70.212] | 96 | 15 | | [585KB, 594KB] | 16MB | | 67 | 229009.04
14 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [60.960, 64.439] | 76 | 11 | 1 | [328KB, 328KB] | 2MB | | 67 | 227794.32
15 | -> Vector Hash Left Join (16, 28) | [65.789, 69.540] | 76 | 11 | | [580KB, 580KB] | 16MB | | 67 | 227794.23
16 | -> Vector Streaming(type: REDISTRIBUTE) | [65.537, 67.284] | 76 | 11 | 1 | [264KB, 360KB] | 2MB | | 84 | 132445.56
17 | -> Vector Hash Right Join (18, 21) | [10.330, 68.703] | 76 | 11 | | [580KB, 588KB] | 16MB | | 84 | 132445.48
18 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [55.561, 55.957] | 41773 | 46163 | 2885 | [312KB, 521KB] | 2MB | | 20 | 102647.46
19 | -> Vector Subquery Scan on t | [31.988, 61.889] | 60841 | 46163 | | [120KB, 120KB] | 1MB | | 20 | 102409.45
20 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [31.633, 61.318] | 60841 | 46163 | | [3MB, 3MB] | 1MB | | 28 | 102380.60
21 | -> Vector Streaming(type: REDISTRIBUTE) | [9.225, 12.780] | 17 | 11 | 1 | [264KB, 344KB] | 2MB | [0,198] | 74 | 29790.78
22 | -> Vector Nest Loop (23,27) | [4.182, 9.992] | 17 | 11 | | [512KB, 512KB] | 1MB | | 74 | 29790.69
23 | -> Vector Sonic Hash Aggregate | [0.593, 5.972] | 1648 | 176 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4184.62
24 | -> Vector Result | [0.211, 5.620] | 1712 | 79 | | [120KB, 120KB] | 1MB | | 10 | 4184.48
25 | -> Vector Streaming(type: BROADCAST) | [0.130, 5.526] | 1712 | 176 | | [280KB, 280KB] | 2MB | | 10 | 4184.48
26 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.435, 0.744] | 107 | 11 | | [1MB, 1MB] | 1MB | | 10 | 4181.96
27 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [1.552, 3.199] | 17 | 1 | | [6MB, 6MB] | 16MB | | 74 | 2327.81
28 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.005, 2.080] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95348.65
29 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95348.65
30 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95348.65
31 | -> Vector Streaming(type: REDISTRIBUTE) | [1.388, 7.650] | 83288 | 82943 | 5017 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4589.66
32 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.427, 0.768] | 83288 | 82943 | | [1MB, 1MB] | 1MB | | 10 | 4161.98
33 | -> Vector Subquery Scan on subquery | [0.711, 0.830] | 3 | 15 | 1 | [120KB, 120KB] | 1MB | | 161 | 229009.22
34 | -> Vector Sonic Hash Aggregate | [0.705, 0.826] | 3 | 15 | | [1MB, 1MB] | 16MB | [139,145] | 93 | 229009.21
35 | -> Vector Hash Aggregate | [0.218, 0.302] | 75 | 15 | | [584KB, 600KB] | 16MB | [195,201] | 85 | 229009.18
36 | -> Vector Streaming(type: REDISTRIBUTE) | [0.101, 0.144] | 96 | 15 | | [360KB, 360KB] | 2MB | | 77 | 229009.14
37 | -> Vector Hash Left Join (38, 55) | [66.927, 70.532] | 96 | 15 | | [849KB, 858KB] | 16MB | | 77 | 229009.04
38 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [58.831, 65.168] | 76 | 11 | 1 | [344KB, 344KB] | 2MB | | 77 | 227794.32
39 | -> Vector Hash Left Join (40, 52) | [66.351, 69.787] | 76 | 11 | | [580KB, 580KB] | 16MB | | 77 | 227794.23
40 | -> Vector Streaming(type: REDISTRIBUTE) | [66.021, 68.554] | 76 | 11 | 1 | [264KB, 376KB] | 2MB | | 94 | 132445.56
41 | -> Vector Hash Right Join (42, 45) | [10.720, 69.296] | 76 | 11 | | [580KB, 588KB] | 16MB | | 94 | 132445.48
42 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [55.723, 56.106] | 41773 | 46163 | 2885 | [553KB, 569KB] | 2MB | | 30 | 102647.46
43 | -> Vector Subquery Scan on t | [32.879, 63.143] | 60841 | 46163 | | [120KB, 120KB] | 1MB | | 30 | 102409.45
44 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [32.565, 62.581] | 60841 | 46163 | | [3MB, 3MB] | 1MB | | 38 | 102380.60
45 | -> Vector Streaming(type: REDISTRIBUTE) | [10.336, 12.889] | 17 | 11 | 1 | [264KB, 344KB] | 2MB | [0,198] | 74 | 29790.78
46 | -> Vector Nest Loop (47,51) | [4.436, 9.763] | 17 | 11 | | [512KB, 512KB] | 1MB | | 74 | 29790.69
47 | -> Vector Sonic Hash Aggregate | [0.567, 5.957] | 1648 | 176 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4184.62
48 | -> Vector Result | [0.176, 5.588] | 1712 | 79 | | [120KB, 120KB] | 1MB | | 10 | 4184.48
49 | -> Vector Streaming(type: BROADCAST) | [0.104, 5.525] | 1712 | 176 | | [280KB, 280KB] | 2MB | | 10 | 4184.48
50 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.452, 0.779] | 107 | 11 | | [1MB, 1MB] | 1MB | | 10 | 4181.96
51 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [1.528, 3.224] | 17 | 1 | | [6MB, 6MB] | 16MB | | 74 | 2327.81
52 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.006, 1.718] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95348.65
53 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95348.65
54 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95348.65
55 | -> Vector Streaming(type: REDISTRIBUTE) | [1.252, 11.025] | 83288 | 82943 | 5017 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4589.66
56 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.439, 0.843] | 83288 | 82943 | | [1MB, 1MB] | 1MB | | 10 | 4161.98
57 | -> Vector Subquery Scan on subquery | [0.755, 1.074] | 3 | 15 | 1 | [120KB, 120KB] | 1MB | | 161 | 229009.22
58 | -> Vector Sonic Hash Aggregate | [0.745, 1.069] | 3 | 15 | | [1MB, 1MB] | 16MB | [139,145] | 91 | 229009.21
59 | -> Vector Hash Aggregate | [0.215, 0.245] | 17 | 15 | | [584KB, 584KB] | 16MB | [194,200] | 83 | 229009.18
60 | -> Vector Streaming(type: REDISTRIBUTE) | [0.113, 0.126] | 96 | 15 | | [360KB, 360KB] | 2MB | | 75 | 229009.14
61 | -> Vector Hash Left Join (62, 79) | [66.878, 70.926] | 96 | 15 | | [849KB, 858KB] | 16MB | | 75 | 229009.04
62 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [60.652, 65.145] | 76 | 11 | 1 | [360KB, 360KB] | 2MB | | 85 | 227794.32
63 | -> Vector Hash Left Join (64, 76) | [63.385, 68.935] | 76 | 11 | | [580KB, 580KB] | 16MB | | 85 | 227794.23
64 | -> Vector Streaming(type: REDISTRIBUTE) | [63.143, 68.057] | 76 | 11 | 1 | [264KB, 376KB] | 2MB | | 92 | 132445.56
65 | -> Vector Hash Right Join (66, 69) | [9.061, 67.040] | 76 | 11 | | [580KB, 588KB] | 16MB | | 92 | 132445.48
66 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [54.585, 54.824] | 41773 | 46163 | 2885 | [312KB, 521KB] | 2MB | | 20 | 102647.46
67 | -> Vector Subquery Scan on t | [33.516, 63.187] | 60841 | 46163 | | [120KB, 120KB] | 1MB | | 20 | 102409.45
68 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [33.168, 62.607] | 60841 | 46163 | | [3MB, 3MB] | 1MB | | 28 | 102380.60
69 | -> Vector Streaming(type: REDISTRIBUTE) | [8.744, 11.961] | 17 | 11 | 1 | [264KB, 360KB] | 2MB | [0,225] | 82 | 29790.78
70 | -> Vector Nest Loop (71,75) | [3.220, 9.975] | 17 | 11 | | [512KB, 512KB] | 1MB | | 82 | 29790.69
71 | -> Vector Sonic Hash Aggregate | [0.343, 7.110] | 1648 | 176 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4184.62
72 | -> Vector Result | [0.124, 6.716] | 1712 | 79 | | [120KB, 120KB] | 1MB | | 10 | 4184.48
73 | -> Vector Streaming(type: BROADCAST) | [0.076, 6.629] | 1712 | 176 | | [280KB, 280KB] | 2MB | | 10 | 4184.48
74 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.416, 0.805] | 107 | 11 | | [1MB, 1MB] | 1MB | | 10 | 4181.96
75 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [1.618, 3.405] | 17 | 1 | | [6MB, 6MB] | 16MB | | 82 | 2327.81
76 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.007, 3.013] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95348.65
77 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95348.65
78 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95348.65
79 | -> Vector Streaming(type: REDISTRIBUTE) | [2.080, 8.826] | 83288 | 82943 | 5017 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4589.66
80 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.444, 0.794] | 83288 | 82943 | | [1MB, 1MB] | 1MB | | 10 | 4161.98
81 | -> Vector Subquery Scan on subquery | [0.633, 1.342] | 3 | 15 | 1 | [120KB, 120KB] | 1MB | | 161 | 229009.23
82 | -> Vector Sonic Hash Aggregate | [0.628, 1.337] | 3 | 15 | | [1MB, 1MB] | 16MB | [139,145] | 101 | 229009.22
83 | -> Vector Hash Aggregate | [0.256, 0.453] | 16 | 15 | | [584KB, 584KB] | 16MB | [194,200] | 93 | 229009.19
84 | -> Vector Streaming(type: REDISTRIBUTE) | [0.131, 0.215] | 96 | 15 | | [376KB, 376KB] | 2MB | | 85 | 229009.15
85 | -> Vector Hash Left Join (86, 103) | [59.021, 71.446] | 96 | 15 | | [849KB, 858KB] | 16MB | | 85 | 229009.04
86 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [58.182, 65.750] | 76 | 11 | 1 | [376KB, 376KB] | 2MB | | 95 | 227794.32
87 | -> Vector Hash Left Join (88, 100) | [64.757, 70.757] | 76 | 11 | | [580KB, 580KB] | 16MB | | 95 | 227794.23
88 | -> Vector Streaming(type: REDISTRIBUTE) | [64.240, 70.124] | 76 | 11 | 1 | [264KB, 408KB] | 2MB | | 112 | 132445.56
89 | -> Vector Hash Right Join (90, 93) | [9.747, 67.725] | 76 | 11 | | [580KB, 588KB] | 16MB | | 112 | 132445.48
90 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [54.222, 54.558] | 41773 | 46163 | 2885 | [553KB, 569KB] | 2MB | | 30 | 102647.46
91 | -> Vector Subquery Scan on t | [32.736, 63.935] | 60841 | 46163 | | [120KB, 120KB] | 1MB | | 30 | 102409.45
92 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [32.416, 63.327] | 60841 | 46163 | | [3MB, 3MB] | 1MB | | 38 | 102380.60
93 | -> Vector Streaming(type: REDISTRIBUTE) | [8.884, 12.646] | 17 | 11 | 1 | [264KB, 360KB] | 2MB | [0,225] | 82 | 29790.78
94 | -> Vector Nest Loop (95,99) | [3.479, 9.736] | 17 | 11 | | [512KB, 512KB] | 1MB | | 82 | 29790.69
95 | -> Vector Sonic Hash Aggregate | [0.446, 7.045] | 1648 | 176 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4184.62
96 | -> Vector Result | [0.126, 6.648] | 1712 | 79 | | [120KB, 120KB] | 1MB | | 10 | 4184.48
97 | -> Vector Streaming(type: BROADCAST) | [0.086, 6.563] | 1712 | 176 | | [280KB, 280KB] | 2MB | | 10 | 4184.48
98 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.454, 0.763] | 107 | 11 | | [1MB, 1MB] | 1MB | | 10 | 4181.96
99 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [1.577, 3.437] | 17 | 1 | | [6MB, 6MB] | 16MB | | 82 | 2327.81
100 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.007, 3.675] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 10 | 95348.65
101 | -> Vector Adapter | [0.003, 0.003] | 0 | 1 | | [24KB, 24KB] | 1MB | | 0 | 95348.65
102 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95348.65
103 | -> Vector Streaming(type: REDISTRIBUTE) | [0.273, 9.185] | 83288 | 82943 | 5017 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4589.66
104 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.478, 0.797] | 83288 | 82943 | | [1MB, 1MB] | 1MB | | 10 | 4161.98
105 | -> Vector Subquery Scan on subquery | [63.241, 67.908] | 3 | 15 | 1 | [120KB, 120KB] | 1MB | | 161 | 229009.20
106 | -> Vector Sonic Hash Aggregate | [63.237, 67.905] | 3 | 15 | | [1MB, 1MB] | 16MB | [0,145] | 83 | 229009.19
107 | -> Vector Sonic Hash Aggregate | [62.381, 67.308] | 3 | 15 | | [1MB, 1MB] | 16MB | [0,146] | 75 | 229009.17
108 | -> Vector Streaming(type: REDISTRIBUTE) | [61.603, 66.727] | 96 | 15 | | [264KB, 312KB] | 2MB | | 67 | 229009.14
109 | -> Vector Hash Left Join (110, 127) | [67.518, 71.541] | 96 | 15 | | [585KB, 594KB] | 16MB | | 67 | 229009.04
110 | -> Vector Streaming(type: PART REDISTRIBUTE PART LOCAL) | [61.328, 66.517] | 76 | 11 | 1 | [328KB, 328KB] | 2MB | | 77 | 227794.32
111 | -> Vector Hash Left Join (112, 124) | [66.230, 70.733] | 76 | 11 | | [580KB, 580KB] | 16MB | | 77 | 227794.23
112 | -> Vector Streaming(type: REDISTRIBUTE) | [65.778, 70.570] | 76 | 11 | 1 | [264KB, 360KB] | 2MB | | 84 | 132445.56
113 | -> Vector Hash Right Join (114, 117) | [13.941, 67.871] | 76 | 11 | | [580KB, 588KB] | 16MB | | 84 | 132445.48
114 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [52.534, 52.783] | 41773 | 46163 | 2885 | [312KB, 521KB] | 2MB | | 20 | 102647.46
115 | -> Vector Subquery Scan on t | [32.774, 64.044] | 60841 | 46163 | | [120KB, 120KB] | 1MB | | 20 | 102409.45
116 | -> CStore Scan on dmsalesw.dm_sale_mcr_pc_activity_f t | [32.444, 63.455] | 60841 | 46163 | | [3MB, 3MB] | 1MB | | 28 | 102380.60
117 | -> Vector Streaming(type: REDISTRIBUTE) | [10.768, 14.569] | 17 | 11 | 1 | [264KB, 344KB] | 2MB | [0,198] | 74 | 29790.78
118 | -> Vector Nest Loop (119,123) | [3.489, 13.717] | 17 | 11 | | [512KB, 512KB] | 1MB | | 74 | 29790.69
119 | -> Vector Sonic Hash Aggregate | [0.449, 9.264] | 1648 | 176 | | [676KB, 676KB] | 16MB | [52,52] | 10 | 4184.62
120 | -> Vector Result | [0.153, 9.014] | 1712 | 79 | | [120KB, 120KB] | 1MB | | 10 | 4184.48
121 | -> Vector Streaming(type: BROADCAST) | [0.085, 8.917] | 1712 | 176 | | [280KB, 280KB] | 2MB | | 10 | 4184.48
122 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.458, 0.821] | 107 | 11 | | [1MB, 1MB] | 1MB | | 10 | 4181.96
123 | -> CStore Index Scan using dm_mcr_contact_period_d_pk on dmsalesw.dm_mcr_contact_period_d c | [1.662, 2.979] | 17 | 1 | | [6MB, 6MB] | 16MB | | 74 | 2327.81
124 | -> Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | [0.008, 1.859] | 0 | 1 | 1 | [72KB, 72KB] | 2MB | | 20 | 95348.65
125 | -> Vector Adapter | [0.002, 0.002] | 0 | 1 | | [56KB, 56KB] | 1MB | | 0 | 95348.65
126 | -> Result | [0.001, 0.001] | 0 | 1 | | [8KB, 8KB] | 1MB | | 0 | 95348.65
127 | -> Vector Streaming(type: REDISTRIBUTE) | [0.540, 9.530] | 83288 | 82943 | 5017 | [280KB, 280KB] | 2MB | [29,29] | 10 | 4589.66
128 | -> CStore Scan on dmsalesw.dm_mcr_object_tag_f f | [0.475, 0.793] | 83288 | 82943 | | [1MB, 1MB] | 1MB | | 10 | 4161.98
129 | -> Vector Subquery Scan on q | [2.477, 7.082] | 160 | 464 | 29 | [120KB, 120KB] | 1MB | [91,94] | 55 | 9331.31
130 | -> Vector Sonic Hash Aggregate | [2.472, 7.071] | 160 | 464 | | [948KB, 948KB] | 16MB | [63,66] | 62 | 9331.02
131 | -> Vector Streaming(type: REDISTRIBUTE) | [2.047, 6.804] | 575 | 471 | | [312KB, 312KB] | 2MB | | 30 | 9330.51
132 | -> CStore Scan on dmsalesw.dm_mcr_action_plan_f t | [1.011, 2.046] | 575 | 471 | | [3MB, 3MB] | 1MB | | 30 | 9329.51 可以看到，表扫描的耗时减少了非常多，从22s减少到了3ms。
5、【调优总结】
在本案例中，三个对同一个表的CStore Scan算子成为了整个SQL的瓶颈。通过分析SQL发现，表扫描慢是因为三处都对表数据进行了类型转换，导致计算资源消耗大。通过把数据类型转换转移到等号的右边，我们消除了对表数据的类型转换，从而使SQL耗时得到了很大的提升。整体从8s减少到了300ms。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）