Django: annotate a model with multiple counts is super slow

I'm trying to annotate a model that has multiple relationships, with multiple counts of those relationships. But the query is super slow.

Campaign.objects.annotate(
    num_characters=Count("character", distinct=True),
    num_factions=Count("faction", distinct=True),
    num_locations=Count("location", distinct=True),
    num_quests=Count("quest", distinct=True),
    num_loot=Count("loot", distinct=True),
    num_entries=Count("entry", distinct=True),
)

When I mean super slow, I mean it: it takes multiple minutes on my local MacBook Pro with the M1 Max 😰 And there aren't even that many rows in these tables.

If I simply fetch all campaigns, loop over them, and then get the counts of all these related objects in separate queries, it's a LOT faster:

campaigns = Campaign.objects.all()
for campaign in campaigns:
    campaign.num_characters = campaign.character_set.count()
    campaign.num_factions = campaign.faction_set.count()
    campaign.num_locations = campaign.location_set.count()
    campaign.num_quests = campaign.quest_set.count()
    campaign.num_loot = campaign.loot_set.count()
    campaign.num_entries = campaign.entry_set.count()

But this is doing a lot of queries of course, which isn't ideal either. Can't this query be optimized somehow?

While a bit ugly you should be able to speed up the query by using subqueries instead

from django.db.models import OuterRef, Subquery, Count
Campaign.objects.annotate(
    num_characters=Subquery(Character.objects.filter(campaign=OuterRef('pk')).order_by().values('campaign').annotate(count=Count('campaign')).values('count')),
    num_factions=Subquery(Faction.objects.filter(campaign_id=OuterRef('pk')).order_by().values('campaign').annotate(count=Count('campaign')).values('count')),
    num_locations=Subquery(Location.objects.filter(campaign_id=OuterRef('pk')).order_by().values('campaign').annotate(count=Count('campaign')).values('count')),
    num_quests=Subquery(Quest.objects.filter(campaign_id=OuterRef('pk')).order_by().values('campaign').annotate(count=Count('campaign')).values('count')),
    num_loot=Subquery(Loot.objects.filter(campaign_id=OuterRef('pk')).order_by().values('campaign').annotate(count=Count('campaign')).values('count')),
    num_entries=Subquery(Entry.objects.filter(campaign_id=OuterRef('pk')).order_by().values('campaign').annotate(count=Count('campaign')).values('count')),
)
Back to Top