Improving performance of Django admin list filter queries

Question

I'm developing a Django admin wrapper around a PostgreSQL data-warehouse app, which has some tables with millions of records.

The admin changelist page, without any list filters, loads in a second, but if I include certain columns in the admin's list_filters, the it loads very slowly, and can take between 30 seconds to a minute to load.

Inspecting the database, I saw several queries like:

SELECT DISTINCT "warehouse_data"."filter_field1" FROM "warehouse_data" ORDER BY "warehouse_data"."filter_field1" ASC;

each one only takes 3-5 seconds, but there's as there's a dozen of them, these add up. All the fields are indexed, so I'm not sure how else I can speed them up. How can I improve admin performance? How would I plug in Django's caching mechanism to cache the actual queries of these list filters?

Answer 1

As you observed; the slowness comes from django compiling the list of unique values so it can display them in the sidebar.

Under the hood this requires a full table scan against the database, which is costly when your table is very large. If you're using this field as a list_filter; there's a good chance that the number of unique values is small, and that you can generate a list of unique values more efficiently yourself (assuming you know where the values are coming from). To do that, you can define a custom list_filter.

From the docs (condensed for berevity):

list_filter should be a list or tuple of elements, where each element should be of one of the following types:

• a field name
• a class inheriting from django.contrib.admin.SimpleListFilter

from datetime import date
from django.contrib import admin
from django.utils.translation import gettext_lazy as _

class DecadeBornListFilter(admin.SimpleListFilter):
    title = _('decade born')
    parameter_name = 'decade'

    def lookups(self, request, model_admin):
        return (
            ('80s', _('in the eighties')),
            ('90s', _('in the nineties')),
        )

    def queryset(self, request, queryset):
        # Compare the requested value (either '80s' or '90s')
        # to decide how to filter the queryset.
        if self.value() == '80s':
            return queryset.filter(birthday__gte=date(1980, 1, 1),
                                    birthday__lte=date(1989, 12, 31))
        if self.value() == '90s':
            return queryset.filter(birthday__gte=date(1990, 1, 1),
                                    birthday__lte=date(1999, 12, 31))

class PersonAdmin(admin.ModelAdmin):
    list_filter = (DecadeBornListFilter,)