查询性能优化求助：计算累计distinct kode_buatan时执行过慢

核心问题分析 #

原查询性能极差的根源：

• 关联子查询重复执行：每一行记录都会触发一次子查询，相当于对全表执行N次扫描（N为原表行数），资源开销呈指数级增长。
• 函数导致索引失效：EXTRACT(MONTH FROM date)、EXTRACT(YEAR FROM date)直接作用在date列上，使得该列的索引无法被利用，每次子查询都要全表扫描。
• 重复数据放大计算量：原表存在重复记录，导致COUNT(DISTINCT)需要反复处理相同的kode_buatan值，进一步拖慢查询。

优化步骤与示例SQL #

1. 先去重，压缩计算基数

首先提取shipto、年份、月份、kode_buatan的唯一组合，避免后续重复处理相同数据：

WITH unique_data AS (
    SELECT DISTINCT
        shipto,
        EXTRACT(YEAR FROM date) AS tahun,
        EXTRACT(MONTH FROM date) AS bulan,
        kode_buatan
    FROM table_A
)

2. 用窗口函数+分组统计替代关联子查询

通过标记每个kode_buatan在shipto下的首次出现年月，再按年月分组计算累计去重计数，彻底避免逐行触发子查询：

WITH unique_data AS (
    SELECT DISTINCT
        shipto,
        EXTRACT(YEAR FROM date) AS tahun,
        EXTRACT(MONTH FROM date) AS bulan,
        kode_buatan
    FROM table_A
),
first_occurrence AS (
    SELECT
        shipto,
        tahun,
        bulan,
        kode_buatan,
        -- 用年月拼接值标记首次出现的时间点
        MIN((tahun * 100) + bulan) OVER (PARTITION BY shipto, kode_buatan) AS first_year_month
    FROM unique_data
),
monthly_ytd_counts AS (
    SELECT
        shipto,
        tahun,
        bulan,
        -- 统计到当前年月为止，首次出现时间<=当前年月的kode_buatan数量
        COUNT(DISTINCT kode_buatan) FILTER (WHERE first_year_month <= (tahun * 100) + bulan) AS ytd_distinct_kode
    FROM first_occurrence
    GROUP BY shipto, tahun, bulan
)
-- 关联回原表，将计算好的累计数匹配到每条记录
SELECT
    a.*,
    my.ytd_distinct_kode
FROM table_A a
JOIN monthly_ytd_counts my
    ON a.shipto = my.shipto
    AND EXTRACT(YEAR FROM a.date) = my.tahun
    AND EXTRACT(MONTH FROM a.date) = my.bulan;

3. 添加索引加速查询

为原表创建复合索引，覆盖查询所需字段，避免全表扫描：

-- 索引覆盖shipto、date、kode_buatan，支持快速去重和年月提取
CREATE INDEX idx_table_a_shipto_date_kode ON table_A (shipto, date, kode_buatan);

如果数据库支持计算列，可以提前生成年月拼接列并建索引，彻底避免EXTRACT函数的开销：

-- 添加计算列（以PostgreSQL为例）
ALTER TABLE table_A ADD COLUMN tahun_bulan VARCHAR(6) GENERATED ALWAYS AS (TO_CHAR(date, 'YYYYMM')) STORED;
-- 创建覆盖索引
CREATE INDEX idx_table_a_shipto_tahunbulan_kode ON table_A (shipto, tahun_bulan, kode_buatan);

此时可将SQL中的EXTRACT替换为tahun_bulan，进一步提升查询效率。

对尝试方案的问题说明 #

• 方案1与原查询逻辑完全一致，未解决关联子查询和函数索引失效的核心问题，性能无改善。
• 方案2虽然提前建表，但仍使用关联子查询，且仅限定2024年数据，未从根本上优化计算逻辑，无法解决大量数据下的性能问题。

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