In django, is aggregate(Count()) faster or better than .count() in anyway?

Question

Django annotations is great for average, min/max, etc. It also does Count. So does that generate the same SQL as if I used the older .count() on the queryset? Or does it generate more efficient SQL in some cases? Or worse SQL?

Sorry, to clarify, I meant to compare the count() operation against something like aggregate(Count('id')) where id is the PK of the table.

So with that, I believe Brian has the correct answer. In short, count() is simply a special case of aggregate().

Answer 1

Calling the queryset's .count() method ultimately calls Count().

Specifically: django.db.models.QuerySet.count() calls django.db.models.sql.Query.get_count(), which calls django.db.models.sql.Query.add_count_column(), which adds django.db.models.sql.aggregates.Count to the query.

The main difference between the two is that when you use Count directly, you specify the fields you want to count, whereas when you call .count() on the queryset, this will result in SELECT COUNT(*)... (except when you also use distinct() or when you limit the fields in the select clause, in which case it's more complicated).

Answer 2

Apples and oranges. .count() does a SQL count on the current queryset. The Count aggregate, however, runs a count on relationships you specify on the queryset.

Pizza.objects.count() # Total amount of pizzas

Pizza.objects.aggregate(topping_count=Count('toppings')) # Total amount of toppings associated to a pizza

Pizza.objects.annotate(topping_count=Count('toppings')) # Total amount of toppings on each pizza