ORM queries & performance

The Django ORM translates Python into SQL. Used well, it produces efficient queries; used carelessly, a single page can fire hundreds of them. This lesson covers powerful query tools and how to keep them fast.

Example models: Customer, Order(customer FK related_name="orders", created_at, status, total), OrderItem(order FK related_name="items", product FK, qty, price) and Product(name, stock).

Filtering with lookups

from datetime import date
from django.db.models import Q

Order.objects.filter(created_at__date__gte=date(2026, 4, 1), status="PAID")
Customer.objects.filter(city__in=["Chennai", "Madurai"], name__istartswith="s")
Order.objects.filter(Q(status="PAID") | Q(total__gt=10000))   # OR conditions
Order.objects.exclude(customer__city="Delhi")                  # follow relations with __

QuerySets are lazy: no SQL runs until you iterate, slice with a step, call len(), list(), or evaluate in a template.

Aggregate vs annotate

aggregate() returns a single dict for the whole queryset. annotate() adds a computed value to each row.

from django.db.models import Count, F, Sum

Order.objects.aggregate(revenue=Sum("total"), orders=Count("id"))
# {'revenue': Decimal('845200.00'), 'orders': 312}

top_customers = (
    Customer.objects
    .annotate(order_count=Count("orders"), spend=Sum("orders__total"))
    .filter(spend__gt=50000)
    .order_by("-spend")[:10]
)

OrderItem.objects.annotate(line_total=F("qty") * F("price"))

F() refers to a column value in the database, which also enables race-free updates: Product.objects.filter(pk=7).update(stock=F("stock") - 1).

Be careful when annotating two different reverse relations with Count in one query — the joins multiply rows. Use Count("items", distinct=True) or subqueries.

prefetch_related for many-to-many and reverse relations

select_related joins single-valued relations. For many related objects, prefetch_related runs one extra query and stitches results together in Python.

from django.db.models import Prefetch

orders = (
    Order.objects
    .select_related("customer")
    .prefetch_related(Prefetch("items", queryset=OrderItem.objects.select_related("product")))
)
for order in orders:                     # 3 queries in total, not 1 + 2N
    print(order.customer.name, [i.product.name for i in order.items.all()])

Tool	Relation type	How
select_related	ForeignKey, OneToOne (forward)	SQL JOIN
prefetch_related	ManyToMany, reverse FK	Separate query + Python join
only() / defer()	Any	Load fewer columns
values() / values_list()	Any	Return dicts/tuples, skip model objects

Other quick wins

• exists() instead of if queryset: when you only need a yes/no.
• count() instead of len(queryset) when you don't need the rows.
• bulk_create() and bulk_update() for many rows at once.
• iterator() for streaming very large querysets.
• Add db_index=True or Meta.indexes for fields you filter on often.

Measuring

Print str(queryset.query) to see the SQL, use queryset.explain() for the plan, and install django-debug-toolbar in development to count queries per page. In tests, self.assertNumQueries(3) locks in the improvement.

Common mistakes

• Accessing order.items.all() inside a loop without prefetch_related.
• Calling .filter() on a prefetched relation inside a loop — it bypasses the prefetch cache and queries again.
• Using len(qs) just to count, loading every object into memory.