数仓性能调优:row_number() over(p)-rn=1性能瓶颈发现和改写套路

本文分享自华为云社区《GaussDB(DWS)性能调优:row_number() over(p)-rn=1性能瓶颈发现和改写套路》,作者:Zawami 。

1、改写场景

本套路应用于子查询中含有row_number() over(partition by order by) rn,并仅把rn列用于分类排序后筛选最大值的场景。

2、性能分析

GaussDB中SQL语句的执行很多时候是流式的,即对每一条数据进行流水加工,各层算子同时在执行,缩短执行耗时。

但是在一些场景下,需要先取得前一个算子的全部结果集,然后才能够进行下一步的加工;窗口函数就是其中的一种。

观察执行计划可以看到,SQL会在计算得到rn列后,再同本层查询其它列进行关联。由于存在窗口函数,必须先把51号算子先执行完,然后才能进行关联,造成性能瓶颈。

通过去窗口函数改写,我们可以使得分类汇总同明细数据之间的关联流水执行。

改写前局部SQL

SELECT

PROD_EN_NAME,

PROD_LIFE_CYCLE_STATUS

FROM

(

SELECT

PROD_EN_NAME,

LIFE_CYCLE AS PROD_LIFE_CYCLE_STATUS,

DEL_FLAG,

ROW_NUMBER ( ) OVER ( PARTITION BY PROD_EN_NAME ORDER BY RUN_DATE DESC ) RN

FROM

DMISC.DM_DIM_INV_PROD_ATTRI_SNAP_D

WHERE

DATA_TYPE = 1



AND DEL_FLAG = 'N'

AND RUN_DATE <= CAST ( '2023-06-11' || ' 00:00:00' AS TIMESTAMP )

)

WHERE

RN = 1

改写后局部SQL

WITH T AS (

SELECT

PROD_EN_NAME,

MAX ( LIFE_CYCLE ) AS PROD_LIFE_CYCLE_STATUS,

RUN_DATE

FROM

DMISC.DM_DIM_INV_PROD_ATTRI_SNAP_D

WHERE

DATA_TYPE = 1

AND DEL_FLAG = 'N'

AND RUN_DATE <= CAST ( '2023-06-11' || ' 00:00:00' AS TIMESTAMP )

GROUP BY

PROD_EN_NAME,

RUN_DATE

)

SELECT

PROD_EN_NAME,

PROD_LIFE_CYCLE_STATUS

FROM T

WHERE

(PROD_EN_NAME, RUN_DATE) IN (SELECT PROD_EN_NAME, MAX(RUN_DATE) FROM T GROUP BY PROD_EN_NAME)

改写解析:这里先把数据根据原SQL中row_number() over()的partition列和order列进行去重,由于原SQL未定义LIFE_CYCLE的排序方式,改写既可以使用MAX也可以使用MIN函数来进行聚合。然后再对去重后的数据进行过滤,过滤条件显然。

使用这种修改方法,修改前后的全量执行计划已在附件中给出。

这种改写方式解决了上层算子等窗口函数的问题。我们发现,一些业务场景下对不涉及聚合的其它列,比如上面例子中的LIFE_CYCLE并不敏感,且还需要进行进一步聚合的,那么对本层子查询中的去重其实没有硬性需求。可以进一步去除这层去重。

WITH T AS (

SELECT

PROD_EN_NAME,

LIFE_CYCLE AS PROD_LIFE_CYCLE_STATUS,

RUN_DATE

FROM

DMISC.DM_DIM_INV_PROD_ATTRI_SNAP_D

WHERE

DATA_TYPE = 1

AND DEL_FLAG = 'N'

AND RUN_DATE <= CAST ( '2023-06-11' || ' 00:00:00' AS TIMESTAMP )

)

SELECT

PROD_EN_NAME,

PROD_LIFE_CYCLE_STATUS

FROM T

WHERE

(PROD_EN_NAME, RUN_DATE) IN (SELECT PROD_EN_NAME, MAX(RUN_DATE) FROM T GROUP BY PROD_EN_NAME)

改写后执行计划如下:

可以看到,执行计划中虽然51层算子只快了200ms,但由于减少阻塞,1~7层算子的执行时间缩短了,总体比原先快了约480ms。

 

点击关注,第一时间了解华为云新鲜技术~

 

热门相关:离婚合约:前妻的秘密   哥哥的老婆3   哥哥的老婆2   演播室   我老婆的妹妹2