Reducing queries for ForeignKeys in Django admin inlines

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 byraw_id_fields in inlines from using the prefetched values.

TL;DR: in the Django code the relatedForeignKeyIdWidget has 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.

Benchmark

A change-view page with 180 inline items with relations:

1. Using no raw_id_fields: 1658 queries

2. With raw_id_fields and prefetching in Django 3.1: 384 queries

3. 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.