上下文
我有一个Django REST API，它使用PostgreSQL数据库和数百万项.这些项目由多个系统处理，处理详细信息会发回并存储在记录表中.简化模型包括:
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表进行任意筛选，并获得各种处理系统的摘要.此摘要获取每个系统的每个选定项目的最新状态，并显示每个状态的计数.例如，如果我筛选1055个项目，返回的示例为:
{
System_1: [running: 5, completed: 1000, error: 50],
System_2: [halted: 55, completed: 1000],
System_3: [submitted: 1055]
}
我目前正在进行如下查询，返回System\u 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"
我们有数以百万计的项目和记录，如果我们 Select 的项目少于1000个，这项工作就相当好了.在这上面需要几分钟.试图为数十万件物品这么做是灾难性的.
问题
我可以改进此查询的性能吗？我不知道怎么做，除了玩索引？
或者，在项目模型中添加一个JSONField来存储该项目的每个系统的最新状态缓存，这会是一个坏主意吗？虽然我不喜欢复制数据的 idea ，但在查询时，对项目模型上已经存在的字段进行聚合应该很快.我有DjangoQ，我可以使用调度函数来保持这些字段最新.