Cutting database queries by not referencing Foreign Keys. Or fixing bad behavior.

Photo by israel palacio on Unsplash

So you have a model with foreign key:

from django.db import models
from .models import Author

class Book(models.Model):
    name = models.CharField(...)
    author = models.ForeignKey(to=Author,...)

Now you want to see an author in your admin panel. Easy, right? Just add:

def __str__(self):
    return f'{self.id} {self.author.name}'

What this will do is hit our database for an author name for every row present on a page. Imagine there are 100 rows. That's 100 more hits. This problem is known as the n+1 query.

The solution is quite simple. If that's a ForeignKey, you need to use select_related on your queryset. If that's a ManyToMany field or reverse FK, then you need a prefetch_related method. And, by the way, that's the difference between those two methods. It's a common interview question.

But in our case it's not a simple query. If you really want to fix that you would need to dig deeper into how Django admin works and provide custom queryset to the view.

When I encountered this problem the solution that has been accepted was just removing the reference to ForeingKey from the __str__ method ¯\_(ツ)_/¯

A little ToDo for myself: find a way to solve this via select_related.

UPDATE after half a year

For some time I didn't face this problem but not long ago I did. So, as I said, the solution to this is quite simple. All we need to do is to redefine get_queryset method on admin model. For example:

@admin.register(models.Book)
class BookAdmin(admin.ModelAdmin):
    def get_queryset(self, request):
        qs = self.model._default_manager.select_related(
            'author',
        )
        # This stuff is marked as TODO in Django 3.1
        # so it might change in the future
        ordering = self.get_ordering(request)
        if ordering:
            qs = qs.order_by(*ordering)

        return qs

Now there will be just one query per record, containing JOIN clause, hitting our database.

Optimization FTW 😏