Reiteach Aigean Logo
Reiteach Aigeanblog.reiteachaigean.com
Admin
HomeAI UpdatesCodingCloudApps & ToolsTech NewsArticles

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.

A
Admin
Published on November 18, 2026
Django annotate vs aggregate: Performance Guide with Examples

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

Free

Free 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

Free

Free 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 WHERE clause should use an index. Check with queryset.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

Free

Free 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

  1. Need one number or a small set of numbers for the whole queryset? Use aggregate().
  2. Need a computed value on each object, or to filter or sort by it? Use annotate().
  3. Need one row per category, such as per status or per month? Use values().annotate().
  4. Need several conditional numbers? Use one aggregate() with filter=.
  5. Aggregating across two multi-valued relations? Use distinct=True carefully, or subqueries.
  6. Only need to know if something exists? Use Exists().
  7. Only using the computed value to filter? Use alias() instead of annotate().

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:

Quick Checklist

Before shipping a query that uses annotate() or aggregate(), confirm that you:

  • Chose aggregate() for totals and annotate() for per-row values
  • Combined several numbers into one query with filter=
  • Checked multiple aggregates across relations for join inflation
  • Used distinct=True or 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 ANALYZE on 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.

Share this article

Pass this technical dispatch along to fellow engineers

Did you enjoy this article?

Rate it so we know what content to bring next

Comments (0)