Django REST Framework: still N+1 queries after select_related, what am I missing?

Question

One of my mentees brought this to me and now I am also stuck, which is humbling. We have a simple endpoint listing courses. Each course has a teacher (ForeignKey) and many tags (ManyToMany), and the serializer also shows how many students are enrolled.

class CourseSerializer(serializers.ModelSerializer):
    teacher = TeacherSerializer()
    tags = TagSerializer(many=True)
    student_count = serializers.SerializerMethodField()

    def get_student_count(self, obj):
        return obj.enrollments.count()

# in the view
queryset = Course.objects.select_related("teacher").all()

Django debug toolbar shows 1 query for the courses with their teachers, which is good, but then 2 extra queries per course. With 50 courses that is 101 queries. select_related is there so what is happening? Postgres 15, Django 4.2.

Arjun Mehta · Accepted Answer

select_related only follows ForeignKey and OneToOne, with a JOIN. Your 2 extra queries per row are the tags (ManyToMany) and the count. Do this: queryset = (Course.objects .select_related("teacher") .prefetch_related("tags") .annotate(student_count=Count("enrollments", distinct=True))) and change student_count to serializers.IntegerField(read_only=True). prefetch_related fetches all the tags in one extra query, and the annotate puts the count into the main query. You should land at 2 queries total, no matter how many courses.

Lin Wei · Answer

Also add assertNumQueries in a test for this endpoint so it cannot come back silently. We had same problem return after someone add one more nested serializer.

Django REST Framework: still N+1 queries after select_related, what am I missing?

Accepted answer

Grace Wanjiru

Get the best of #select-star-anonymous weekly

Comments