我有一个模型定义为波纹管

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秒,当我尝试在外壳中查看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内部完成的

所以我有两个问题:


我可以采取其他措施来提高查询速度吗?还是应该将计数存储在某个位置并在每次更改图像时更改值?
我期望查询首先在提交ID上过滤,然后在stage上过滤,但我感觉它反过来完成了

最佳答案

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

关于python - Django聚合花费很多时间,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51039538/

10-12 12:42
查看更多