DEV Community

Cover image for Differences between select_related and prefetch_related in Django
Eduardo Zepeda
Eduardo Zepeda

Posted on • Originally published at coffeebytes.dev on

Differences between select_related and prefetch_related in Django

Image credits to ときわた

The select_related and prefetch_related methods are used to reduce the number of queries made to the database. This translates into response time for each view. In addition, using these methods is one of the actions to implement to improve the performance of a Django application

Just remember that there are better things to do for your app than to obsess about its performance.

select_related

The select_related method is used to follow a relationship of type ForeignKey or OneToOneField to the respective objects it points to and obtain them..

When using select_related we will have a longer query, however, the advantage is that it will no longer be necessary to access the database again to obtain the objects of the related model.

Simplified diagram of how Django select_related works

Simplified diagram of how select_related works

Consider this example:

from django.db import models

class Main(models.Model):
    name = models.CharField(max_length=256)

class Derivative(models.Model):
    name = models.CharField(max_length=256)
    main = models.ForeignKey(
        "Main", related_name="derivatives", on_delete=models.CASCADE
    )
Enter fullscreen mode Exit fullscreen mode

If we try to access the object pointed to by the Foreign Key relationship, a new database query will be generated. select_related avoids that extra query for each object.

{% for object in queryset %}
    <p>{{object.name}}</p>
    <small>{{object.main.name}}</small>
{% endfor %}
Enter fullscreen mode Exit fullscreen mode

For example, if we have three Derived objects related to a single main object:

  • A main query that retrieves all objects Derivative
  • Three queries, exactly the same, one for each time we access the main object from the Derived object.

Use in a query

To use select_related we call it from our query, passing it the name of the field that corresponds to our relationship with the other model.

Derivative.objects.select_related("main")
Enter fullscreen mode Exit fullscreen mode

Internal operation of select_related

How select_related works internally, select_related replaces multiple queries being performed by a single INNER JOIN at the database level:

SELECT "my_app_derivative"."id",
       "my_app_derivative"."name",
       "my_app_derivative"."main_id"
  FROM "my_app_derivative"

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 WHERE "my_app_main"."id" = '1'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 WHERE "my_app_main"."id" = '1'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 WHERE "my_app_main"."id" = '1'
Enter fullscreen mode Exit fullscreen mode

This reduces multiple SQL queries to a single, longer query.

SELECT "my_app_derivative"."id",
       "my_app_derivative"."name",
       "my_app_derivative"."main_id",
       "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_derivative"
 INNER JOIN "my_app_main"
    ON ("my_app_derivative"."main_id" = "my_app_main"."id")
Enter fullscreen mode Exit fullscreen mode

prefetch_related

If the select_related method retrieves a single object from a single relationship field, the prefetch_related method is used when we have a multiple relationship with another model , i.e. a relationship of type **ManyToMany or a reverse ForeignKey.

Simplified diagram of how Django prefetch_related works

Simplified diagram of how prefetch_related works

Consider this example, note the ManyToManyField field towards the Principal model.

from django.db import models

class Main(models.Model):
    name = models.CharField(max_length=256)

class ManyToManyModel(models.Model):
    name = models.CharField(max_length=256)
    ManyToManyRel = models.ManyToManyField("Main", related_name="multiples")
Enter fullscreen mode Exit fullscreen mode

If we access the field that represents the multiple relation of our object, without using prefetch_related, we will be impacting the database with a new query.

{% for object in queryset %}
    <p>{{object.name}}</p>
    {% for main in object.ManyToManyRel.all %}
      <!-- New query each iteration -->
      <p><small>{{main.name}}</small></p>
    {% endfor %}
{% endfor %}
Enter fullscreen mode Exit fullscreen mode

Use in a query

To use the prefetch_related method call it at the end of our query, choosing the field that represents the many-to-many relationship in our object.

queryset = ManyToManyModel.objects.prefetch_related("ManyToManyRel")
Enter fullscreen mode Exit fullscreen mode

Inner workings of prefetch_related

How does prefecth_related work internally? The prefetch_related method replaces the multiple SQL queries by only 2 SQL queries: one for the main query and the other for the related objects, then it will join the data using Python.

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '1'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '2'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '3'

SELECT "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" = '4'
Enter fullscreen mode Exit fullscreen mode

The multiple queries above are reduced to only 2 SQL queries.

SELECT "my_app_manytomanyrel"."id",
       "my_app_manytomanyrel"."name"
  FROM "my_app_manytomanyrel"

SELECT ("my_app_manytomanyrel_main"."manytomanyrel_id") AS "*prefetch_related*val_manytomanyrel_id",
       "my_app_main"."id",
       "my_app_main"."name"
  FROM "my_app_main"
 INNER JOIN "my_app_manytomanyrel_main"
    ON ("my_app_main"."id" = "my_app_manytomanyrel_main"."main_id")
 WHERE "my_app_manytomanyrel_main"."manytomanyrel_id" IN ('1', '2', '3', '4')
Enter fullscreen mode Exit fullscreen mode

Other related resources

Top comments (0)