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?

Back to Top