Annotate performance Django
I've got the following models:
class Match(models.Model):
objects = BulkUpdateOrCreateQuerySet.as_manager()
id = models.AutoField(primary_key=True)
betsapi_id = models.IntegerField(unique=True, null=False)
competition:Competition = models.ForeignKey(Competition, on_delete=models.CASCADE, related_name='matches')
season:Season = models.ForeignKey(Season, on_delete=models.CASCADE, related_name='season_matches', null=True, default=None)
home:Team = models.ForeignKey(Team, on_delete=models.CASCADE, related_name='home_matches')
away:Team = models.ForeignKey(Team, on_delete=models.CASCADE, related_name='away_matches')
minute = models.IntegerField(default=None, null=True)
period = models.CharField(max_length=25, default=None, null=True)
datetime = models.DateTimeField()
status = models.IntegerField()
opta_match = models.OneToOneField(OptaMatch, on_delete=models.CASCADE, related_name='match', default=None, null=True)
updated_at = models.DateTimeField(auto_now=True)
created_at = models.DateTimeField(auto_now_add=True)
class Season(models.Model):
id = models.AutoField(primary_key=True)
competition:Competition = models.ForeignKey(to=Competition, on_delete=models.CASCADE, null=False, blank=False, related_name='seasons')
start_date = models.DateTimeField(blank=False, null=False)
end_date = models.DateTimeField(blank=False, null=False)
name = models.CharField(max_length=255, null=True, default=None)
updated_at = models.DateTimeField(auto_now=True)
created_at = models.DateTimeField(auto_now_add=True)
class Event(models.Model):
objects = BulkUpdateOrCreateQuerySet.as_manager()
id = models.AutoField(primary_key=True)
betsapi_id = models.IntegerField(unique=True)
name = models.CharField(max_length=255)
minute = models.IntegerField()
player_name = models.CharField(max_length=255, null=True, default=None)
extra_player_name = models.CharField(max_length=255, null=True, default=None)
period = models.CharField(max_length=255)
team:Team = models.ForeignKey(Team, on_delete=models.CASCADE, related_name='events')
match:Match = models.ForeignKey(Match, on_delete=models.CASCADE, related_name='events')
updated_at = models.DateTimeField(auto_now=True)
created_at = models.DateTimeField(auto_now_add=True)
And I wrote the following annotations to a Match object:
all_matches_qs = (Match
.objects
.filter(status=1)
.select_related('home', 'away', 'competition', 'competition__country', 'season', 'opta_match')
.prefetch_related(
Prefetch("season", queryset=Season.objects.prefetch_related(
Prefetch("season_matches", queryset=Match.objects.prefetch_related(
Prefetch('statistics')))
)),
Prefetch('events', queryset=Event.objects.select_related("team", "match").filter(name__in=["Corner", "Goal", "Substitution", "Yellow Card", "Red Card"])),
)
.annotate(season_statistics_count=Count('season__season_matches__statistics'))
.annotate(goals=Count('events', distinct=True, filter=(Q(events__name='Goal') & Q(events__team=F('home')))))
)
Executing this on about 25 records takes me about 3.75 seconds whereas if I remove one of the annotations (doesn't matter which one) the time to execute drops to about 0.5 seconds.
Also, removing the filter(status=1)
seems to be dropping the time to execute to near 0.3s.
I'm wondering what is causing this issue and how to ensure I can execute both annotations AND the filter without a drastic performance drop?