Interview question

What is the N+1 query problem in Django and how do select_related() and prefetch_related() fix it? Django में N+1 query problem क्या है और select_related() और prefetch_related() इसे कैसे fix करते हैं?

Answer

The N+1 problem occurs when fetching a list of objects triggers one additional query PER object to load related data, instead of one combined query - a common performance killer in Django apps.

# PROBLEM - N+1 queries
books = Book.objects.all()  # 1 query
for book in books:
    print(book.author.name)  # 1 EXTRA query per book, since author is a ForeignKey
# Total: 1 + N queries for N books

# FIX for ForeignKey/OneToOne - select_related() uses a SQL JOIN
books = Book.objects.select_related('author').all()  # 1 query, JOIN included
for book in books:
    print(book.author.name)  # no extra query - author data already loaded

# FIX for ManyToMany/reverse ForeignKey - prefetch_related() uses a SEPARATE query,
# then joins results in Python (JOIN doesn't work well for these relationships)
authors = Author.objects.prefetch_related('books').all()  # 2 queries total
for author in authors:
    for book in author.books.all():  # no extra query per author
        print(book.title)

# Chaining multiple select_related for nested ForeignKeys
reviews = Review.objects.select_related('book__author').all()
for review in reviews:
    print(review.book.author.name)  # both book and author preloaded, 1 query

# Combining both when needed
books = Book.objects.select_related('author').prefetch_related('reviews').all()

# Detecting N+1 issues: use django-debug-toolbar in development,
# or set 'django.db.backends' logger to DEBUG to see all executed queries
import logging
logging.basicConfig()
logging.getLogger('django.db.backends').setLevel(logging.DEBUG)

N+1 problem तब होता है जब objects की list fetch करने पर related data load करने के लिए हर object पर एक extra query चलती है - Django apps में common performance killer है।

# Problem - N+1 queries
books = Book.objects.all()  # 1 query
for book in books:
    print(book.author.name)  # हर book पर 1 extra query

# ForeignKey/OneToOne के लिए FIX - select_related() SQL JOIN use करता है
books = Book.objects.select_related('author').all()  # 1 query, JOIN के साथ
for book in books:
    print(book.author.name)  # extra query नहीं

# ManyToMany/reverse ForeignKey के लिए FIX - prefetch_related() अलग query use करता है
authors = Author.objects.prefetch_related('books').all()  # कुल 2 queries
for author in authors:
    for book in author.books.all():
        print(book.title)

# Nested ForeignKeys के लिए chaining
reviews = Review.objects.select_related('book__author').all()
for review in reviews:
    print(review.book.author.name)

# दोनों साथ combine करना
books = Book.objects.select_related('author').prefetch_related('reviews').all()

# N+1 issues detect करना - django-debug-toolbar use करें
import logging
logging.basicConfig()
logging.getLogger('django.db.backends').setLevel(logging.DEBUG)

Was this answer clear?

Subjects

Related questions