优化查询获取唯一记录：产品销售统计查询去重及性能优化

问题背景 #

需要获取产品销售统计数据，原查询因关联存在重复记录的pdt_solds表（仅pdt_soldsID唯一）导致返回大量重复行；改写后的查询解决了重复问题，但耗时从8秒增加到18秒，需在保证结果唯一的前提下提升性能。

原查询问题分析 #

1. 初始查询：通过LEFT JOIN关联pdt_solds，因该表存在重复记录，导致prdcost的每条记录被重复关联，结果行数膨胀至322212，虽然窗口函数统计了数量，但结果行冗余。
2. 改写后查询：用CTE预聚合pdt_solds解决了重复问题，但执行计划显示触发了Parallel Seq Scan，且Hash Right Join的Join Filter过滤了270万+行，聚合和关联的开销大幅增加。

优化方案 #

方案1：基于原查询直接去重，复用高效索引 #

利用DISTINCT去重结果行，同时用COUNT(DISTINCT)确保统计唯一的销售记录数量，复用原查询中已有的索引扫描逻辑：

-- 预期返回14941行，复用原索引提升性能
SELECT DISTINCT
       pc.qty_typecode,
       pc.prdcost,
       pc.d1cost,
       pc.d2cost,
       pc.saledate,
       qt.qtycvalue,
       COUNT(DISTINCT ps.pdt_soldsID) OVER (PARTITION BY pc.saledate, qt.qtycvalue) AS pdt_soldsCOUNT
FROM prdcost pc
LEFT JOIN qty_type qt ON qt.qty_typecode = pc.qty_typecode AND qt.classcode = pc.classcode
LEFT JOIN pdt_solds ps ON pc.qty_typecode = ps.qty_typecode 
                       AND pc.classcode = ps.classcode 
                       AND pc.saledate = ps.saledate
WHERE pc.classcode = 'CD901';

优势：无需修改表结构，直接复用原查询的索引扫描路径，避免CTE聚合的额外开销，同时确保结果唯一。

方案2：优化CTE聚合逻辑，添加覆盖索引 #

先给pdt_solds创建复合覆盖索引，让聚合操作直接走索引扫描，避免全表扫描：

-- 创建覆盖索引，包含过滤、分组、聚合所需字段
CREATE INDEX idx_pdt_solds_class_sale_qty ON pdt_solds(classcode, saledate, qty_typecode) INCLUDE(pdt_soldsID);

再修改CTE查询，利用索引提升聚合效率：

WITH cte AS (
    SELECT classcode, saledate, qty_typecode, COUNT(pdt_soldsID) AS pdt_solds_count
    FROM pdt_solds
    WHERE classcode = 'CD901'
    GROUP BY classcode, saledate, qty_typecode
)
SELECT pc.qty_typecode,
       pc.prdcost,
       pc.d1cost,
       pc.d2cost,
       pc.saledate,
       qt.qtycvalue,
       SUM(cte.pdt_solds_count) OVER (PARTITION BY pc.saledate, qt.qtycvalue) AS pdt_soldsCOUNT
FROM prdcost pc
LEFT JOIN qty_type qt ON pc.qty_typecode = qt.qty_typecode AND pc.classcode = qt.classcode
LEFT JOIN cte ON pc.classcode = cte.classcode 
              AND pc.saledate = cte.saledate 
              AND pc.qty_typecode = cte.qty_typecode
WHERE pc.classcode = 'CD901';

优势：索引覆盖所有聚合所需字段，将Parallel Seq Scan转为高效的索引扫描，大幅降低CTE的执行耗时；同时保留原查询的关联逻辑。

方案3：提前聚合到目标维度，避免窗口函数开销 #

直接在CTE中按saledate和qtycvalue维度聚合统计，跳过后续窗口函数的排序与计算：

WITH sale_agg AS (
    SELECT 
        ps.saledate,
        qt.qtycvalue,
        COUNT(DISTINCT ps.pdt_soldsID) AS total_solds
    FROM pdt_solds ps
    JOIN qty_type qt ON ps.qty_typecode = qt.qty_typecode AND ps.classcode = qt.classcode
    WHERE ps.classcode = 'CD901'
    GROUP BY ps.saledate, qt.qtycvalue
)
SELECT 
    pc.qty_typecode,
    pc.prdcost,
    pc.d1cost,
    pc.d2cost,
    pc.saledate,
    qt.qtycvalue,
    COALESCE(sa.total_solds, 0) AS pdt_soldsCOUNT
FROM prdcost pc
LEFT JOIN qty_type qt ON pc.qty_typecode = qt.qty_typecode AND pc.classcode = qt.classcode
LEFT JOIN sale_agg sa ON pc.saledate = sa.saledate AND qt.qtycvalue = sa.qtycvalue
WHERE pc.classcode = 'CD901';

优势：将窗口函数的计算提前到CTE聚合阶段，减少内存和CPU的排序开销，结果直接关联返回，逻辑更简洁。

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