EF Core+PostgreSQL中添加IQueryable过滤致查询性能骤降

问题根源在于原写法中，Select里的context.B.Max(...)会被EF解析为针对每条A记录的独立子查询，添加过滤条件后，数据库需要对数百万条A记录逐一执行MAX子查询做判断，导致性能急剧下降。以下是两种高效的优化方案：

方案一：预计算分组MAX后关联 #

先批量计算每个A对应的最新PromotionDay，再与A表关联查询，MAX仅执行一次分组计算：

// 预查询每个A的最新PromotionDay（假设B关联A的外键是AId）
var latestPromotionPerA = context.B
    .GroupBy(b => b.AId)
    .Select(g => new {
        AId = g.Key,
        LatestPromotionDay = g.Max(b => b.PromotionDay)
    });

// 关联A表并选择所需字段
var query = context.A.AsNoTracking()
    .Join(latestPromotionPerA,
          a => a.Id,
          b => b.AId,
          (a, b) => new {
              PromotionDay = b.LatestPromotionDay,
              // 这里添加A表的其他属性
              a.Id,
              a.ProductName // 示例属性，替换为你的实际字段
          });

// 添加过滤条件（直接在关联后的结果上筛选）
if (filters.PromotionDate is { } promotionDate)
{
    query = query.Where(x => x.PromotionDay == promotionDate);
}

方案二：使用窗口函数筛选最新记录 #

如果后续需要B表的其他字段，可通过ROW_NUMBER()窗口函数筛选每个A对应的最新B记录，再关联A表：

// 筛选每个A对应的最新B记录
var latestBRecords = context.B
    .Select(b => new {
        b.AId,
        b.PromotionDay,
        // 按AId分组，按PromotionDay倒序排，取第一条
        RowNum = EF.Functions.RowNumber().Over(PartitionBy(b.AId).OrderByDescending(b => b.PromotionDay))
    })
    .Where(r => r.RowNum == 1);

// 关联A表查询
var query = context.A.AsNoTracking()
    .Join(latestBRecords,
          a => a.Id,
          b => b.AId,
          (a, b) => new {
              PromotionDay = b.PromotionDay,
              // 添加A表其他属性
          });

// 过滤逻辑同上
if (filters.PromotionDate is { } promotionDate)
{
    query = query.Where(x => x.PromotionDay == promotionDate);
}

额外优化建议 #

给B表创建复合索引(AId, PromotionDay DESC)，这会大幅加速分组MAX或窗口函数的计算过程，进一步提升查询性能。

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