Redshift高资源消耗查询优化请求：降低成本与每日状态统计

引言 #

数据工程团队反馈我当前运行的查询资源消耗极高，需要优化建议来降低查询成本。

场景说明 #

我有一张braze_subscription_history表（原描述中为status_history_table），记录所有客户的订阅状态及生效日期范围。需要生成最近365天的每日统计结果，展示状态变更/未变更的客户数量。

表示例 #

braze_subscription_history示例：

注：当valid_to为'99991231'时，表示这是客户当前的状态，会持续至下一次变更。

dim_date是日期维度表（原描述中为dim_date_table）：

当前查询与执行计划 #

实际运行的查询 #

with
    base as (
        select 
            d.date_,
            h.subscription_status as status,
            braze_id
        from 
            curated_data.braze_subscription_history h
            inner join business_layer.dim_date d 
                on d.pk_date between h.valid_from and h.valid_to 
                and d.date_ <= current_date
                and d.date_ > current_date - 365
        where
            h.source = 'PUSH'
            and h.country_code = 'BR'
    ),

    -- 获取每个客户的上一状态
    lag as (
        select 
            *,
            lag(status,1)over(partition by braze_id order by date_ asc) as previous_status
        from
            base
    )
    

    select 
        date_
        , case
            when previous_status = status then 'No change'
            else 'Changed'
        end as class,
        count(1) as user_qty
    from 
        lag
    group by 
        1,2

执行计划（explain结果） #

XN HashAggregate  (cost=1000679035081.81..1000679075081.81 rows=8000000 width=78)
  ->  XN Subquery Scan lag  (cost=1000627708182.17..1000668036460.46 rows=1466482847 width=78)
        ->  XN Window  (cost=1000627708182.17..1000649705424.87 rows=1466482847 width=45)
              Partition: h.braze_id
              Order: d.date_
              ->  XN Sort  (cost=1000627708182.17..1000631374389.29 rows=1466482847 width=45)
                    Sort Key: h.braze_id, d.date_
                    ->  XN Network  (cost=7525.05..404438272.74 rows=1466482847 width=45)
                          Distribute
                          ->  XN Nested Loop DS_BCAST_INNER  (cost=7525.05..404438272.74 rows=1466482847 width=45)
                                Join Filter: (("inner".pk_date <= ("outer".valid_to)::bigint) AND ("inner".pk_date >= ("outer".valid_from)::bigint))
                                ->  XN Seq Scan on braze_subscription_history h  (cost=0.00..1472107.32 rows=36159851 width=49)
                                      Filter: (((source)::text = 'PUSH'::text) AND ((country_code)::text = 'BR'::text))
                                ->  XN Materialize  (cost=7525.05..7528.70 rows=365 width=12)
                                      ->  XN Seq Scan on dim_date d  (cost=0.00..224.69 rows=365 width=12)
                                            Filter: ((date_ > '2023-04-09'::date) AND (date_ <= '2024-04-08'::date))
----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----

表结构定义（DDL） #

CREATE TABLE IF NOT EXISTS curated_data.braze_subscription_history
(
    country_code VARCHAR(2)   ENCODE zstd
    ,braze_id VARCHAR(32)   ENCODE zstd
    ,external_id VARCHAR(32)   ENCODE zstd
    ,source VARCHAR(16)   ENCODE zstd
    ,subscription_status VARCHAR(16)   ENCODE zstd
    ,valid_from INTEGER   ENCODE az64
    ,valid_to INTEGER   ENCODE az64
)
DISTSTYLE EVEN


CREATE TABLE IF NOT EXISTS business_layer.dim_date
(
    pk_date BIGINT NOT NULL  ENCODE az64
    ,iso_date VARCHAR(40)   ENCODE zstd
    ,"year" VARCHAR(16)   ENCODE zstd
    ,quarter_of_year VARCHAR(8)   ENCODE zstd
    ,month_of_year VARCHAR(8)   ENCODE zstd
    ,date_ DATE   ENCODE az64
    ,year_month VARCHAR(6)   ENCODE zstd
    ,id_date BIGINT   ENCODE az64
)
DISTSTYLE ALL

优化建议 #

1. 先过滤历史记录，减少关联数据量 #

当前查询是先关联全量日期再过滤，改为先筛选与最近365天有时间重叠的状态记录，再关联日期表，大幅缩小关联范围：

with filtered_history as (
    select 
        braze_id,
        subscription_status as status,
        valid_from,
        valid_to
    from curated_data.braze_subscription_history h
    where 
        h.source = 'PUSH'
        and h.country_code = 'BR'
        -- 筛选与统计时间范围有重叠的记录
        and valid_from <= (select max(pk_date) from business_layer.dim_date where date_ <= current_date)
        and valid_to >= (select min(pk_date) from business_layer.dim_date where date_ > current_date - 365)
),
base as (
    select 
        d.date_,
        f.status,
        f.braze_id
    from filtered_history f
    inner join business_layer.dim_date d 
        on d.pk_date between f.valid_from and f.valid_to 
        and d.date_ <= current_date
        and d.date_ > current_date - 365
),
lag as (
    select 
        *,
        lag(status,1)over(partition by braze_id order by date_ asc) as previous_status
    from base
)
select 
    date_,
    case
        when previous_status = status then 'No change'
        else 'Changed'
    end as class,
    count(1) as user_qty
from lag
group by 1,2

2. 添加复合索引，避免全表扫描 #

在braze_subscription_history上创建覆盖过滤条件和关联字段的复合索引，直接通过索引获取所需数据：

CREATE INDEX idx_braze_sub_br_push ON curated_data.braze_subscription_history 
(country_code, source, valid_from, valid_to) INCLUDE (braze_id, subscription_status);

3. 基于状态变更点计算，避免全量日期膨胀 #

当前逻辑会把每条状态记录拆分成对应每天的数据，导致中间表数据量爆炸。改为只处理状态变更的时间区间，再关联日期表：

with filtered_history as (
    select 
        braze_id,
        subscription_status as status,
        valid_from,
        -- 限制状态结束日期不超过统计截止日
        least(valid_to, (select max(pk_date) from business_layer.dim_date where date_ <= current_date)) as valid_to,
        -- 获取同一用户的上一状态结束日期
        lag(valid_to) over(partition by braze_id order by valid_from) as prev_valid_to
    from curated_data.braze_subscription_history h
    where 
        h.source = 'PUSH'
        and h.country_code = 'BR'
        and valid_from <= (select max(pk_date) from business_layer.dim_date where date_ <= current_date)
        and valid_to >= (select min(pk_date) from business_layer.dim_date where date_ > current_date - 365)
),
-- 生成状态变更区间（包括统计开始前未结束的状态）
status_changes as (
    select 
        braze_id,
        status,
        greatest(valid_from, (select min(pk_date) from business_layer.dim_date where date_ > current_date - 365)) as start_date,
        valid_to as end_date
    from filtered_history
    union all
    select 
        braze_id,
        lag(status) over(partition by braze_id order by valid_from) as status,
        (select min(pk_date) from business_layer.dim_date where date_ > current_date - 365) as start_date,
        prev_valid_to as end_date
    from filtered_history
    where prev_valid_to >= (select min(pk_date) from business_layer.dim_date where date_ > current_date - 365)
),
-- 关联日期表获取每日状态
daily_status as (
    select 
        d.date_,
        s.status,
        s.braze_id
    from status_changes s
    inner join business_layer.dim_date d
        on d.pk_date between s.start_date and s.end_date
),
lag as (
    select 
        *,
        lag(status,1)over(partition by braze_id order by date_ asc) as previous_status
    from daily_status
)
select 
    date_,
    case
        when previous_status is null then 'Changed' -- 初始状态视为变更
        when previous_status = status then 'No change'
        else 'Changed'
    end as class,
    count(1) as user_qty
from lag
group by 1,2

4. 调整表分布键，减少跨节点传输 #

braze_subscription_history当前是DISTSTYLE EVEN，将braze_id设为分布键，让同一用户的数据落在同一节点，减少窗口函数计算时的网络开销：

ALTER TABLE curated_data.braze_subscription_history ALTER DISTKEY braze_id;

内容的提问来源于stack exchange，提问作者Miguel