PostgreSQL查询优化求助：现有查询耗时且CTE出现重复数据

问题分析 #

原查询的核心问题：

• 存在无用的左连接：Users表在查询中未被引用任何字段，左连会额外增加数据处理开销
• 先关联维度表再聚合，可能因维度表的一对多关系（或数据冗余）导致行膨胀，拖慢分组计算，也是CTE出现重复数据的根源
• 分组使用位置序号（1）可读性差，且易出错

优化方案 #

1. 移除无用连接，重构查询逻辑（直接优化版） #

先聚合TimeSheet主表数据，再关联维度表获取名称，避免行膨胀：

select 
    agg."monthYear",
    agg."TimeEntryID",
    agg."TotalHours",
    agg."CreatedOn",
    agg."ApplicationID",
    agg."TSTypeCodeID",
    rcv."CodeName" as TSType,
    agg."UserID",
    agg."StatusID",
    st."Status"
from (
    select 
        to_char(ts."SubmittedDate", 'YYYY-MM') as "monthYear",
        MIN(ts."TimeEntryID") as "TimeEntryID",
        SUM(ts."SpentTime") as "TotalHours",
        MAX(ts."CreatedOn") as "CreatedOn",
        ts."ApplicationID",
        ts."TSTypeCodeID",
        ts."UserID",
        ts."StatusID"
    from task_management_v1."TimeSheet" ts
    where 
        ts."ApplicationID" = 34
        and ts."UserID" in ('YXR4318','KXL5356','BXB0448') 
        and ts."StatusID" in (47,44,45,46) 
        and ts."SubmittedDate" > CURRENT_DATE - INTERVAL '3 months' 
    group by 
        ts."UserID", 
        ts."StatusID", 
        ts."ApplicationID",
        to_char(ts."SubmittedDate", 'YYYY-MM'),
        ts."TSTypeCodeID"
) agg
left join task_management_v1."Status" st
    on agg."StatusID" = st."ID"
left join task_management_v1."RefCodeValue" rcv
    on agg."TSTypeCodeID" = rcv."CodeValueID"
order by agg."CreatedOn" desc
limit 25 offset 0;

2. 正确使用CTE的写法（避免重复数据） #

如果一定要用CTE，核心是先聚合主表，再关联维度表，而不是先关联再聚合：

with agg_time_sheet as (
    select 
        to_char(ts."SubmittedDate", 'YYYY-MM') as "monthYear",
        MIN(ts."TimeEntryID") as "TimeEntryID",
        SUM(ts."SpentTime") as "TotalHours",
        MAX(ts."CreatedOn") as "CreatedOn",
        ts."ApplicationID",
        ts."TSTypeCodeID",
        ts."UserID",
        ts."StatusID"
    from task_management_v1."TimeSheet" ts
    where 
        ts."ApplicationID" = 34
        and ts."UserID" in ('YXR4318','KXL5356','BXB0448') 
        and ts."StatusID" in (47,44,45,46) 
        and ts."SubmittedDate" > CURRENT_DATE - INTERVAL '3 months' 
    group by 
        ts."UserID", 
        ts."StatusID", 
        ts."ApplicationID",
        to_char(ts."SubmittedDate", 'YYYY-MM'),
        ts."TSTypeCodeID"
)
select 
    ats."monthYear",
    ats."TimeEntryID",
    ats."TotalHours",
    ats."CreatedOn",
    ats."ApplicationID",
    ats."TSTypeCodeID",
    rcv."CodeName" as TSType,
    ats."UserID",
    ats."StatusID",
    st."Status"
from agg_time_sheet ats
left join task_management_v1."Status" st
    on ats."StatusID" = st."ID"
left join task_management_v1."RefCodeValue" rcv
    on ats."TSTypeCodeID" = rcv."CodeValueID"
order by ats."CreatedOn" desc
limit 25 offset 0;

3. 添加性能优化索引 #

为TimeSheet表创建复合索引，覆盖过滤条件和聚合所需字段，实现索引覆盖扫描，避免回表：

create index idx_timesheet_filter_agg on task_management_v1."TimeSheet"
("ApplicationID", "UserID", "StatusID", "SubmittedDate")
include ("TimeEntryID", "SpentTime", "CreatedOn", "TSTypeCodeID");

关键优化点说明 #

• 移除无用连接：Users表未被使用，左连会增加数据行数和IO开销，直接删除即可
• 先聚合后关联：主表聚合后数据量大幅减少，再关联维度表不会导致行膨胀，彻底解决CTE重复数据问题
• 索引优化：复合索引精准匹配where条件，include子句包含聚合所需字段，让数据库无需读取主表数据，直接从索引完成计算

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