周报8_YMK

文章讨论了微软DeepSpeed框架下的ZeRO++,一种针对大模型训练的通信优化方法,通过权重量化、分层存储和梯度通信优化,显著减少GPU间通信,提高训练效率,尤其在低带宽环境中表现优异。

周报8

这周主要看了HPC-AI的文档

另一个任务是在做计网课程的presentation

看了一篇大模型训练优化的模型,ZeRO++: Extremely Efficient Collective Communication for Giant Model Training(https://arxiv.org/pdf/2306.10209.pdf)

是微软DeepSpeed框架下的,它的上一个工作是2022年发在International Conference on High Performance Computing, Networking, Storage and Analysis (SC)上的,ZeRO: Memory Optimizations Toward Training Trillion Parameter Models

ZeRO++主要是为了降低数据并行时,GPU之间的通讯量的

这里简单介绍一下ZeRO,ZeRO 将模型权重、梯度以及优化器状态(比如动量,方差)分别切分到各GPU上,从而可在有限的显存上训练更大的模型。

这样一来,模型前向计算和反向计算都需要提前聚合当前层对应的全量参数,这个聚合的过程是通过调用通信原语 All-Gather 来完成的。

之后便需要对计算好的梯度进行平均,把平均后的梯度值传播到各 GPU 上,用于各 GPU 更新自己负责的那一部分模型权重,这个平均以及传播的过程是通过调用通信原语 Reduce-Scatter 来完成的。

至此完成一步迭代,也就是说ZeRO是在用通信换显存,在有限的显存中训练更大的模型。分析这个过程可以发现,ZeRO 相比于普通的数据并行,后者只需要对最后计算出的梯度做一次 All-Reduce 通信,而前者需要两次 All-Gather 通信 + 一次 Reduce-Scatter 通信,通信量以及通信频率都大幅增长。

如果机器集群节点间的网络带宽再拉跨一些,那么 ZeRO 的训练效率简直不堪入目。这也是目前很多大模型都是基于张量并行和流水并行对模型进行精细切分,让一些频繁通信的操作(张量并行)尽量限制在节点内部的原因,把通信压力小的操作放在节点间完成,比如流水并行。

所以ZeRO作为数据并行的方法,也需要降低自己的通讯成本,进而提升训练效率,具体优化策略也就是接下来将要介绍的 ZeRO++。

先说结论:ZeRO++ 相比 ZeRO 将总通信量减少了 4 倍,而不会影响模型质量。

  1. 每个 GPU 上 batch size 较小时:无论是在数千个 GPU 上预训练大型模型,还是在数百个甚至数十个 GPU 上对其进行微调,ZeRO++ 提供比 ZeRO 高 2.2 倍的吞吐量,直接减少训练时间和成本。
  2. 低带宽计算集群: ZeRO++ 使低带宽集群能够实现与带宽高 4 倍的高端集群类似的吞吐量。因此,ZeRO++ 可以跨更广泛的集群进行高效的大型模型训练。

瓶颈分析:

在这里插入图片描述

如前面提到的,ZeRO的通信开销主要由三部分组成:

  • 假设模型大小为 M。在前向传播过程中,ZeRO 执行全收集 / 广播 (all-gather/broadcast) 操作以在需要之时为每个模型层收集参数(总共大小为 M)。
  • 在向后传递中,ZeRO 对每一层的参数采用类似的通信模式来计算其局部梯度(总大小为 M)。
  • 此外,ZeRO 在对每个局部梯度计算完毕后会立刻使用 reduce 或 reduce-scatter 通信进行平均和分割储存(总大小为 M)。

因此,ZeRO 总共有 3M 的通信量,平均分布在两个全收集 / 广播 (all-gather/broadcast) 和一个减少分散 / 减少 (reduce-scatter/reduce) 操作中。

通信过程中的权重量化 (qwZ)

首先,为了减少 all-gather 期间的参数通信量,在 All-Gather 通信之前,首先把 FP16(两字节) 权重量化成 INT8(单字节),这样一来通信数据量就下降了一半,Al-Gather 通信之后,再通过反量化将 INT8 反量化成 FP16。

然而,简单地对权重进行量化会降低模型训练的准确性。为了保持良好的模型训练精度,我们采用分区量化Blocked Quantization,即对模型参数的每个子集进行独立量化。

在这里插入图片描述

如下图(a)是两种量化策略的对比,Blocked Quantization 相比于 Baseline 具有更小的量化误差;下图(b)说明 Block 切得越多,欧式距离越小,量化损失也就越小,但是也会带来额外的开销(scale 和 zero);

在这里插入图片描述

ZeRO 模型权重的分层分割存储 (hpZ)

第二,减少反向传递期间全收集 (all-gather) 权重的通信开销。由于 ZeRO 把整个模型权重切分到所有的 GPU 上,所以反向计算梯度时需要所有 GPU 参与通信,把权重分片聚拢起来,但是节点间的网络带宽远远小于节点内部,导致节点间通信成为瓶颈。

为了缓解这个问题,ZeRO++ 采用分层切片的策略尽量减少反向计算时的跨节点通信。简单来说,与在ZeRO中将整个模型权重分散在所有机器上不同,ZeRO++在每台机器内维护一个完整的模型副本。(用GPU显存换取通信效率)

具体来讲,已知前向计算时会把所有权重 All-Gather 起来,之后便对权重进行切片,切成多少片可以根据集群配置进行调节,一般情况下会把权重切片尽量限制在单个节点内部,也就是一个节点有多少张卡,就切成多少片,因为每个节点都拥有完整的权重,在反向计算梯度时只需要在节点内部执行 All-Gather 通信,完全避免了跨节点的通信。

在这里插入图片描述

在这里插入图片描述

ZeRO 通信过程中梯度量化 (qgZ)

ZeRO 在反向计算完成之后需要一次 Reduce-Scatter 通信,如果直接将量化策略应用到 Reduce-Scatter 通信原语,会造引发一系列的量化和反量化(量化和反量化的次数为所有 GPU 的个数),这不可避免地会引入巨大的量化误差,如下图左所示:

在这里插入图片描述

为了减少量化和反量化的次数(Q+D),可如上图右所示,首先对全部梯度量化,然后所有 GPU 进行一次 All-to-All 通信,最后执行反量化操作。这个过程只需一次量化和反量化操作,因此也被称作 1-hop all-to all。但是肉眼可见的这个1-hop all-to all的通信开销太大了。

在这里插入图片描述

从上图可以看出,基于 Reduce-Scatter 的 ZeRO3 跨机通信量为 M,而基于 1-hop all-to-all 的算法跨机通信量为 N * M / Z(其中 Z 为压缩比率,比如 FP16 量化为 INT8,也就是从 2 个字节压缩成 1 个字节,因此压缩比率为 2;由于每张卡都要发送压缩后的数据,所以需要对压缩后的数据乘上 N)。

相比于 Reduce-Scatter,1-hop all-to-all 的跨机通信总量大幅增加,因此需要进一步优化以减少跨机通信数据量。ZeRO++ 提出基于分层策略的 2-hop all-to-all 算法:

具体来讲,4步:

Step1: Tensor Slice Reordering(张量切片重排),重排的原因稍后解释,重排后进行量化(Quantizaiton),然后在节点内执行 All-to-All 通信:

在这里插入图片描述

Step2:在各个节点内部首先执行反量化(Dequantization),然后把反量化的结果相加(Reducetion),减小精度损失:

在这里插入图片描述

Step3:执行 Reduction 之后,再次对张量进行量化(Quantization),然后对量化后的结果执行第二次 All-to-All 通信,只不过这一次是节点间(以下图为例:Machine 0 的 G2 和 Machine 1 的 G2,Machine 0 的 G3 和 Machine 1 的 G3):

在这里插入图片描述

Step4:节点间 All-to-All 通信之后,首先进行反量化(Dequantization),然后执行 Reduction 操作,这时每张卡上都拿到了权重(Primary Parameters)对应的、平均后的梯度:

在这里插入图片描述

总体流程是这样

在这里插入图片描述

那么为什么要重排呢?可以看这张图,左边是(未重排)右边是(重排)

如果没有切片重排,两次 ALL-to-ALL 通信之后,每张卡上的张量切片无法与正确的切片顺序对齐。

在这里插入图片描述

接下来我们分析一下2-hop all-to-all ,节点间的通信开销

在这里插入图片描述

第一次 All-to-All 通信之后,总参数量从 M/Z 降到 M / (Z * N),其中 N 为每个节点的 GPU 个数;

第二次 All-to-All 通信每张卡发送的数据量为 M / ( Z * N),那么每台机器的跨机通信量就是 (N * M) / (Z * N) ,也就是 M / Z(FP16 -> INT4,所以是 0.25M)。

至此,已经完整介绍前向通信优化(qwZ),反向通信优化(hpZ),以及梯度通信优化(qgZ)。节点间通信量如下图:

在这里插入图片描述

相比于 ZeRO,ZeRO++在前向时量化权重节省了一半的跨机通信量(PF16 -> INT8),后向时由于权重都已经存在本地节点,所以跨机通信量为 0,最后的梯度同步可减少 3/4 跨机通信量。

-- explain(analyze,buffers,verbose) EXPLAIN ANALYZE WITH wk1 AS ( SELECT pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code FROM m_pattern AS pm INNER JOIN m_reading_number_by_pattern AS ym ON pm.pattern_type = ym.pattern_type AND pm.pattern_code = ym.pattern_code AND ym.version = '1109_036' INNER JOIN m_staff_by_information_order AS jtm ON pm.original_store_code = jtm.original_store_code AND ym.host_cycle_code = jtm.host_cycle_code AND ym.store_cycle_code = jtm.store_cycle_code AND ym.information_category_code = jtm.information_category_code LEFT JOIN m_reading_number_by_store AS ymk ON pm.original_store_code = ymk.original_store_code AND ym.host_cycle_code = ymk.host_cycle_code AND ym.store_cycle_code = ymk.store_cycle_code AND ym.information_category_code = ymk.information_category_code WHERE pm.version = '10010001' AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date ) OR ymk.information_category_code IS NULL ) AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date GROUP BY pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code ), WK2 AS ( SELECT pm.original_store_code, ym.group_number, jtm.setting_date, jtm.host_cycle_code, jtm.store_cycle_code FROM m_pattern AS pm INNER JOIN m_reading_number_by_pattern AS ym ON pm.pattern_type = ym.pattern_type AND pm.pattern_code = ym.pattern_code AND ym.version = '1109_036' INNER JOIN m_staff_by_information_order AS jtm ON pm.original_store_code = jtm.original_store_code AND ym.host_cycle_code = jtm.host_cycle_code AND ym.store_cycle_code = jtm.store_cycle_code AND ym.information_category_code = jtm.information_category_code LEFT JOIN m_reading_number_by_store AS ymk ON pm.original_store_code = ymk.original_store_code AND ym.host_cycle_code = ymk.host_cycle_code AND ym.store_cycle_code = ymk.store_cycle_code AND ym.information_category_code = ymk.information_category_code WHERE pm.version = '10010001' AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date ) OR ymk.information_category_code IS NULL ) AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date GROUP BY pm.original_store_code, ym.group_number, jtm.setting_date, jtm.host_cycle_code, jtm.store_cycle_code ), wk3 AS ( SELECT pm.original_store_code, ym.group_number, ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code, wk2.setting_date FROM m_pattern AS pm INNER JOIN m_reading_number_by_pattern AS ym ON pm.pattern_type = ym.pattern_type AND pm.pattern_code = ym.pattern_code AND ym.version = '1109_036' INNER JOIN wk2 ON pm.original_store_code = wk2.original_store_code AND ym.group_number = wk2.group_number AND ym.host_cycle_code = wk2.host_cycle_code AND ym.store_cycle_code = wk2.store_cycle_code LEFT JOIN m_reading_number_by_store AS ymk ON pm.original_store_code = ymk.original_store_code AND ym.host_cycle_code = ymk.host_cycle_code AND ym.store_cycle_code = ymk.store_cycle_code AND ym.information_category_code = ymk.information_category_code WHERE pm.version = '10010001' AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date ) OR ymk.information_category_code IS NULL ) AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date GROUP BY pm.original_store_code, ym.group_number, ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code, wk2.setting_date ) SELECT wk1.original_store_code, wk3.host_cycle_code, wk3.store_cycle_code, wk3.information_category_code, wk1.staff_code, wk3.setting_date FROM wk1 INNER JOIN wk3 ON wk1.original_store_code = wk3.original_store_code AND wk1.group_number = wk3.group_number AND wk1.host_cycle_code = wk3.host_cycle_code AND wk1.store_cycle_code = wk3.store_cycle_code ; 一直显示连接超时
08-19
现在我们有了执行计划,你分析一下哪慢:QUERY PLAN Limit (cost=9550.97..9800.70 rows=1 width=38) (actual time=9795.278..9795.400 rows=1 loops=1) -> Nested Loop (cost=9550.97..9800.70 rows=1 width=38) (actual time=9795.275..9795.396 rows=1 loops=1) Join Filter: (((pm.original_store_code)::text = (pm_1.original_store_code)::text) AND ((ym.group_number)::text = (ym_1.group_number)::text) AND ((ym.host_cycle_code)::text = (ym_1.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (ym_1.store_cycle_code)::text)) -> Group (cost=4704.81..4841.78 rows=1 width=29) (actual time=3247.129..3247.199 rows=1 loops=1) Group Key: pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code -> Gather Merge (cost=4704.81..4841.77 rows=1 width=29) (actual time=3247.128..3247.196 rows=1 loops=1) Workers Planned: 1 Workers Launched: 1 -> Incremental Sort (cost=3704.80..3841.65 rows=2 width=29) (actual time=1623.114..1623.117 rows=1 loops=2) Sort Key: pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code Presorted Key: pm.original_store_code Full-sort Groups: 1 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 45kB Peak Memory: 45kB Worker 0: Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB -> Nested Loop (cost=3568.02..3841.56 rows=1 width=29) (actual time=1622.343..1622.884 rows=131 loops=2) -> Merge Left Join (cost=3567.74..3628.53 rows=522 width=21) (actual time=1621.903..1622.179 rows=228 loops=2) Merge Cond: (((pm.original_store_code)::text = (ymk.original_store_code)::text) AND ((ym.host_cycle_code)::text = (ymk.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (ymk.store_cycle_code)::text) AND ((ym.information_category_code)::text = (ymk.information_category_code)::text)) Filter: ((((ymk.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk.apply_start_date) AND ('2023-11-09'::date <= ymk.apply_end_date)) OR (ymk.information_category_code IS NULL)) -> Sort (cost=3541.22..3552.58 rows=4542 width=21) (actual time=1621.878..1622.115 rows=228 loops=2) Sort Key: pm.original_store_code, ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code Sort Method: external merge Disk: 13800kB Worker 0: Sort Method: quicksort Memory: 25kB -> Merge Join (cost=3008.64..3265.32 rows=4542 width=21) (actual time=29.380..351.880 rows=226995 loops=2) Merge Cond: (((ym.pattern_type)::text = (pm.pattern_type)::text) AND ((ym.pattern_code)::text = (pm.pattern_code)::text)) -> Sort (cost=2045.25..2090.67 rows=18168 width=21) (actual time=26.306..27.871 rows=15434 loops=2) Sort Key: ym.pattern_type, ym.pattern_code Sort Method: quicksort Memory: 3181kB Worker 0: Sort Method: quicksort Memory: 25kB -> Parallel Seq Scan on m_reading_number_by_pattern_1109_036 ym (cost=0.00..759.94 rows=18168 width=21) (actual time=0.018..5.019 rows=15443 loops=2) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text)) -> Sort (cost=963.39..988.39 rows=10000 width=14) (actual time=6.142..27.592 rows=455991 loops=1) Sort Key: pm.pattern_type, pm.pattern_code Sort Method: quicksort Memory: 853kB -> Seq Scan on m_pattern_10010001 pm (cost=0.00..299.00 rows=10000 width=14) (actual time=0.034..2.930 rows=10000 loops=1) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text)) -> Sort (cost=26.52..27.32 rows=320 width=102) (actual time=0.041..0.042 rows=1 loops=1) Sort Key: ymk.original_store_code, ymk.host_cycle_code, ymk.store_cycle_code, ymk.information_category_code Sort Method: quicksort Memory: 25kB -> Seq Scan on m_reading_number_by_store ymk (cost=0.00..13.20 rows=320 width=102) (actual time=0.025..0.025 rows=1 loops=1) -> Index Only Scan using m_staff_by_information_order_pkey on m_staff_by_information_order jtm (cost=0.29..0.40 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=455) Index Cond: ((original_store_code = (pm.original_store_code)::text) AND (host_cycle_code = (ym.host_cycle_code)::text) AND (store_cycle_code = (ym.store_cycle_code)::text) AND (information_category_code = (ym.information_category_code)::text)) Heap Fetches: 0 -> Group (cost=4846.16..4958.88 rows=1 width=29) (actual time=6548.141..6548.193 rows=1 loops=1) Group Key: pm_1.original_store_code, ym_1.group_number, ym_1.host_cycle_code, ym_1.store_cycle_code, ym_1.information_category_code, jtm_1.setting_date -> Incremental Sort (cost=4846.16..4958.85 rows=2 width=29) (actual time=6548.139..6548.191 rows=1 loops=1) Sort Key: pm_1.original_store_code, ym_1.group_number, ym_1.host_cycle_code, ym_1.store_cycle_code, ym_1.information_category_code, jtm_1.setting_date Presorted Key: pm_1.original_store_code, ym_1.group_number Full-sort Groups: 1 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB -> Nested Loop Left Join (cost=4733.53..4958.76 rows=1 width=29) (actual time=6546.392..6547.850 rows=97 loops=1) Filter: ((((ymk_1.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk_1.apply_start_date) AND ('2023-11-09'::date <= ymk_1.apply_end_date)) OR (ymk_1.information_category_code IS NULL)) -> Nested Loop (cost=4733.38..4958.55 rows=1 width=29) (actual time=6546.377..6547.735 rows=97 loops=1) -> Group (cost=4732.69..4897.53 rows=1 width=23) (actual time=6546.292..6546.369 rows=24 loops=1) Group Key: pm_2.original_store_code, ym_2.group_number, jtm_1.setting_date, jtm_1.host_cycle_code, jtm_1.store_cycle_code -> Gather Merge (cost=4732.69..4897.52 rows=1 width=23) (actual time=6546.290..6546.349 rows=64 loops=1) Workers Planned: 1 Workers Launched: 1 -> Incremental Sort (cost=3732.68..3897.40 rows=2 width=23) (actual time=3271.945..3271.949 rows=32 loops=2) Sort Key: pm_2.original_store_code, ym_2.group_number, jtm_1.setting_date, jtm_1.host_cycle_code, jtm_1.store_cycle_code Presorted Key: pm_2.original_store_code Full-sort Groups: 1 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 45kB Peak Memory: 45kB Worker 0: Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB -> Nested Loop (cost=3568.02..3897.31 rows=1 width=23) (actual time=3271.191..3271.688 rows=131 loops=2) -> Merge Left Join (cost=3567.74..3628.53 rows=522 width=21) (actual time=3270.971..3271.034 rows=228 loops=2) Merge Cond: (((pm_2.original_store_code)::text = (ymk_2.original_store_code)::text) AND ((ym_2.host_cycle_code)::text = (ymk_2.host_cycle_code)::text) AND ((ym_2.store_cycle_code)::text = (ymk_2.store_cycle_code)::text) AND ((ym_2.information_category_code)::text = (ymk_2.information_category_code)::text)) Filter: ((((ymk_2.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk_2.apply_start_date) AND ('2023-11-09'::date <= ymk_2.apply_end_date)) OR (ymk_2.information_category_code IS NULL)) -> Sort (cost=3541.22..3552.58 rows=4542 width=21) (actual time=3270.944..3270.968 rows=228 loops=2) Sort Key: pm_2.original_store_code, ym_2.host_cycle_code, ym_2.store_cycle_code, ym_2.information_category_code Sort Method: external sort Disk: 15584kB Worker 0: Sort Method: quicksort Memory: 25kB -> Merge Join (cost=3008.64..3265.32 rows=4542 width=21) (actual time=118.146..277.069 rows=226995 loops=2) Merge Cond: (((ym_2.pattern_type)::text = (pm_2.pattern_type)::text) AND ((ym_2.pattern_code)::text = (pm_2.pattern_code)::text)) -> Sort (cost=2045.25..2090.67 rows=18168 width=21) (actual time=115.483..212.185 rows=15434 loops=2) Sort Key: ym_2.pattern_type, ym_2.pattern_code Sort Method: quicksort Memory: 3181kB Worker 0: Sort Method: quicksort Memory: 25kB -> Parallel Seq Scan on m_reading_number_by_pattern_1109_036 ym_2 (cost=0.00..759.94 rows=18168 width=21) (actual time=0.004..3.696 rows=15443 loops=2) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text)) -> Sort (cost=963.39..988.39 rows=10000 width=14) (actual time=5.316..32.792 rows=455991 loops=1) Sort Key: pm_2.pattern_type, pm_2.pattern_code Sort Method: quicksort Memory: 853kB -> Seq Scan on m_pattern_10010001 pm_2 (cost=0.00..299.00 rows=10000 width=14) (actual time=0.027..2.123 rows=10000 loops=1) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text)) -> Sort (cost=26.52..27.32 rows=320 width=102) (actual time=0.045..0.046 rows=1 loops=1) Sort Key: ymk_2.original_store_code, ymk_2.host_cycle_code, ymk_2.store_cycle_code, ymk_2.information_category_code Sort Method: quicksort Memory: 25kB -> Seq Scan on m_reading_number_by_store ymk_2 (cost=0.00..13.20 rows=320 width=102) (actual time=0.024..0.025 rows=1 loops=1) -> Index Scan using m_staff_by_information_order_pkey on m_staff_by_information_order jtm_1 (cost=0.29..0.50 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=455) Index Cond: (((original_store_code)::text = (pm_2.original_store_code)::text) AND ((host_cycle_code)::text = (ym_2.host_cycle_code)::text) AND ((store_cycle_code)::text = (ym_2.store_cycle_code)::text) AND ((information_category_code)::text = (ym_2.information_category_code)::text)) -> Nested Loop (cost=0.70..60.96 rows=5 width=21) (actual time=0.034..0.056 rows=4 loops=24) -> Index Scan using m_pattern_10010001_pkey on m_pattern_10010001 pm_1 (cost=0.29..15.41 rows=5 width=14) (actual time=0.004..0.005 rows=5 loops=24) Index Cond: (((version)::text = '10010001'::text) AND ((original_store_code)::text = (pm_2.original_store_code)::text) AND (apply_start_date <= '2023-11-09'::date)) Filter: ('2023-11-09'::date <= apply_end_date) -> Index Scan using m_reading_number_by_pattern_1109_036_pkey on m_reading_number_by_pattern_1109_036 ym_1 (cost=0.41..9.10 rows=1 width=21) (actual time=0.009..0.010 rows=1 loops=116) Index Cond: (((pattern_type)::text = (pm_1.pattern_type)::text) AND ((pattern_code)::text = (pm_1.pattern_code)::text) AND (apply_start_date <= '2023-11-09'::date) AND ((host_cycle_code)::text = (jtm_1.host_cycle_code)::text) AND ((store_cycle_code)::text = (jtm_1.store_cycle_code)::text) AND ((group_number)::text = (ym_2.group_number)::text) AND ((version)::text = '1109_036'::text)) Filter: ('2023-11-09'::date <= apply_end_date) -> Index Scan using m_reading_number_by_store_pkey on m_reading_number_by_store ymk_1 (cost=0.15..0.19 rows=1 width=102) (actual time=0.001..0.001 rows=0 loops=97) Index Cond: (((original_store_code)::text = (pm_1.original_store_code)::text) AND ((host_cycle_code)::text = (ym_1.host_cycle_code)::text) AND ((store_cycle_code)::text = (ym_1.store_cycle_code)::text) AND ((information_category_code)::text = (ym_1.information_category_code)::text)) Planning Time: 6.857 ms Execution Time: 9981.633 ms SQL:-- explain(analyze,buffers,verbose) EXPLAIN ANALYZE WITH wk1 AS ( SELECT pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code FROM m_pattern AS pm INNER JOIN m_reading_number_by_pattern AS ym ON pm.pattern_type = ym.pattern_type AND pm.pattern_code = ym.pattern_code AND ym.version = '1109_036' INNER JOIN m_staff_by_information_order AS jtm ON pm.original_store_code = jtm.original_store_code AND ym.host_cycle_code = jtm.host_cycle_code AND ym.store_cycle_code = jtm.store_cycle_code AND ym.information_category_code = jtm.information_category_code LEFT JOIN m_reading_number_by_store AS ymk ON pm.original_store_code = ymk.original_store_code AND ym.host_cycle_code = ymk.host_cycle_code AND ym.store_cycle_code = ymk.store_cycle_code AND ym.information_category_code = ymk.information_category_code WHERE pm.version = '10010001' AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date ) OR ymk.information_category_code IS NULL ) AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date GROUP BY pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code ), WK2 AS ( SELECT pm.original_store_code, ym.group_number, jtm.setting_date, jtm.host_cycle_code, jtm.store_cycle_code FROM m_pattern AS pm INNER JOIN m_reading_number_by_pattern AS ym ON pm.pattern_type = ym.pattern_type AND pm.pattern_code = ym.pattern_code AND ym.version = '1109_036' INNER JOIN m_staff_by_information_order AS jtm ON pm.original_store_code = jtm.original_store_code AND ym.host_cycle_code = jtm.host_cycle_code AND ym.store_cycle_code = jtm.store_cycle_code AND ym.information_category_code = jtm.information_category_code LEFT JOIN m_reading_number_by_store AS ymk ON pm.original_store_code = ymk.original_store_code AND ym.host_cycle_code = ymk.host_cycle_code AND ym.store_cycle_code = ymk.store_cycle_code AND ym.information_category_code = ymk.information_category_code WHERE pm.version = '10010001' AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date ) OR ymk.information_category_code IS NULL ) AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date GROUP BY pm.original_store_code, ym.group_number, jtm.setting_date, jtm.host_cycle_code, jtm.store_cycle_code ), wk3 AS ( SELECT pm.original_store_code, ym.group_number, ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code, wk2.setting_date FROM m_pattern AS pm INNER JOIN m_reading_number_by_pattern AS ym ON pm.pattern_type = ym.pattern_type AND pm.pattern_code = ym.pattern_code AND ym.version = '1109_036' INNER JOIN wk2 ON pm.original_store_code = wk2.original_store_code AND ym.group_number = wk2.group_number AND ym.host_cycle_code = wk2.host_cycle_code AND ym.store_cycle_code = wk2.store_cycle_code LEFT JOIN m_reading_number_by_store AS ymk ON pm.original_store_code = ymk.original_store_code AND ym.host_cycle_code = ymk.host_cycle_code AND ym.store_cycle_code = ymk.store_cycle_code AND ym.information_category_code = ymk.information_category_code WHERE pm.version = '10010001' AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date ) OR ymk.information_category_code IS NULL ) AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date GROUP BY pm.original_store_code, ym.group_number, ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code, wk2.setting_date ) SELECT wk1.original_store_code, wk3.host_cycle_code, wk3.store_cycle_code, wk3.information_category_code, wk1.staff_code, wk3.setting_date FROM wk1 INNER JOIN wk3 ON wk1.original_store_code = wk3.original_store_code AND wk1.group_number = wk3.group_number AND wk1.host_cycle_code = wk3.host_cycle_code AND wk1.store_cycle_code = wk3.store_cycle_code limit 1 ;
08-19
新的执行计划看看哪慢:QUERY PLAN Merge Join (cost=13166.94..13167.03 rows=1 width=38) (actual time=7872.404..9014.874 rows=32768 loops=1) Merge Cond: (((pm.original_store_code)::text = (pm_1.original_store_code)::text) AND ((ym.group_number)::text = (ym_1.group_number)::text)) Join Filter: (((ym.host_cycle_code)::text = (ym_1.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (ym_1.store_cycle_code)::text)) Rows Removed by Join Filter: 122984 -> Group (cost=5669.68..5669.69 rows=1 width=29) (actual time=3346.894..3881.617 rows=5004 loops=1) Group Key: pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code -> Sort (cost=5669.68..5669.68 rows=1 width=29) (actual time=3346.891..3877.207 rows=16777 loops=1) Sort Key: pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code Sort Method: quicksort Memory: 2079kB -> Hash Left Join (cost=5354.20..5669.67 rows=1 width=29) (actual time=3018.486..3855.427 rows=16777 loops=1) Hash Cond: (((pm.original_store_code)::text = (ymk.original_store_code)::text) AND ((ym.host_cycle_code)::text = (ymk.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (ymk.store_cycle_code)::text) AND ((ym.information_category_code)::text = (ymk.information_category_code)::text)) Filter: ((((ymk.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk.apply_start_date) AND ('2023-11-09'::date <= ymk.apply_end_date)) OR (ymk.information_category_code IS NULL)) -> Gather (cost=5334.60..5649.88 rows=1 width=34) (actual time=3018.434..3850.134 rows=16777 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Hash Join (cost=4334.60..4649.78 rows=1 width=34) (actual time=2858.224..3060.741 rows=8389 loops=2) Hash Cond: (((pm.pattern_type)::text = (ym.pattern_type)::text) AND ((pm.pattern_code)::text = (ym.pattern_code)::text) AND ((pm.original_store_code)::text = (jtm.original_store_code)::text)) -> Parallel Seq Scan on m_pattern_10010001 pm (cost=0.00..226.94 rows=5882 width=14) (actual time=0.030..1.780 rows=5000 loops=2) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text)) -> Parallel Hash (cost=4333.88..4333.88 rows=41 width=41) (actual time=2791.825..2791.827 rows=1182307 loops=2) Buckets: 65536 (originally 1024) Batches: 64 (originally 1) Memory Usage: 3840kB -> Merge Join (cost=3950.80..4333.88 rows=41 width=41) (actual time=64.775..519.901 rows=1182307 loops=2) Merge Cond: (((ym.host_cycle_code)::text = (jtm.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (jtm.store_cycle_code)::text) AND ((ym.information_category_code)::text = (jtm.information_category_code)::text)) -> Sort (cost=2045.25..2090.67 rows=18168 width=21) (actual time=26.968..28.654 rows=15434 loops=2) Sort Key: ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code Sort Method: quicksort Memory: 3181kB Worker 0: Sort Method: quicksort Memory: 25kB -> Parallel Seq Scan on m_reading_number_by_pattern_1109_036 ym (cost=0.00..759.94 rows=18168 width=21) (actual time=0.007..8.958 rows=15443 loops=2) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text)) -> Sort (cost=1905.55..1955.80 rows=20099 width=29) (actual time=75.601..212.554 rows=2365650 loops=1) Sort Key: jtm.host_cycle_code, jtm.store_cycle_code, jtm.information_category_code Sort Method: quicksort Memory: 2339kB -> Seq Scan on m_staff_by_information_order jtm (cost=0.00..468.99 rows=20099 width=29) (actual time=0.030..6.775 rows=20099 loops=1) -> Hash (cost=13.20..13.20 rows=320 width=102) (actual time=0.027..0.029 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on m_reading_number_by_store ymk (cost=0.00..13.20 rows=320 width=102) (actual time=0.021..0.022 rows=1 loops=1) -> Materialize (cost=7497.27..7497.30 rows=1 width=29) (actual time=4525.501..5101.215 rows=155752 loops=1) -> Group (cost=7497.27..7497.29 rows=1 width=29) (actual time=4525.490..5089.772 rows=17721 loops=1) Group Key: pm_1.original_store_code, ym_1.group_number, ym_1.host_cycle_code, ym_1.store_cycle_code, ym_1.information_category_code, wk2.setting_date -> Sort (cost=7497.27..7497.27 rows=1 width=29) (actual time=4525.487..5085.106 rows=18085 loops=1) Sort Key: pm_1.original_store_code, ym_1.group_number, ym_1.host_cycle_code, ym_1.store_cycle_code, ym_1.information_category_code, wk2.setting_date Sort Method: quicksort Memory: 2181kB -> Hash Left Join (cost=6163.32..7497.26 rows=1 width=29) (actual time=3309.082..5047.098 rows=18085 loops=1) Hash Cond: (((pm_1.original_store_code)::text = (ymk_1.original_store_code)::text) AND ((ym_1.host_cycle_code)::text = (ymk_1.host_cycle_code)::text) AND ((ym_1.store_cycle_code)::text = (ymk_1.store_cycle_code)::text) AND ((ym_1.information_category_code)::text = (ymk_1.information_category_code)::text)) Filter: ((((ymk_1.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk_1.apply_start_date) AND ('2023-11-09'::date <= ymk_1.apply_end_date)) OR (ymk_1.information_category_code IS NULL)) -> Hash Join (cost=6143.72..7477.47 rows=1 width=29) (actual time=3309.025..5040.124 rows=18085 loops=1) Hash Cond: (((ym_1.pattern_type)::text = (pm_1.pattern_type)::text) AND ((ym_1.pattern_code)::text = (pm_1.pattern_code)::text) AND ((wk2.original_store_code)::text = (pm_1.original_store_code)::text)) -> Hash Join (cost=5669.72..6999.70 rows=1 width=36) (actual time=3303.924..4575.576 rows=2496348 loops=1) Hash Cond: (((ym_1.group_number)::text = (wk2.group_number)::text) AND ((ym_1.host_cycle_code)::text = (wk2.host_cycle_code)::text) AND ((ym_1.store_cycle_code)::text = (wk2.store_cycle_code)::text)) -> Seq Scan on m_reading_number_by_pattern_1109_036 ym_1 (cost=0.00..982.50 rows=30886 width=21) (actual time=0.015..12.938 rows=30886 loops=1) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text)) -> Hash (cost=5669.70..5669.70 rows=1 width=23) (actual time=3303.897..3862.474 rows=4027 loops=1) Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 253kB -> Subquery Scan on wk2 (cost=5669.68..5669.70 rows=1 width=23) (actual time=3296.758..3861.178 rows=4027 loops=1) -> Group (cost=5669.68..5669.69 rows=1 width=23) (actual time=3296.756..3860.702 rows=4027 loops=1) Group Key: pm_2.original_store_code, ym_2.group_number, jtm_1.setting_date, jtm_1.host_cycle_code, jtm_1.store_cycle_code -> Sort (cost=5669.68..5669.68 rows=1 width=23) (actual time=3296.753..3856.222 rows=16777 loops=1) Sort Key: pm_2.original_store_code, ym_2.group_number, jtm_1.setting_date, jtm_1.host_cycle_code, jtm_1.store_cycle_code Sort Method: quicksort Memory: 2079kB -> Hash Left Join (cost=5354.20..5669.67 rows=1 width=23) (actual time=2914.666..3831.131 rows=16777 loops=1) Hash Cond: (((pm_2.original_store_code)::text = (ymk_2.original_store_code)::text) AND ((ym_2.host_cycle_code)::text = (ymk_2.host_cycle_code)::text) AND ((ym_2.store_cycle_code)::text = (ymk_2.store_cycle_code)::text) AND ((ym_2.information_category_code)::text = (ymk_2.information_category_code)::text)) Filter: ((((ymk_2.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk_2.apply_start_date) AND ('2023-11-09'::date <= ymk_2.apply_end_date)) OR (ymk_2.information_category_code IS NULL)) -> Gather (cost=5334.60..5649.88 rows=1 width=33) (actual time=2914.613..3824.856 rows=16777 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Hash Join (cost=4334.60..4649.78 rows=1 width=33) (actual time=2839.055..3096.744 rows=8389 loops=2) Hash Cond: (((pm_2.pattern_type)::text = (ym_2.pattern_type)::text) AND ((pm_2.pattern_code)::text = (ym_2.pattern_code)::text) AND ((pm_2.original_store_code)::text = (jtm_1.original_store_code)::text)) -> Parallel Seq Scan on m_pattern_10010001 pm_2 (cost=0.00..226.94 rows=5882 width=14) (actual time=0.034..1.958 rows=5000 loops=2) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text)) -> Parallel Hash (cost=4333.88..4333.88 rows=41 width=40) (actual time=2749.035..2749.038 rows=1182307 loops=2) Buckets: 65536 (originally 1024) Batches: 64 (originally 1) Memory Usage: 3840kB -> Merge Join (cost=3950.80..4333.88 rows=41 width=40) (actual time=25.227..451.907 rows=1182307 loops=2) Merge Cond: (((ym_2.host_cycle_code)::text = (jtm_1.host_cycle_code)::text) AND ((ym_2.store_cycle_code)::text = (jtm_1.store_cycle_code)::text) AND ((ym_2.information_category_code)::text = (jtm_1.information_category_code)::text)) -> Sort (cost=2045.25..2090.67 rows=18168 width=21) (actual time=12.502..13.951 rows=15434 loops=2) Sort Key: ym_2.host_cycle_code, ym_2.store_cycle_code, ym_2.information_category_code Sort Method: quicksort Memory: 3181kB Worker 0: Sort Method: quicksort Memory: 25kB -> Parallel Seq Scan on m_reading_number_by_pattern_1109_036 ym_2 (cost=0.00..759.94 rows=18168 width=21) (actual time=0.004..3.979 rows=15443 loops=2) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text)) -> Sort (cost=1905.55..1955.80 rows=20099 width=24) (actual time=25.441..171.873 rows=2365650 loops=1) Sort Key: jtm_1.host_cycle_code, jtm_1.store_cycle_code, jtm_1.information_category_code Sort Method: quicksort Memory: 2339kB -> Seq Scan on m_staff_by_information_order jtm_1 (cost=0.00..468.99 rows=20099 width=24) (actual time=0.020..3.138 rows=20099 loops=1) -> Hash (cost=13.20..13.20 rows=320 width=102) (actual time=0.035..0.037 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on m_reading_number_by_store ymk_2 (cost=0.00..13.20 rows=320 width=102) (actual time=0.021..0.022 rows=1 loops=1) -> Hash (cost=299.00..299.00 rows=10000 width=14) (actual time=4.468..4.469 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 578kB -> Seq Scan on m_pattern_10010001 pm_1 (cost=0.00..299.00 rows=10000 width=14) (actual time=0.026..2.351 rows=10000 loops=1) Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text)) -> Hash (cost=13.20..13.20 rows=320 width=102) (actual time=0.033..0.034 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on m_reading_number_by_store ymk_1 (cost=0.00..13.20 rows=320 width=102) (actual time=0.024..0.025 rows=1 loops=1) Planning Time: 8.215 ms Execution Time: 9019.218 ms
08-19
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值