Django ORM Optimization Tips #2 prefetch_related

#python #django #sql #database

This has a similar purpose to select_related, in that both are designed to stop the deluge of database queries that is caused by accessing related objects, but the strategy is quite different.

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query. However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one.

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related

lets go to our favourite part examples:

In this we have users and languages tables, each user knows more than one language and we want to list users with their languages

# models.py
class Language(models.Model):
    name = models.CharField(max_length=63)
    code = models.CharField(max_length=7)


class User(models.Model):
    fullname = models.CharField(max_length=127)
    languages = models.ManyToManyField(Language, related_name="users")

# serializers.py
class LanguageSerializer(serializers.ModelSerializer):
    class Meta:
        model = Language
        fields = ('id', 'name', 'code')


class UserSerializer(serializers.ModelSerializer):
    languages = LanguageSerializer(many=True)

    class Meta:
        model = User
        fields = ('id', 'fullname', 'languages')

# views.py
class UserListView(ListAPIView):
    serializer_class = UserSerializer
    queryset = User.objects.prefetch_related('languages')

According to prefetch_related documentation:

To avoid the much larger result set that would result from joining across a ‘many’ relationship, prefetch_related does a separate lookup for each relationship, and does the ‘joining’ in Python

So queryset User.objects.prefetch_related('languages') will be divided into 2 queries:

SELECT "user"."id", "user"."fullname" FROM "user"

SELECT "user_language"."user_id","language"."id", "language"."name",
"language"."code" 
FROM "language" INNER JOIN "user_language" 
ON ("language"."id" = "user_language"."language_id") 
WHERE "user_language"."user_id" IN (...);