Django+PostgreSQL慢汇总聚合查询性能优化方案咨询

我搭建了一套基于PostgreSQL数据库的Django REST API，库中存储有数百万条Item数据。这些Item会经过多个系统处理，处理详情会回传并存储在Record表中，简化后的模型定义如下：

class Item(models.Model):
    details = models.JSONField()


class Record(models.Model):
    items = models.ManyToManyField(Item)
    created = models.DateTimeField(auto_created=True)
    system = models.CharField(max_length=100)
    status = models.CharField(max_length=100)
    details = models.JSONField()

我需要支持对Item表做任意条件过滤，同时获取各处理系统的状态汇总。该汇总逻辑为：获取每个筛选出的Item对应每个系统的最新状态，统计各状态的计数。例如筛选出1055条Item时，示例返回结果如下：

{
  "System_1": {"running": 5, "completed": 1000, "error": 50},
  "System_2": {"halted": 55, "completed": 1000},
  "System_3": {"submitted": 1055}
}

我当前的实现逻辑为：通过如下查询获取System_1的处理状态计数，再对其他系统重复执行同类查询，最终封装为JSON返回。

Item.objects.filter(....).annotate(
    system_1_status=Subquery(
        Record.objects.filter(
            system='System_1',
            items__id=OuterRef('pk')
        ).order_by('-created').values('status')[:1]
    )
).values('system_1_status').annotate(count=Count('system_1_status'))

该ORM查询转换后的SQL语句如下：

SELECT 
    "api_item"."id", 
    "api_item"."details", 
    (
        SELECT 
            U0."status" 
        FROM 
            "api_record" U0 
        INNER JOIN 
            "api_record_items" U1 
        ON 
            (U0."id" = U1."record_id") 
        WHERE 
            (U1."item_id" = ("api_item"."id") AND U0."system" = 'System_1') 
        ORDER BY 
            U0."created" DESC LIMIT 1
    ) AS "system_1_status" 
FROM "api_item"

当前库中存在数百万条Item与Record数据，当筛选的Item数量少于1000条时查询性能尚可，超过该量级后查询耗时可达数分钟，若筛选数十万条Item则性能会出现灾难性下降。

1. 除了调整索引外，是否有其他方式可以优化该查询的性能？
2. 如果在Item模型中新增JSONField，用于缓存该Item对应各系统的最新状态是否是合理方案？我虽不希望做数据冗余，但直接基于Item模型上的字段做聚合查询速度会非常快，我可以使用DjangoQ配置定时任务保证该缓存字段的数据实时性。

针对问题1：非索引类的查询优化方案 #

你当前的性能瓶颈本质是关联子查询导致的N+1问题：现有写法会为每一条筛选出的Item单独执行一次Record表查询，筛选出几十万条Item时，单系统就要跑几十万次关联查询，多系统场景下查询次数还要翻倍，性能必然雪崩。除了建索引之外，有几个成本极低、效果明显的优化方向：

• 替换逐行子查询为单次集合查询：用PostgreSQL的DISTINCT ON语法或者ROW_NUMBER()窗口函数，一次性取出所有符合条件的Item在所有系统下的最新状态，不需要每个系统单独写子查询。核心逻辑是先把过滤后的Item ID集合拿出来，再关联Record中间表，按item_id + system分组，取每组created时间最新的那条记录的status，最后直接按system和status分组计数即可。这种写法只会扫1-2次表，完全避免逐行执行子查询的开销，性能至少提升两个数量级。
  参考SQL逻辑如下：

  -- 第一步：先取出符合过滤条件的Item ID，缩小后续关联范围
  WITH matched_items AS (
      SELECT id FROM api_item
      WHERE -- 替换为你实际的Item过滤条件
  ),
  -- 第二步：一次性取出所有匹配Item在各系统的最新状态
  item_latest_status AS (
      SELECT DISTINCT ON (ri.item_id, r.system)
          ri.item_id, r.system, r.status
      FROM api_record r
      INNER JOIN api_record_items ri ON r.id = ri.record_id
      INNER JOIN matched_items mi ON ri.item_id = mi.id
      ORDER BY ri.item_id, r.system, r.created DESC
  )
  -- 第三步：直接聚合得到各系统的状态计数
  SELECT system, status, COUNT(*) AS cnt
  FROM item_latest_status
  GROUP BY system, status;
  

  ORM层面如果不想写原生SQL，可以借助支持CTE的第三方包实现，逻辑和上述SQL对齐即可。
• 提前缩小关联范围：不要在子查询里直接关联全量Record表再做过滤，先把匹配的Item ID查出来（过滤后ID数量少于10万可以直接放内存，量级更大可以落临时表），后续关联Record时只匹配这部分ID，能大幅减少扫描的行数。
• 检查多对多关联的去重逻辑：当前ORM生成的关联查询如果没有加distinct，中间表可能产生重复行，虽然LIMIT 1不影响最终结果，但会增加排序阶段的开销，可以手动调整关联条件减少无效行扫描。

针对问题2：缓存字段方案评估 #

在Item表新增JSONField缓存各系统最新状态是完全可行的，是这类统计场景下非常通用的优化方案，没必要为了严格的数据库范式要求牺牲核心查询性能。
几个落地的注意点：

• 不建议用定时全量同步的方案维护缓存，开销大且实时性差。更优的方式是增量更新：每当新的Record写入并关联Item时，同步更新对应Item缓存字段中对应系统的状态值。如果担心逻辑分散，可以把更新逻辑放到Record的post_save信号里，或者统一封装到Record的写入服务层，保证所有写入路径都会触发缓存更新，一致性比定时任务好很多，资源消耗也极低。
• 如果业务能接受秒级的延迟，也可以在Record写入时把需要更新的Item ID推到DjangoQ的异步队列，异步消费更新缓存，避免占用同步请求的耗时，比定时全表扫描效率高得多。
• 字段类型优先选JSONB：Django的JSONField在PostgreSQL后端默认映射为JSONB类型，支持索引和高效的键值查询，给该字段加GIN索引后，后续做聚合查询的速度和直接查询结构化字段几乎没有差异。

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