select_related and prefetch_related

Query optimization when working with related objects.

The N+1 Problem

# Bad: 1 query for tasks + N queries for each project
tasks = Task.objects.all()
for task in tasks:
    print(task.project.name)  # a new SQL query every time!

select_related — JOIN for ForeignKey/OneToOne

# Good: one query with JOIN
tasks = Task.objects.select_related('project', 'owner')
for task in tasks:
    print(task.project.name)  # already loaded
    print(task.owner.username)  # already loaded

Works with: ForeignKey, OneToOneField.

prefetch_related — Separate Query for ManyToMany

# Two queries: tasks + all tags
tasks = Task.objects.prefetch_related('tags')
for task in tasks:
    for tag in task.tags.all():  # already loaded
        print(tag.name)

Works with: ManyToManyField, reverse ForeignKey via related_name.

Combining Both

tasks = Task.objects.select_related('project', 'owner').prefetch_related('tags')

Prefetch with Filtering

from django.db.models import Prefetch

projects = Project.objects.prefetch_related(
    Prefetch(
        'tasks',
        queryset=Task.objects.filter(status='todo').order_by('priority'),
        to_attr='todo_tasks',  # store in attribute
    )
)

for project in projects:
    print(project.todo_tasks)  # list, not a queryset

When to Use Which