python / intermediate
Snippet
Django QuerySet Optimization with select_related and prefetch_related
Django's N+1 query problem occurs when accessing related objects in loops, generating one query per iteration. select_related performs SQL joins for ForeignKey and OneToOne relationships, fetching related data in a single query. prefetch_related is optimized for reverse ForeignKey and ManyToMany relationships, executing a separate query and joining in Python.
snippet.py
python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
from django.db.models import Prefetchfrom .models import Author, Article, Tag# N+1 problem example - inefficientauthors = Author.objects.all()for author in authors:print(author.articles.all().count()) # New query for each author# Solution 1: select_related for ForeignKey/OneToOneauthors_optimized = Author.objects.select_related('profile').all()# Solution 2: prefetch_related for reverse ForeignKey/ManyToManyarticles = Article.objects.prefetch_related(Prefetch('tags', queryset=Tag.objects.filter(is_active=True))).all()# Solution 3: Combined optimal queryauthors_best = Author.objects.select_related('profile').prefetch_related('articles__tags').all()# Debug query countfrom django.db import connectionprint(f"Queries executed: {len(connection.queries)}")
django
Breakdown
1
from django.db.models import Prefetch
Import Prefetch to customize the queryset for prefetched relationships
2
for author in authors:
Iterating through authors causes N+1 problem - one query per author for articles
3
select_related('profile')
Uses SQL JOIN to fetch profile data in same query for ForeignKey relationship
4
Prefetch('tags', queryset=Tag.objects.filter(is_active=True))
Prefetch with custom queryset filters tags before joining, reducing data transfer
5
'articles__tags'
Nested prefetch traverses relationships: articles then their tags in optimized queries
6
from django.db import connection
Import connection to access query logs and monitor performance
7
len(connection.queries)
Check number of SQL queries executed to verify optimization effectiveness