时间戳

首页 > 解决方案 > Django 聚合需要很多时间

python - Django 聚合需要很多时间

问题描述

我有一个定义如下的模型

class Image(model.Models):
    # Stages
    STAGE_TRAIN = 'train'
    STAGE_VAL = 'val'
    STAGE_TEST = 'test'
    STAGE_TRASH = 'trash'

    STAGE_CHOICES = (
        (STAGE_TRAIN, 'Train'),
        (STAGE_VAL, 'Validation'),
        (STAGE_TEST, 'Test'),
        (STAGE_TRASH, 'Trash'),
    )
    stage = models.CharField(max_length=5, choices=STAGE_CHOICES, default=STAGE_TRAIN)
    commit = models.ForeignKey(Commit, on_delete=models.CASCADE, related_name="images", related_query_name="image")

在我的数据库中，我有 170k 图像，我尝试使用一个端点来按阶段计算所有图像

目前我有类似的东西

base_query = Image.objects.filter(commit=commit_uuid).only('id', 'stage')
count_query = base_query.aggregate(count_train=Count('id', filter=Q(stage='train')),
                                   count_val=Count('id', filter=Q(stage='val')),
                                   count_trash=Count('id', filter=Q(stage='trash')))

但这大约需要 40 秒，当我尝试在我的 shell 中查看 SQL 请求时，我有一些看起来不错的东西

{'sql': 'SELECT COUNT("image"."id") FILTER (WHERE "image"."stage" = \'train\') AS "count_train", COUNT("image"."id") FILTER (WHERE "image"."stage" = \'val\') AS "count_val", COUNT("image"."id") FILTER (WHERE "image"."stage" = \'trash\') AS "count_trash" FROM "image" WHERE "image"."commit_id" = \'333681ff-886a-42d0-b88a-5d38f1e9fe94\'::uuid', 'time': '42.140'}

另一个奇怪的事情是，如果我改变我的聚合函数

count_query = base_query.aggregate(count_train=Count('id', filter=Q(stage='train')&Q(commit=commit_uuid)),
                                           count_val=Count('id', filter=Q(stage='val')&Q(commit=commit_uuid)),
                                           count_trash=Count('id', filter=Q(stage='trash')&Q(commit=commit_uuid)))

当我这样做时，查询的速度是原来的两倍（仍然是 20 秒），当我显示 SQL 时，我看到提交的过滤器是在FILTER

所以我有两个问题：

标签： pythondjango

解决方案

1）您可以使用index_together选项添加字段索引

class Image(model.Models):
    class Meta:
         index_together = [['stage'], ['stage', 'commit']]

或indexes选项（参见https://docs.djangoproject.com/en/2.0/ref/models/options/#django.db.models.Options.indexes）

class Image(model.Models):
    class Meta:
        indexes = [models.Index(fields=['stage', 'commit'])]

2）您不需要查找id：

base_query = Image.objects.filter(commit=commit_uuid).only('stage')

# count images in stages
count = base_query.aggregate(train=Count(1, filter=Q(commit=commit_uuid) & Q(stage='train')),
                             val=Count(1, filter=Q(commit=commit_uuid) & Q(stage='val')),
                             trash=Count(1, filter=Q(commit=commit_uuid) & Q(stage='trash')))

推荐阅读