DEV Community

Cover image for Django annotate() vs aggregate(): Performance Traps and Fixes
sizan mahmud0
sizan mahmud0

Posted on

Django annotate() vs aggregate(): Performance Traps and Fixes

aggregate() and annotate() both push calculations into the database, but they answer different questions.

The difference

aggregate() annotate()
Returns A dictionary, one row A queryset, one row per object
Chainable No Yes
Typical use Totals, dashboards Per-row values, filter/order by computed data
from django.db.models import Count, Sum, Avg

# aggregate: one summary
Order.objects.filter(status="paid").aggregate(
    revenue=Sum("total"), average=Avg("total"), orders=Count("id")
)

# annotate: one value per customer
Customer.objects.annotate(order_count=Count("orders"), total_spent=Sum("orders__total"))
Enter fullscreen mode Exit fullscreen mode

Replace loops and N+1

# Slow
total = sum(o.total for o in Order.objects.filter(status="paid"))

# Fast
total = Order.objects.filter(status="paid").aggregate(t=Sum("total"))["t"]
Enter fullscreen mode Exit fullscreen mode

Trap 1: join inflation (silent wrong numbers)

Customer.objects.annotate(
    order_count=Count("orders"),
    item_count=Count("orders__items"),
)
Enter fullscreen mode Exit fullscreen mode

Two aggregates across different multi-valued relations multiply rows. Fixes:

# distinct=True for counts
Customer.objects.annotate(
    order_count=Count("orders", distinct=True),
    item_count=Count("orders__items"),
)
Enter fullscreen mode Exit fullscreen mode

Or use a Subquery for one of them:

from django.db.models import Subquery, OuterRef, Count, IntegerField
from django.db.models.functions import Coalesce

item_counts = (
    OrderItem.objects
    .filter(order__customer=OuterRef("pk"))
    .order_by()
    .values("order__customer")
    .annotate(c=Count("id"))
    .values("c")
)

customers = Customer.objects.annotate(
    order_count=Count("orders"),
    item_count=Coalesce(Subquery(item_counts, output_field=IntegerField()), 0),
)
Enter fullscreen mode Exit fullscreen mode

Don't use distinct=True with Sum or Avg: it de-duplicates values, not rows.

Trap 2: one query per number

from django.db.models import Q

Order.objects.aggregate(
    paid=Count("id", filter=Q(status="paid")),
    refunded=Count("id", filter=Q(status="refunded")),
    revenue=Sum("total", filter=Q(status="paid")),
)
Enter fullscreen mode Exit fullscreen mode

One scan, three answers.

Trap 3: group by with default ordering

Order.objects.order_by().values("status").annotate(n=Count("id"))
Enter fullscreen mode Exit fullscreen mode

values() before annotate() groups by those fields. A default Meta.ordering leaks into GROUP BY unless you clear it.

Trap 4: annotating just to filter

Customer.objects.alias(order_count=Count("orders")).filter(order_count__gt=10)
Enter fullscreen mode Exit fullscreen mode

alias() (Django 3.2+) computes the value without selecting it. Also filter on plain columns before annotating so fewer rows get grouped.

Trap 5: counts used as existence checks

from django.db.models import Exists, OuterRef

Customer.objects.filter(Exists(Order.objects.filter(customer=OuterRef("pk"))))
Enter fullscreen mode Exit fullscreen mode

Big tables

  • Index filter columns and check qs.explain(analyze=True)
  • Cache totals that can be slightly stale
  • Use summary tables or materialized views for heavy reports
  • Count without annotations when you can, since .count() on an annotated queryset may wrap it in a subquery

Full guide

The complete post covers aggregating over annotations, a decision guide, a checklist, and an FAQ:

👉 https://blog.reiteachaigean.com/blog/django-orm-annotate-vs-aggregate-performance

Related reads:

What's the nastiest ORM aggregation bug you've hit?

Top comments (0)