背景
我有一个使用PostgreSQL数据库的Django REST API，其中包含数百万个项目。这些项目由多个系统进行处理，处理详细信息被发送回并存储在记录表中。简化模型如下：
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()
目标
我想对“Items”表进行任意过滤，并获取各种处理系统的汇总信息。此摘要为所选每个系统的每个选择的“Item”获取最新状态，并显示每个状态的计数。例如，如果我筛选了1055个项，则返回示例如下：
{
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'))
这将转换为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"
我们有数百万个项目和记录，如果选择的项目少于一千个，则性能表现良好。但是如果选择的项目数量超过一千个，则需要花费数分钟时间。当试图处理数十万个项目时，情况变得非常糟糕。
问题
我该如何提高此查询的性能？除了调整索引，我看不到还有其他方法？
或者，是否将 JSONField 添加到 Item 模型中并存储每个系统的最新状态缓存是一个不好的主意？虽然我不喜欢复制数据的想法，但在模型上已经存在的字段进行聚合应该可以在查询时非常快速。我有 DjangoQ，可以使用计划函数来保持这些字段的最新状态。