db.models.query.QuerySet.prefetch_related()

prefetch_related(*lookups)

Returns a QuerySet that will automatically retrieve, in a single batch, related objects for each of the specified lookups.

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, in addition to the foreign key and one-to-one relationships that are supported by select_related. It also supports prefetching of GenericRelation and GenericForeignKey, however, it must be restricted to a homogeneous set of results. For example, prefetching objects referenced by a GenericForeignKey is only supported if the query is restricted to one ContentType.

For example, suppose you have these models:

from django.db import models

class Topping(models.Model):
    name = models.CharField(max_length=30)

class Pizza(models.Model):
    name = models.CharField(max_length=50)
    toppings = models.ManyToManyField(Topping)

    def __str__(self):              # __unicode__ on Python 2
        return "%s (%s)" % (
            self.name,
            ", ".join(topping.name for topping in self.toppings.all()),
        )

and run:

>>> Pizza.objects.all()
["Hawaiian (ham, pineapple)", "Seafood (prawns, smoked salmon)"...

The problem with this is that every time Pizza.__str__() asks for self.toppings.all() it has to query the database, so Pizza.objects.all() will run a query on the Toppings table for every item in the Pizza QuerySet.

We can reduce to just two queries using prefetch_related:

>>> Pizza.objects.all().prefetch_related('toppings')

This implies a self.toppings.all() for each Pizza; now each time self.toppings.all() is called, instead of having to go to the database for the items, it will find them in a prefetched QuerySet cache that was populated in a single query.

That is, all the relevant toppings will have been fetched in a single query, and used to make QuerySets that have a pre-filled cache of the relevant results; these QuerySets are then used in the self.toppings.all() calls.

The additional queries in prefetch_related() are executed after the QuerySet has begun to be evaluated and the primary query has been executed.

If you have an iterable of model instances, you can prefetch related attributes on those instances using the prefetch_related_objects() function.

Note that the result cache of the primary QuerySet and all specified related objects will then be fully loaded into memory. This changes the typical behavior of QuerySets, which normally try to avoid loading all objects into memory before they are needed, even after a query has been executed in the database.

>>> pizzas = Pizza.objects.prefetch_related('toppings')
>>> [list(pizza.toppings.filter(spicy=True)) for pizza in pizzas]

You can also use the normal join syntax to do related fields of related fields. Suppose we have an additional model to the example above:

class Restaurant(models.Model):
    pizzas = models.ManyToManyField(Pizza, related_name='restaurants')
    best_pizza = models.ForeignKey(Pizza, related_name='championed_by')

The following are all legal:

>>> Restaurant.objects.prefetch_related('pizzas__toppings')

This will prefetch all pizzas belonging to restaurants, and all toppings belonging to those pizzas. This will result in a total of 3 database queries - one for the restaurants, one for the pizzas, and one for the toppings.

>>> Restaurant.objects.prefetch_related('best_pizza__toppings')

This will fetch the best pizza and all the toppings for the best pizza for each restaurant. This will be done in 3 database queries - one for the restaurants, one for the ‘best pizzas’, and one for one for the toppings.

Of course, the best_pizza relationship could also be fetched using select_related to reduce the query count to 2:

>>> Restaurant.objects.select_related('best_pizza').prefetch_related('best_pizza__toppings')

Since the prefetch is executed after the main query (which includes the joins needed by select_related), it is able to detect that the best_pizza objects have already been fetched, and it will skip fetching them again.

Chaining prefetch_related calls will accumulate the lookups that are prefetched. To clear any prefetch_related behavior, pass None as a parameter:

>>> non_prefetched = qs.prefetch_related(None)

One difference to note when using prefetch_related is that objects created by a query can be shared between the different objects that they are related to i.e. a single Python model instance can appear at more than one point in the tree of objects that are returned. This will normally happen with foreign key relationships. Typically this behavior will not be a problem, and will in fact save both memory and CPU time.

While prefetch_related supports prefetching GenericForeignKey relationships, the number of queries will depend on the data. Since a GenericForeignKey can reference data in multiple tables, one query per table referenced is needed, rather than one query for all the items. There could be additional queries on the ContentType table if the relevant rows have not already been fetched.

prefetch_related in most cases will be implemented using an SQL query that uses the ‘IN’ operator. This means that for a large QuerySet a large ‘IN’ clause could be generated, which, depending on the database, might have performance problems of its own when it comes to parsing or executing the SQL query. Always profile for your use case!

Note that if you use iterator() to run the query, prefetch_related() calls will be ignored since these two optimizations do not make sense together.

You can use the Prefetch object to further control the prefetch operation.

In its simplest form Prefetch is equivalent to the traditional string based lookups:

>>> Restaurant.objects.prefetch_related(Prefetch('pizzas__toppings'))

You can provide a custom queryset with the optional queryset argument. This can be used to change the default ordering of the queryset:

>>> Restaurant.objects.prefetch_related(
...     Prefetch('pizzas__toppings', queryset=Toppings.objects.order_by('name')))

Or to call select_related() when applicable to reduce the number of queries even further:

>>> Pizza.objects.prefetch_related(
...     Prefetch('restaurants', queryset=Restaurant.objects.select_related('best_pizza')))

You can also assign the prefetched result to a custom attribute with the optional to_attr argument. The result will be stored directly in a list.

This allows prefetching the same relation multiple times with a different QuerySet; for instance:

>>> vegetarian_pizzas = Pizza.objects.filter(vegetarian=True)
>>> Restaurant.objects.prefetch_related(
...     Prefetch('pizzas', to_attr='menu'),
...     Prefetch('pizzas', queryset=vegetarian_pizzas, to_attr='vegetarian_menu'))

Lookups created with custom to_attr can still be traversed as usual by other lookups:

>>> vegetarian_pizzas = Pizza.objects.filter(vegetarian=True)
>>> Restaurant.objects.prefetch_related(
...     Prefetch('pizzas', queryset=vegetarian_pizzas, to_attr='vegetarian_menu'),
...     'vegetarian_menu__toppings')

Using to_attr is recommended when filtering down the prefetch result as it is less ambiguous than storing a filtered result in the related manager’s cache:

>>> queryset = Pizza.objects.filter(vegetarian=True)
>>>
>>> # Recommended:
>>> restaurants = Restaurant.objects.prefetch_related(
...     Prefetch('pizzas', queryset=queryset, to_attr='vegetarian_pizzas'))
>>> vegetarian_pizzas = restaurants[0].vegetarian_pizzas
>>>
>>> # Not recommended:
>>> restaurants = Restaurant.objects.prefetch_related(
...     Prefetch('pizzas', queryset=queryset))
>>> vegetarian_pizzas = restaurants[0].pizzas.all()

Custom prefetching also works with single related relations like forward ForeignKey or OneToOneField. Generally you’ll want to use select_related() for these relations, but there are a number of cases where prefetching with a custom QuerySet is useful:

• You want to use a QuerySet that performs further prefetching on related models.
• You want to prefetch only a subset of the related objects.

• You want to use performance optimization techniques like deferred fields:

  >>> queryset = Pizza.objects.only('name')
  >>>
  >>> restaurants = Restaurant.objects.prefetch_related(
  ...     Prefetch('best_pizza', queryset=queryset))
  

>>> prefetch_related('pizzas__toppings', 'pizzas')

>>> prefetch_related('pizzas__toppings', Prefetch('pizzas', queryset=Pizza.objects.all()))

>>> prefetch_related('pizza_list__toppings', Prefetch('pizzas', to_attr='pizza_list'))