Reducing queries for ForeignKeys in Django admin inlines
Use 93x less queries using patched raw_id_fields and prefetching
3 min read
Speed up with raw_id_fields
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:
Prefetching in inlines
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...
The bug in ForeignKeyRawIdWidget
Unfortunately, there is currently a bug in the Django issue tracker that prevents the widget used by
raw_id_fields in inlines from using the prefetched values.
TL;DR: in the Django code the related
ForeignKeyIdWidgethas to do a
QuerySet.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.