Avoid N+1 queries with select_related and prefetch_related

Approach

Use select_related for foreign keys and one-to-one fields (SQL join) and prefetch_related for reverse and many-to-many relations (one extra query per relation). Use Prefetch to filter or order the prefetched rows, and assert the query count in tests.

Solution

from django.db.models import Count, Prefetch

from shop.models import Order, OrderLine


def recent_orders():
    return (
        Order.objects.filter(status="paid")
        .select_related("customer")                      # one JOIN instead of a query per order
        .prefetch_related(
            Prefetch("lines", queryset=OrderLine.objects.select_related("product").order_by("id"))
        )
        .annotate(line_count=Count("lines"))
        .order_by("-created_at")[:50]
    )


# tests.py
from django.test import TestCase


class OrderListQueries(TestCase):
    def test_query_count_is_constant(self):
        with self.assertNumQueries(2):                   # orders + lines, however many rows
            for order in recent_orders():
                [line.product.name for line in order.lines.all()]

Pitfalls

• Calling .filter() on a prefetched relation inside a loop; it ignores the prefetch and queries again.
• select_related on many-to-many or reverse relations (not supported) or on huge joins.
• Fixing N+1 in one view but not in the serializer that runs afterwards.

Variations

• Use only()/defer() or values() to fetch fewer columns for wide tables.
• Turn on django-debug-toolbar or log connection.queries in development to spot repeated queries.

Further reading: prefetch_related