Django annotate vs aggregate: Performance Guide with Examples
Understand Django ORM annotate vs aggregate performance: how each builds SQL, when to use which, avoiding join inflation, FILTER clauses, subqueries, and indexing.
Introduce
annotate() and aggregate() are two of the most useful tools in the Django ORM, and two of the most commonly confused. Both let the database do calculations such as counts, sums, and averages instead of looping in Python. But they produce very different SQL, return very different shapes of data, and have very different performance traps.
Pick the wrong one and you may load thousands of rows you did not need. Combine them carelessly and you can get totals that are silently wrong. This guide explains how each works, compares their performance, and covers the mistakes that matter most on large tables.
The Core Difference in One Minute
aggregate()collapses the whole queryset into one summary result. It returns a dictionary, not a queryset, so it is a terminal operation.annotate()adds a computed value to every object in the queryset. It returns a queryset, so you can keep filtering, ordering, and chaining.
Using these models for a small shop:
class Customer(models.Model):
name = models.CharField(max_length=100)
class Order(models.Model):
customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name="orders")
status = models.CharField(max_length=20)
total = models.DecimalField(max_digits=10, decimal_places=2)
created_at = models.DateTimeField(auto_now_add=True)
class OrderItem(models.Model):
order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name="items")
quantity = models.PositiveIntegerField()
aggregate() answers "what is the total across everything?":
from django.db.models import Sum, Avg, Count
Order.objects.filter(status="paid").aggregate(
revenue=Sum("total"),
average=Avg("total"),
orders=Count("id"),
)
## {'revenue': Decimal('1250000.00'), 'average': Decimal('83.33'), 'orders': 15000}
annotate() answers "what is the value for each customer?":
customers = Customer.objects.annotate(
order_count=Count("orders"),
total_spent=Sum("orders__total"),
)
for c in customers:
print(c.name, c.order_count, c.total_spent)
A good way to remember it: aggregate summarizes the queryset; annotate decorates each row.
What the SQL Looks Like
Understanding the SQL explains most of the performance differences.
aggregate() produces a single query that returns exactly one row:
SELECT SUM(total), AVG(total), COUNT(id)
FROM orders_order
WHERE status = 'paid';
The result size is tiny, no matter how large the table is. The cost is in how much data the database has to scan to compute it.
annotate() with an aggregate function adds a GROUP BY and returns one row per object:
SELECT customer.id, customer.name,
COUNT(order.id) AS order_count,
SUM(order.total) AS total_spent
FROM customers_customer customer
LEFT OUTER JOIN orders_order order ON order.customer_id = customer.id
GROUP BY customer.id, customer.name;
Here the cost has two parts: the work of joining and grouping, and the size of the result, which grows with the number of customers. On a table with a million customers, that is a million rows coming back to Python if you do not limit them.
Performance Comparison
| aggregate() | annotate() | |
|---|---|---|
| Returns | A dictionary, one row | A queryset, one row per object |
| Result size | Always tiny | Grows with the number of rows |
| SQL | Aggregate functions over the filtered set | Often JOIN plus GROUP BY |
| Main cost | Scanning and computing | Join, grouping, sorting, and transferring rows |
| Chainable | No, it ends the queryset | Yes |
| Typical use | Dashboards, totals, reports | Per-row values, filtering and ordering by computed data |
Neither is "faster" in general. They answer different questions. The performance question is whether you are asking the database the right one, and whether you are doing it in one query instead of many.
Free tech newsletter
FreeFree to join — no card, no paywall. Weekly AI, cloud & tooling insights.
The Biggest Win: Replace Python Loops and N+1 Queries
Both tools shine when they replace work done in Python.
## Slow: loads every order, sums in Python
total = sum(o.total for o in Order.objects.filter(status="paid"))
## Fast: one query, one row back
total = Order.objects.filter(status="paid").aggregate(t=Sum("total"))["t"]
## Slow: one COUNT query per customer (N+1)
for c in Customer.objects.all():
print(c.name, c.orders.count())
## Fast: one query for everyone
for c in Customer.objects.annotate(order_count=Count("orders")):
print(c.name, c.order_count)
This is the same family of problem as the N+1 issues that select_related() and prefetch_related() solve. If you want to see how those two tools differ and when to use each, read the guide on select_related vs prefetch_related in the related reading section below.
Performance Trap 1: Join Inflation with Multiple Aggregates
This is the most dangerous annotate mistake, because it gives you wrong numbers without any error.
Customer.objects.annotate(
order_count=Count("orders"),
item_count=Count("orders__items"),
)
Two aggregations across different multi-valued relations create a JOIN where each order row is repeated once per item. The counts multiply, so order_count can come back far too high.
Fix 1: use distinct=True where it is correct to do so:
Customer.objects.annotate(
order_count=Count("orders", distinct=True),
item_count=Count("orders__items"),
)
distinct=True works for counts, and it is also fine for Sum of a column that is unique per group, but it can be slow on big data because the database must de-duplicate. And it is not safe for Sum or Avg when values can legitimately repeat, because it would drop duplicate values, not duplicate rows.
Fix 2: use a subquery, which avoids the multiplication entirely:
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),
)
The subquery computes the item count separately, so it cannot inflate the order count. It is more code, but it is correct and often faster on large tables.
Performance Trap 2: Several Queries Where One Would Do
If you need several numbers, ask for them in a single aggregate() call instead of running one query per number.
## Three queries, three scans
paid = Order.objects.filter(status="paid").count()
refunded = Order.objects.filter(status="refunded").count()
revenue = Order.objects.filter(status="paid").aggregate(t=Sum("total"))["t"]
Use conditional aggregation with the filter argument. On PostgreSQL, this becomes the efficient FILTER (WHERE ...) clause:
from django.db.models import Q
stats = Order.objects.aggregate(
paid=Count("id", filter=Q(status="paid")),
refunded=Count("id", filter=Q(status="refunded")),
revenue=Sum("total", filter=Q(status="paid")),
)
One table scan answers all three questions. The same filter= argument works in annotate() for per-row conditional counts:
Customer.objects.annotate(
paid_orders=Count("orders", filter=Q(orders__status="paid")),
)
This is usually better than filtering the queryset first, because filtering with .filter(orders__status="paid") before annotating also removes customers who have no paid orders.
Free tech newsletter
FreeFree to join — no card, no paywall. Weekly AI, cloud & tooling insights.
Performance Trap 3: Group By with values()
To group by a column instead of by object, use values() before annotate():
Order.objects.values("status").annotate(n=Count("id"), revenue=Sum("total"))
This returns one row per status, not one row per order. Order matters here. values() before annotate() groups by those fields. annotate() before values() annotates each object first, which is a different query.
One detail that surprises many people: if your model has a default Meta.ordering, Django adds those columns to the GROUP BY, which can break your grouping and slow the query. Clear it with an empty order_by():
Order.objects.order_by().values("status").annotate(n=Count("id"))
Performance Trap 4: Annotations You Only Use for Filtering or Ordering
Every annotation is added to the SELECT list. If you only need the computed value to filter or sort, use alias() (available since Django 3.2) instead:
Customer.objects.alias(order_count=Count("orders")).filter(order_count__gt=10)
alias() computes the value for use in the query but does not return it as a column, so you avoid transferring data you never read.
Related to that, filtering on an annotation becomes a HAVING clause, which runs after grouping. Filter on regular columns before annotating whenever you can, so the database groups fewer rows:
## Better: reduce rows first, then group
Customer.objects.filter(is_active=True).annotate(order_count=Count("orders"))
Performance Trap 5: Counting and Aggregating Huge Tables
On very large tables, even a correct aggregate() can be slow. PostgreSQL's COUNT(*) and SUM must scan many rows, because of how its storage model works.
What helps:
- Indexes on the filter columns. The
WHEREclause should use an index. Check withqueryset.explain(analyze=True). - Narrower scans. Filter by date range or status to reduce the rows examined.
- Caching. If a dashboard total can be a minute or an hour old, cache the result.
- Pre-computed summaries. A summary table or a materialized view, refreshed on a schedule, turns a heavy aggregation into a simple read.
- Estimates. For an approximate total, such as a count shown in a UI, an estimate from PostgreSQL's statistics can be good enough.
Remember that .count() on an annotated queryset can wrap the whole thing in a subquery, which is slower than counting the base table. Count without the annotations when you can.
Performance Trap 6: Existence Checks Disguised as Counts
If you only need to know whether related rows exist, do not count them:
## Slower: computes a count for every customer
Customer.objects.annotate(n=Count("orders")).filter(n__gt=0)
## Faster: can stop at the first match
from django.db.models import Exists, OuterRef
Customer.objects.filter(Exists(Order.objects.filter(customer=OuterRef("pk"))))
Exists() lets the database stop at the first matching row instead of counting them all.
Using annotate and aggregate Together
You can aggregate over an annotation, which is useful for "average of per-customer totals" type questions:
Free tech newsletter
FreeFree to join — no card, no paywall. Weekly AI, cloud & tooling insights.
from django.db.models import Avg, Sum
per_customer = Customer.objects.annotate(spent=Sum("orders__total"))
per_customer.aggregate(avg_spent=Avg("spent"))
Django wraps the annotated query in a subquery. This works well, but check the generated SQL and plan on large tables, because the inner grouping still runs over the full set.
When to Use Which: A Decision Guide
- Need one number or a small set of numbers for the whole queryset? Use
aggregate(). - Need a computed value on each object, or to filter or sort by it? Use
annotate(). - Need one row per category, such as per status or per month? Use
values().annotate(). - Need several conditional numbers? Use one
aggregate()withfilter=. - Aggregating across two multi-valued relations? Use
distinct=Truecarefully, or subqueries. - Only need to know if something exists? Use
Exists(). - Only using the computed value to filter? Use
alias()instead ofannotate().
Always Verify
Print the SQL and the execution plan before trusting a change:
qs = Customer.objects.annotate(order_count=Count("orders"))
print(qs.query)
print(qs.explain(analyze=True))
Look for sequential scans on large tables, large sorts, and the number of rows each step handles. In tests, assertNumQueries locks in the query count so a later change cannot quietly bring N+1 back.
Related Reading: Go Deeper on Query Performance
Aggregation is one part of keeping the ORM fast. These guides cover the rest:
- If your per-row counts still run hundreds of queries: select_related vs prefetch_related: Practical Examples shows how to load related data efficiently when you need the objects themselves, not just a number.
- If your aggregations are slow on big tables: Django ORM Optimization for Millions of Records covers indexes,
EXPLAIN ANALYZE, and pagination, which decide how fast a largeGROUP BYreally runs. - If annotated columns slow down your admin lists: How to Fix Slow Django Admin Pages shows how to move per-row calculations into the queryset with annotations.
- If you export aggregated results to files: How to Export Large CSV Files in Django Without Memory Errors explains how to stream big result sets instead of loading them all at once.
- If heavy reports need to run in the background: How to Process 5 Million Records with Django and Celery shows how to split expensive calculations into chunks that run safely in workers.
Quick Checklist
Before shipping a query that uses annotate() or aggregate(), confirm that you:
- Chose
aggregate()for totals andannotate()for per-row values - Combined several numbers into one query with
filter= - Checked multiple aggregates across relations for join inflation
- Used
distinct=Trueor subqueries where needed, and verified the numbers - Filtered before annotating, and used
alias()for filter-only values - Cleared default ordering with
order_by()before grouping - Used
Exists()instead of counts for existence checks - Looked at the SQL and
EXPLAIN ANALYZEon realistic data - Indexed the filter columns, and considered caching for heavy dashboards
Conclusion
aggregate() and annotate() are not rivals. One summarizes a whole queryset into a small result, and the other attaches a calculation to every row. The performance difference comes from what each asks the database to do and how much data comes back.
Use aggregate() for summaries, annotate() for per-row values, values().annotate() for grouping, and Exists() for checks. Watch for join inflation, keep the result sets small, filter early, and always check the SQL. Do that, and the database does the heavy lifting while your Python code stays simple and fast.
Frequently Asked Questions
What is the difference between annotate and aggregate in Django?
aggregate() returns a single dictionary of summary values for the entire queryset and ends the queryset chain. annotate() adds a computed value to every object in the queryset and returns a queryset you can keep filtering and ordering.
Which is faster, annotate or aggregate?
Neither is faster in general, because they answer different questions. aggregate() returns one tiny row, but still scans the data it summarizes. annotate() with an aggregate function groups the data and returns one row per object, so cost also depends on the result size.
Why are my annotate counts wrong when I use two Count fields?
Counting across two multi-valued relations in one query multiplies rows through the JOIN, which inflates the numbers. Use distinct=True for counts, or compute one of them with a subquery.
Can I use annotate and aggregate together?
Yes. You can call aggregate() over an annotated queryset, for example to average per-customer totals. Django wraps the annotated query in a subquery, so check the SQL and performance on large tables.
What is the difference between annotate and alias?
Both compute a value for the query, but annotate() also returns it as an attribute on each object, while alias() does not. Use alias() when you only need the value for filtering or ordering, to avoid selecting data you will not use.
How do I get one aggregate result per group in Django?
Call values() on the grouping field first, then annotate(). For example, Order.objects.order_by().values("status").annotate(n=Count("id")) returns one row per status.
