Django ORM Optimization Tips #1 select_related

#python #django #database #sql

This is gonna be django performance series

The Best Way to Learn is to Teach

I'm a man of few words

So let's start with example of Users and Cities models.

# models.py

class City(models.Model):
    name = models.CharField(max_length=63)


class User(models.Model):
    fullname = models.CharField(max_length=127)
    city = models.ForeignKey(City, on_delete=models.SET_NULL, null=True)

And this is our serializers

# serializers.py
class CitySerializer(serializers.ModelSerializer):
    class Meta:
        model = City
        fields = ('id', 'name')


class UserSerializer(serializers.ModelSerializer):
    city = CitySerializer()

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

And this is our views

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

So if you looked to the queryset User.objects.all() its sql is

SELECT "users"."id","users"."fullname","users"."city_id" 
FROM "users"

It is not efficient because for each user you are going to access database to select user city for the nested serializer CitySerializer , It is (n+1) queries

Solution: use select_related

after changes queryset will be User.objects.select_related('city')
sql:

SELECT "user"."id","user"."fullname", "user"."city_id","city"."id",
"city"."name"  FROM "user"
LEFT OUTER JOIN "city" ON ("user"."city_id" = "city"."id")