Оптимизация агрегации Django по подмножествам

В настоящее время я пытаюсь выполнить некоторые расчеты сводной статистики для подмножеств на основе дат большой таблицы "соревнования" (~3M строк), хранящейся в SQLite. В частности, я пытаюсь вычислить статистику для:

1. этот год
2. прошлый год
3. срок жизни конкурента

Вот разбивка моделей:

class Competitor(models.Model):
    # ... ID is the only important part here

class Venue(models.Model):
    # ... ID is the only important part here

class Division(models.Model):
    venue = models.ForeignKey(Venue)
    # ...

class Level(models.Model):
    division = models.ForeignKey(Division)
    # ...


class Class(models.TextChoices):
    STANDARD = "Standard", _("Standard")
    JWW = "JWW", _("JWW")


class Run(models.Model):
    competitor_id = models.ForeignKey(Competitor, related_name="runs", db_index=True)
    date = models.DateField(verbose_name="Date created", db_index=True)
    MACH = models.IntegerField(..., db_index=True)
    PACH = models.IntegerField(..., db_index=True)
    yps = models.FloatField(..., db_index=True)
    score = models.IntegerField(..., db_index=True)
    qualified = models.BooleanField(..., db_index=True)
    division = models.ForeignKey(Division, db_index=True)
    level = models.ForeignKey(Level, db_index=True)
    cls = models.CharField(max_length=..., choices=Class.choices)
    # ... Other fields that aren't relevant

Для каждого Competitor я хочу создать сводную статистику, описывающую их производительность в этом году, в прошлом году и за все время, и сохранить ее в Report модели:

class CurrPrevLifetime(models.Model):
    curr_yr = models.FloatField(default=0)
    prev_yr = models.FloatField(default=0)
    lifetime = models.FloatField(default=0)

class Report(models.Model):
    ... = models.ForeignKey(CurrPrevLifetime, related_name=...)
    # repeat as needed for as many fields need this

Моя текущая установка агрегации выглядит следующим образом:

curr_yr = Q(date__year=datetime.date.today().year)
prev_yr = Q(date__year=datetime.date.today().year-1)

JWW = Q(cls=Class.JWW)
standard = Q(cls=Class.STANDARD)

aggregates = {
    "curr_yr_max_standard_MACH": Max("MACH", filter=curr_yr & standard),
    "curr_yr_max_standard_PACH": Max("PACH", filter=curr_yr & standard),
    "curr_yr_average_yps_standard": Avg("yps", filter=curr_yr & standard),
    "curr_yr_max_yps_standard": Max("yps", filter=curr_yr & standard),
    "curr_yr_max_JWW_MACH": Max("MACH", filter=curr_yr & JWW),
    "curr_yr_max_JWW_PACH": Max("PACH", filter=curr_yr & JWW),
    "curr_yr_average_yps_JWW": Avg("yps", filter=curr_yr & JWW),
    "curr_yr_max_yps_JWW": Max("yps", filter=curr_yr & JWW),
    "curr_yr_MACH_points": Sum("MACH", filter=curr_yr),
    "curr_yr_PACH_points": Sum("PACH", filter=curr_yr),

    "prev_yr_max_standard_MACH": Max("MACH", filter=prev_yr & standard),
    "prev_yr_max_standard_PACH": Max("PACH", filter=prev_yr & standard),
    "prev_yr_average_yps_standard": Avg("yps", filter=prev_yr & standard),
    "prev_yr_max_yps_standard": Max("yps", filter=prev_yr & standard),
    "prev_yr_max_JWW_MACH": Max("MACH", filter=prev_yr & JWW),
    "prev_yr_max_JWW_PACH": Max("PACH", filter=prev_yr & JWW),
    "prev_yr_average_yps_JWW": Avg("yps", filter=prev_yr & JWW),
    "prev_yr_max_yps_JWW": Max("yps", filter=prev_yr & JWW),
    "prev_yr_MACH_points": Sum("MACH", filter=prev_yr),
    "prev_yr_PACH_points": Sum("PACH", filter=prev_yr),

    "lifetime_max_standard_MACH": Max("MACH", filter=standard),
    "lifetime_max_standard_PACH": Max("PACH", filter=standard),
    "lifetime_average_yps_standard": Avg("yps", filter=standard),
    "lifetime_max_yps_standard": Max("yps", filter=standard),
    "lifetime_max_JWW_MACH": Max("MACH", filter=JWW),
    "lifetime_max_JWW_PACH": Max("PACH", filter=JWW),
    "lifetime_average_yps_JWW": Avg("yps", filter=JWW),
    "lifetime_max_yps_JWW": Max("yps", filter=JWW),
    "lifetime_MACH_points": Sum("MACH"),
    "lifetime_PACH_points": Sum("PACH"),
}
competitor.runs.aggregate(**aggregates)

Затем я беру результаты, разбиваю их на тройки (curr, prev, lifetime) и сохраняю их как CurrPrevLifetime, используя CurrPrevLifetime.objects.bulk_create.

Эта агрегация занимает удивительно много времени, учитывая, что операции максимизации, усреднения и суммирования (~5 с). Для сравнения, у конкурента за все время его работы было где-то в районе ~500 прогонов.

Что виновато в снижении производительности? Индексы применяются к вычисляемым полям и отношениям между моделями, я пробовал идти по пути "filter then aggregate", без заметной разницы и со значительным увеличением LOC written.

При 40 000 конкурентах 5 секунд на отчет - это слишком долго. Как мне ускорить этот процесс? Я буду рад перестроить структуру, если это необходимо.

Я пытался решить эту проблему несколькими способами:

1. Разбиение competitor.runs на curr_yr_runs, prev_yr_runs и lifetime_runs, и выполнение агрегатов по отдельности (плохая производительность).

2. Разбив их по cls на JWW_runs и standard_runs, и выполнив агрегаты по датам таким образом (низкая производительность).

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