Search for a command to run...
Use 93x less queries using patched raw_id_fields and prefetching
Made my first website in Microsoft Word at age 13, when I discovered the "save as html" button. Since then I've journeyed various computing languages, eventually settling for Python. I love diving into the core of my favorite framework - Django - discover and writing about new stuff.
AFAIK this is applies to all Django versions. You are right that they should be classes instead. It might be that as a string it still works as lazy loading, so probably both work. The key of the problem is how the Django internals work in such a way that it creates a lot of extra queries.
Django offers a great deal of hidden functionality - secret gems - if you know where to look. In this serie I am showing tips and tricks to get the most out of Django.
Prevent 500 error raised by exceptions by catching them in the middleware
When working in the Django admin with a lot of users, you might sometimes want to offer the functionality to users to choose which columns they want to see in the changelist page. For some users some columns might be more relevant than to others. One...
Expiring a superuser in Django after a due date
At Wundermart we started saving monetary amounts in Django, we naively started with DecimalField(decimal_places=2) for our financial data. After some time we realized that you will lose data this way when applying calculations, for example: €100 * 0....
Suppose you change your Django model instance, how do you know if the data actually changed? There are a few ways to achieve this, for example using django-simple-history which will add a database entry for every change to keep history. However, you ...
The django-simple-history package is awesome as it shows you all the activity on a certain Django model object. It allows seeing how it was mutated over time and who changed it. However, there is one caveat: you cannot easily see what objects were de...
ForeignKeys in the Django admin show as a select-box (<select>) by default. This will result in a query for every item in the select, only to get the name. This is not a problem if the model you're referencing will always be small. But growing models with significant rows will become a problem soon. Having 3 times the same select-box with 1000 items will result in 3000 queries. It will also make the select unusable.
Instead, you could use the ModelAdmin.raw_id_fields which turns it into an input widget with a search icon. It will only query the name of the selected item:
raw_id_fields can also be used in ModelAdmin inlines. However, when you have a lot of these inlines on a page, it's gonna hurt. Having 180 items will result in at least that amount of queries, and usually even double or more.
Less queries = faster load, so to cope with this you can use prefetch_related which I wrote about earlier. Apply the same get_queryset technique on the ModelAdmin, this cached prefetch data will also be available to your inlines.
This should reduce the number of queries to just a few for the entire page. But unfortunately there is a caveat...
Unfortunately, there is currently a bug in the Django issue tracker that prevents the widget used byraw_id_fields in inlines from using the prefetched values.
TL;DR: in the Django code the related
ForeignKeyIdWidgethas to do aQuerySet.get()to get the name of the item. By doing so, the already cached prefetch_related values are ignored. In the said bug issue I proposed a solution, but it proved to be changing too much Django code in other places as well to be accepted.
To work around this problem some code needs to be patched, a snippet with a mixin can be found lower on this page. The example below shows how to implement the patched RawIdWidgetAdminMixin in your code. Note that the mixin should precede ModelAdmin to work. Thanks to the mixin and prefetch_related your page will now consume way fewer queries!
class CustomAdmin(RawIdWidgetAdminMixin, ..., ModelAdmin):
inlines = ('some-inline',)
def get_queryset(self, request):
return (
super().get_queryset(request)
.prefetch_related('your-inline-model')
)
The patched ForeignKeyRawIdWidget can be found in this Github Gist I posted below. Perhaps the most important line is 56 where an object is returned (instead of a plain value). This ultimately prevents the get() as on line 30, we can get the attributes from the object instead of having to query them for each item.
The patch cannot be much smaller than this, as one change triggers another down the line. It can probably be smaller by monkey-patching the classes, but I prefer to keep it clean by subclassing.
A change-view page with 180 inline items with relations:
Using no raw_id_fields: 1658 queries
With raw_id_fields and prefetching in Django 3.1: 384 queries
Patched raw_id_fields and prefetching: 18 queries
This proves that if you have a many inline items on a page, the mixin can reduce the amount of queries. Using raw_id_fields already drastically improves queries compared to having select-boxes. The unpatched raw_id_fields still produces an unacceptable high number of queries, because prefetching is not working due to the bug. This number of queries can still cause serious query times, resulting in your page to be very slow.
Maybe in the future this bug will be fixed in Django, so this patch will not be necessary anymore.