Fewer queries with the Django ORM

The setup

Take a small CRM: every lead has one owner and any number of tags.

class Owner(models.Model):
    name = models.CharField(max_length=100)


class Tag(models.Model):
    label = models.CharField(max_length=50)


class Lead(models.Model):
    name = models.CharField(max_length=100)
    owner = models.ForeignKey(Owner, on_delete=models.PROTECT)
    tags = models.ManyToManyField(Tag, blank=True)

The problem: N + 1

This loop looks innocent:

for lead in Lead.objects.all()[:50]:
    print(lead.name, lead.owner.name, [t.label for t in lead.tags.all()])

Querysets are lazy, and related objects load on first access. So Django runs one query for the leads, then one per lead for its owner, then one per lead for its tags. For 50 leads that's 1 + 50 + 50 = 101 queries. Each one is fast on its own, but together they add up, and the count grows with every row.

Seeing it

You can't fix what you can't count. With DEBUG = True, Django records every query on the connection:

from django.db import connection, reset_queries

reset_queries()
render_lead_list()
print(len(connection.queries))  # 101

In day-to-day work, django-debug-toolbar shows the same number on every page, with duplicates highlighted.

Foreign keys: select_related

select_related follows foreign keys in the same query with a SQL JOIN. The owner arrives with the lead.

Lead.objects.select_related("owner")[:50]

SELECT lead.id, lead.name, lead.owner_id, owner.id, owner.name
FROM lead
INNER JOIN owner ON lead.owner_id = owner.id
LIMIT 50;

Many-to-many: prefetch_related

A JOIN would multiply rows for many-to-many fields, so prefetch_related takes a different route: one extra query with an IN list, then Django stitches the results together in Python.

Lead.objects.select_related("owner").prefetch_related("tags")[:50]

-- query 1: leads and owners, as above
-- query 2: every tag for those 50 leads at once
SELECT tag.id, tag.label, lead_tags.lead_id
FROM tag
INNER JOIN lead_tags ON tag.id = lead_tags.tag_id
WHERE lead_tags.lead_id IN (1, 2, 3, ...);

Lock it in with a test

Performance fixes decay quietly: someone adds a field to a serializer and the extra queries come back. assertNumQueries makes that a failing test instead of a slow page.

class LeadListTests(TestCase):
    def test_list_runs_two_queries(self):
        make_leads(50)
        with self.assertNumQueries(2):
            list(lead_list_queryset())

Rules of thumb

• Following a foreign key or one-to-one forwards: select_related.
• Many-to-many, or a reverse foreign key such as owner.lead_set: prefetch_related.
• Only need a few columns: add .only() or .values().
• In Django REST Framework, put it in get_queryset() so every list and detail response benefits.

Query optimisation like this, together with caching, is most of what makes a Django page feel fast. It's usually cheaper than any new infrastructure.