Django duplicate database queries

Question

I am using django_debug_toolbar for analyzing performance of web page. What confuses me in the results is the database queries. No matter I did everything as it should be (I suppose), results tab still shows duplicate database queries warning. For illustrating this problem, I set out django project as simple as below:

models.py

from django.db import models


class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField('date published', auto_now_add=True)


class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

views.py

from django.shortcuts import render

from .models import Question, Choice


def index(request):
    return render(request, 'polls/index.html', {'questions': Question.objects.all()})

index.html

{% for question in questions %}
  <p>{{ question.question_text }}</p>
  <ul>
    {% for choice in question.choice_set.all %}
      <li>{{ choice.votes }} votes - {{ choice.choice_text }}</li>
    {% endfor %}
  </ul>
{% endfor %}

In the above html file and view, I load all questions and their related choices. For testing, I added only 2 question and 2 and 4 choices for them respectively (6 choices in total). And django_debug_toolbar SQL result are as below:

What should I do for avoiding these duplicate SQL queries? I think that these duplicated query may have serious impacts on performance for big websites. What is your approach and best practices to avoid these issues in general?

Answer 1

You should .prefetch_related(..) [Django-doc] the related Choices. Then Django will make an extra query to fetch the Choices all at once, and do the JOIN at the Python/Django level.

def index(request):
    return render(
        request,
        'polls/index.html',
        {'questions': Question.objects.prefetch_related('choice_set')}
    )