Jurrian Tromp
Deep into Django

Deep into Django

Prefetching in Django

Prefetching in Django

Image (Daniel Bonilla): Casa Batlló by Gaudí in Barcelona, one of my favorite masterpieces in architecture.

Jurrian Tromp's photo
Jurrian Tromp
·Apr 6, 2022·

2 min read

A lot has already been written about prefetching, as it is one of the best ways to improve performance for slow admin pages. I'd like to share some tricks to be able to work with large amounts of rows and inlines.

Using prefetch_related and select_related

The best way to reduce the amount of queries by inlines is to use prefetch_related, this will preload the things in a few queries. Prefetch_related will do the joining in Python, select_related will actually create SQL joins. Depending on your use-case the one may be faster than the other, the best way to find out is to experiment. I usually go for prefetch_related or a combination, unless the number of rows gets too big. Another thing that will cause you headaches is dynamic attributes. In your admin list page this will quickly cause a lot of queries.

In this example list_display will need to get the name two relations deep causing two joins. By prefetching these relations, Django already populates the instances with the preloaded data.

class SomeAdmin(admin.ModelAdmin):
    list_display = ('supplier',)

    def get_queryset(self, request):
        return (
            super().get_queryset(request)
            .select_related('article__product')
            .prefetch_related('article__supplier')
        )

    def supplier(self, obj):
        return obj.article.supplier.name

Annotating using Subquery

But what if you want to show an aggregation? Using annotate you can prevent multiple queries with a Subquery. This ensures that each row will have a value that comes from one single SQL query:

class SomeAdmin(admin.ModelAdmin):
    list_display = ('sales_price',)

    def get_queryset(self, request):
        sales_price_subquery = Subquery(
            SalesPrice.objects
            .filter(
                kiosk_id=OuterRef('kiosk_id'),
                article_id=OuterRef('article_id'),
            )
            .values('price')
        )

        return (
            super().get_queryset(request)
            .annotate(sales_price=sales_price_subquery)
        )

    def sales_price(self, obj):
        return obj.sales_price

Bonus tip: enable ordering

If you use dynamic attributes in your admin, you will quickly notice you won't be able to sort on them. Using annotate and F-expression you can use an attribute from another model and sort on it using admin_order_field:

class SomeAdmin(admin.ModelAdmin):
    list_display = ('article_number',)

    def get_queryset(self, request):
        return (
            super().get_queryset(request)
            .annotate(_article_number=F('article__article_number'))
        )

    def article_number(self, obj):
        return obj.article.article_number
    article_number.admin_order_field = '_article_number'  # Use the annotated field for ordering

Use an underscore to prevent the annotated field from clashing with your dynamic attribute.

 
Share this