Django PostgreSQL提高慢速匯總聚合性能的最佳方法？

當子查詢太慢時，我正在尋找改進的性能，每個表中有數百萬行并選擇數十萬個專案進行匯總。

語境

我有一個使用 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 表進行任意過濾并獲得各種處理系統的摘要。此摘要獲??取每個系統的每個選定專案的最新狀態，并顯示每個狀態的計數。例如，如果我過濾 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，我可以使用預定函式來保持這些欄位是最新的。

uj5u.com熱心網友回復：

您正在為每個專案執行一個子查詢，請嘗試執行以下操作并使用聚合。

from django.db.models import Case, When, Sum

items = Item.objects.filter(# your condition)
results = Record.objects.values("system").annotate(
    running=Sum(Case(When(status="running", then=1), default=0, 
        output_field=IntegerField())),
    completed=Sum(Case(When(status="completed", then=1), default=0, 
        output_field=IntegerField())),
    # add more status annotations
).order_by("system").filter(items=items)

uj5u.com熱心網友回復：

我認為一個簡單的group by可能對獲取狀態有用；

from django.db.models import Count
Item.objects.filter(record__system='System_1').values('record__status').annotate(c=Count('record__status')).values('record__status', 'c')

uj5u.com熱心網友回復：

下面的解決方案將查詢時間從我的示例的 16 秒減少到 2 秒。

from collections import Counter

items = Item.objects.filter(...)
{
    "System_1": Counter(
        items.
            filter(record__system='System_1').
            order_by('id', '-record__created').
            values_list('record__status', flat=True).
            distinct('id')),
    "System_2": Counter(
        items.
            filter(record__system='System_2').
            order_by('id', '-record__created').
            values_list('record__status', flat=True).
            distinct('id'))
}

生成的 SQL 查詢的關鍵部分是：

SELECT DISTINCT ON ("api_item"."id") "api_record"."status" FROM "api_item" INNER JOIN "api_record_items" ON ("api_item"."id" = "api_record_items"."item_id") INNER JOIN "api_record" ON ("api_record_items"."record_id" = "api_record"."id") WHERE "api_record"."system" = System_1 ORDER BY "api_item"."id" ASC, "api_record"."created" DESC

我不喜歡我需要從資料庫中提取所有值來計算它們，但是我無法讓聚合與確保每個專案只計算一條記錄所需的不同值一起作業。

標籤：django 数据库 PostgreSQL 表现 django-rest-framework

上一篇：在不使用distinct()方法的情況下查找整數串列中的重復元素

下一篇：TLSGolang上的Http未接收標頭