GaussDB(DWS)性能调优：generate_series导致的执行不下推问题优化方案

部分业务场景下存在记录或者维度扩展，这时候会使用函数generate_series生成序列号，作为维度扩展编号，但是在DWS里面类似generate_series这种多结果集函数会导致部分执行下推，导致性能劣化。本文主要是针对这种场景提出改写的优化思路

• 预置条件

CREATE TABLE tmf(proj_num text, du_id bigint, du_code bigint, du_name text, item_cnt int);
CREATE TABLE ext(del_flag char, extension_value text);

• 原始SQL

SELECT
    tmf.proj_num,
    tmf.du_id,
    tmf.du_code,
    tmf.du_name,
    'Scenario' AS extension_name
FROM tmf,
(SELECT * FROM generate_series(1,(
    SELECT
        max(length(ext.extension_value || ',') - nvl(length(replace(ext.extension_value, ',','')), 0)) max_len
    FROM ext
    WHERE ext.del_flag = 'N'))  AS LVL
) seq 
WHERE seq.lvl <= tmf.item_cnt;

• 优化后SQL

SELECT
    tmf.proj_num,
    tmf.du_id,
    tmf.du_code,
    tmf.du_name,
    'Scenario' AS extension_name
FROM tmf,
(
    WITH RECURSIVE tmp(lvl) AS(
        SELECT
            max(length(ext.extension_value || ',') - nvl(length(replace(ext.extension_value, ',','')), 0)) max_len
        FROM ext
        WHERE ext.del_flag = 'N'

        UNION ALL

        SELECT lvl - 1 FROM tmp WHERE lvl - 1 > 0
    )
    SELECT * FROM tmp
) seq 
WHERE seq.lvl <= tmf.item_cnt;

• 优化前后SQL语句差异

• 执行计划差异