Prefetching in Django: less queries
Image (Daniel Bonilla): Casa Batlló by Gaudí in Barcelona, one of my favorite masterpieces in architecture.
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
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.