Get count of related model efficiently in Django

Question

I have a situation something like this (the actual code is bound up in a template, and omitted for brevity).

threads = Thread.objects.all()
for thread in threads:
    print(thread.comments.count())
    print(thread.upvotes.count())

I've managed to considerably reduce the total number of queries using Django's awesome prefetch_related method.

threads = Thread.objects.prefetch_related('comments').prefetch_related('upvotes')

However I'm wondering if this situation could be further optimized. From what I understand prefetch_related retrieves all of the data associated with the related models. Seeing as I only care about the amount of related models, and not about the models themselves, it seems like this query could be optimized further so that it doesn't retrieve a bunch of unnecessary data. Is there a way to do this in Django without dropping down to raw SQL?

Answer 1

You're right, it's wasteful to fetch all that data from the database if all you want to do is get the count. I suggest annotation:

threads = (Thread.objects.annotate(Count('comments', distinct=True))
                         .annotate(Count('upvotes', distinct=True)))
for thread in threads:
    print(thread.comments__count)
    print(thread.upvotes__count)

See the annotation documentation for more information.