Django + PostgreSQL лучший способ улучшить производительность медленной агрегации сводок?

Context

У меня есть Django REST API, использующий базу данных PostgreSQL с миллионами элементов. Эти элементы обрабатываются несколькими системами, а детали обработки отправляются обратно и хранятся в таблице Records. Упрощенные модели таковы:

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'))

У нас миллионы элементов и записей, и это работает достаточно хорошо, если мы выбираем менее тысячи элементов. При превышении этого значения требуются минуты. Попытка сделать это для сотен тысяч элементов приводит к катастрофе.

Вопросы

Могу ли я улучшить производительность этого запроса? Я не вижу способа, кроме игры с индексами?

В качестве альтернативы было бы плохой идеей добавить JSONField в модели Item, которое хранит кэш последних статусов для каждой системы для данного Item? Хотя мне не нравится идея дублирования данных, выполнение агрегации по полю, которое уже есть в модели Item, должно быть очень быстрым при запросах. У меня есть DjangoQ, в котором я могу использовать запланированные функции для поддержания этих полей в актуальном состоянии

Вы делаете один подзапрос на элемент, попробуйте сделать что-то вроде ниже и использовать aggregations.

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)

Я думаю, что простая группа по может быть полезной для получения статуса;

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')

Нижеприведенное решение уменьшает время запроса с 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

Мне не нравится, что мне нужно извлекать все значения из БД, чтобы подсчитать их, однако я не смог заставить агрегаты работать с различиями, необходимыми для того, чтобы обеспечить подсчет только одной записи на элемент.

Похоже на классическую проблему top-n-per-group. Вы хотите получить последний статус для каждого элемента.

Если вы не можете создать подходящий индекс и если у вас много строк в таблице api_item, то, скорее всего, наиболее эффективным методом будет использование оконной функции ROW_NUMBER, например, так. Я переписал ваш запрос из вопроса, чтобы получить тот же результат, но, надеюсь, более эффективно.

WITH
CTE_rn
AS
(
    SELECT
        U1."item_id"
        ,U0."status" AS "system_1_status"
        ,ROW_NUMBER() OVER (PARTITION BY U1."item_id" ORDER BY U0."created" DESC) AS rn
    FROM
        "api_record" U0 
        INNER JOIN "api_record_items" U1 ON U0."id" = U1."record_id"
    WHERE
        U0."system" = 'system_1'
)
,CTE_item_status
AS
(
    SELECT
        "item_id"
        ,"system_1_status"
    FROM
        CTE_rn
    WHERE
        rn = 1
)
SELECT
    "api_item"."id"
    ,"api_item"."details"
    ,CTE_item_status."system_1_status"
FROM
    "api_item"
    LEFT JOIN CTE_item_status ON CTE_item_status."item_id" = "api_item"."id"
;

Если вы знаете, что никогда не будет api_items без каких-либо статусов, или если вы не хотите видеть такие элементы в наборе результатов, то используйте INNER JOIN CTE_item_status в основном запросе вместо LEFT JOIN.

Вернуться на верх