我有一个模型定义为波纹管
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/