A Django admin change list over a 20-million-row donations table takes seconds per page; which admin options and overrides make it fast?

seniormust knowfreq 42%

answer

count the queries before the rows
two counts per page by default
the paginator is replaceable
search, sort and hierarchy costs

basics

~20 s

A Django admin change list runs two COUNT queries per page by default. Set show_full_result_count = False to drop the unfiltered one, supply a paginator whose count is estimated or capped, and keep search, sorting, date_hierarchy and filters on indexed, cheap paths.

solid answer

~40 s

Start by counting queries. The ChangeList calls paginator.count for the filtered total, and — while show_full_result_count is True, the default — root_queryset.count() for the unfiltered total shown as "99 results (103 total)". On a huge table both are full scans. Set show_full_result_count = False to remove the second, and replace the first by setting paginator (or overriding get_paginator()) to a Paginator subclass whose count returns an estimate or a capped count. Then remove the other expensive work: plain search_fields use icontains, which cannot use a normal index — prefer ^ (istartswith) or = (iexact) prefixes; date_hierarchy adds date queries; related list_filters load every related row as a choice; sorting by an unindexed column scans, so restrict sortable_by and ordering; and add list_select_related for foreign-key columns so each row does not trigger its own query.

Start by counting queries. The `ChangeList` calls `paginator.count` for the filtered total, and — while `show_full_result_count` is `True`, the default — `root_queryset.count()` for the unfiltered total shown as "99 results (103 total)". On a huge table both are full scans. Set `show_full_result_count = False` to remove the second, and replace the first by setting `paginator` (or overriding `get_paginator()`) to a `Paginator` subclass whose `count` returns an estimate or a capped count. Then remove the other expensive work: plain `search_fields` use `icontains`, which cannot use a normal index — prefer `^` (istartswith) or `=` (iexact) prefixes; `date_hierarchy` adds date queries; related `list_filter`s load every related row as a choice; sorting by an unindexed column scans, so restrict `sortable_by` and `ordering`; and add `list_select_related` for foreign-key columns so each row does not trigger its own query.

code

python · 33 lines

python

from django.contrib import admin
from django.core.paginator import Paginator
from django.db import connection
from django.utils.functional import cached_property

from .models import Donation


class EstimatedCountPaginator(Paginator):
    @cached_property
    def count(self):
        query = self.object_list.query
        if query.where:  # filtered or searched: count exactly
            return super().count
        with connection.cursor() as cursor:
            cursor.execute(
                "SELECT reltuples FROM pg_class WHERE relname = %s",
                [query.model._meta.db_table],
            )
            row = cursor.fetchone()
        estimate = int(row[0]) if row else 0
        return estimate if estimate > 0 else super().count


@admin.register(Donation)
class DonationAdmin(admin.ModelAdmin):
    list_display = ["id", "donor", "amount", "received_at"]
    list_select_related = ["donor"]
    show_full_result_count = False
    paginator = EstimatedCountPaginator
    search_fields = ["=reference", "^donor__email"]
    sortable_by = ["id", "received_at"]
    ordering = ["-id"]

go deeper

for a junior

Recall that the admin counts rows on every change list page and that big tables make those counts slow.

for a middle

Explain the two COUNT queries, what show_full_result_count removes, and how search_fields prefixes change the lookup.

for a senior

Measure the queries, replace the paginator count with an estimate or cap, and keep search, sort, filters and date_hierarchy on indexed paths.

for a principal

Decide which huge tables staff should browse in the admin at all, versus purpose-built search or reporting tools.

Where the time goes

A Django admin change list is built by django.contrib.admin.views.main.ChangeList. For every page load it typically runs:

paginator.count — a COUNT(*) over the filtered queryset, needed for the page links.
root_queryset.count() — a second COUNT(*) with no admin filters, used for "N results (M total)", but only while ModelAdmin.show_full_result_count is True (the default).
The page itself — LIMIT 100 OFFSET … with list_per_page = 100 by default.
Extra queries from date_hierarchy, list_filter choices and, without list_select_related, one query per foreign key per row.

On a 20-million-row table the counts dominate: a full count has to visit every row, and the default makes two of them.

The admin's own levers

Lever	Effect
`show_full_result_count = False`	Drops the unfiltered count; the page shows "99 results (Show all)" instead
`paginator = EstimatedCountPaginator` or `get_paginator()`	Replaces the filtered count with something cheap
`list_per_page`	Smaller pages mean less rendering; it does not fix counts
`list_select_related`	Joins foreign keys used by `list_display`, avoiding per-row queries
`sortable_by` and `ordering`	Limits sorting to indexed columns
`search_fields` prefixes	`^name` uses `istartswith`, `=email` uses `iexact`; the default is `icontains`
`date_hierarchy = None`	Avoids the date-listing queries on every page
`show_facets = admin.ShowFacets.NEVER`	Prevents per-filter-choice counts; the default only computes them when a user toggles facets on

Replacing the count

The ModelAdmin.paginator attribute defaults to django.core.paginator.Paginator, whose count is a cached property that calls object_list.count(). A subclass can override count:

Estimated: when the queryset has no filters, read the database's own row estimate (on PostgreSQL, the planner statistics) and fall back to an exact count when filters are active.
Capped: count at most N+1 rows with a sliced subquery and show "N+" — exact enough for navigation.

Page links computed from an estimate can point past the real end of the data, so a user may occasionally land on an empty last page; for a staff tool that browses recent rows, that is usually an acceptable trade.

Search, filters and sorting

search_fields without a prefix build icontains lookups ORed across fields; each search scans. Prefixes make them index-friendly; for real full-text search, @field uses the search lookup, which on PostgreSQL relies on django.contrib.postgres.
Related list_filters list every related object; on large related tables use a custom SimpleListFilter with a short, fixed set of choices.
Default ordering on the model or admin is applied to every page; make sure it matches an index, and exclude costly computed columns from sortable_by.

Diagnosing before tuning

Load the page with a query-inspection tool or database logging and list every query and its time.
Run the slowest through the database's EXPLAIN to see whether it scans.
Fix the biggest one first — usually the counts, then search.
Re-measure with realistic filters, because staff rarely use the unfiltered list.

What not to do

Raise list_per_page to reduce clicks; it multiplies rendering work.
Remove the model from the admin entirely when two attributes would fix it.
Assume select_related fixes counts; joins do not make COUNT(*) cheaper.

A capped count instead of an estimate

When the database offers no cheap estimate, or filtered lists are the common case, a paginator can count at most, say, 10,001 rows by counting a sliced queryset (object_list[:10001].count()), which stops scanning early. The page then shows at most 100 pages of links, and staff narrow with filters to go further. It is exact for small results and bounded for large ones.

A checklist for any large-table admin

show_full_result_count = False.
A paginator with an estimated or capped count.
list_select_related covering every foreign key in list_display.
search_fields with ^ or = prefixes on indexed columns.
ordering and sortable_by limited to indexed columns.
No date_hierarchy, or one on an indexed date column with filters that keep it small.
Related filters replaced by fixed-choice SimpleListFilters.
raw_id_fields or autocomplete_fields on change forms that point at big tables, so the form does not render a million-option dropdown.

## Where the time goes A Django admin **change list** is built by `django.contrib.admin.views.main.ChangeList`. For every page load it typically runs: 1. **`paginator.count`** — a `COUNT(*)` over the filtered queryset, needed for the page links. 2. **`root_queryset.count()`** — a second `COUNT(*)` with **no** admin filters, used for "N results (M total)", but only while `ModelAdmin.show_full_result_count` is `True` (the default). 3. The page itself — `LIMIT 100 OFFSET …` with `list_per_page = 100` by default. 4. Extra queries from `date_hierarchy`, `list_filter` choices and, without `list_select_related`, one query per foreign key per row. On a 20-million-row table the counts dominate: a full count has to visit every row, and the default makes two of them. ## The admin's own levers | Lever | Effect | |---|---| | `show_full_result_count = False` | Drops the unfiltered count; the page shows "99 results (Show all)" instead | | `paginator = EstimatedCountPaginator` or `get_paginator()` | Replaces the filtered count with something cheap | | `list_per_page` | Smaller pages mean less rendering; it does not fix counts | | `list_select_related` | Joins foreign keys used by `list_display`, avoiding per-row queries | | `sortable_by` and `ordering` | Limits sorting to indexed columns | | `search_fields` prefixes | `^name` uses `istartswith`, `=email` uses `iexact`; the default is `icontains` | | `date_hierarchy = None` | Avoids the date-listing queries on every page | | `show_facets = admin.ShowFacets.NEVER` | Prevents per-filter-choice counts; the default only computes them when a user toggles facets on | ## Replacing the count The `ModelAdmin.paginator` attribute defaults to `django.core.paginator.Paginator`, whose `count` is a cached property that calls `object_list.count()`. A subclass can override `count`: - **Estimated**: when the queryset has no filters, read the database's own row estimate (on PostgreSQL, the planner statistics) and fall back to an exact count when filters are active. - **Capped**: count at most N+1 rows with a sliced subquery and show "N+" — exact enough for navigation. Page links computed from an estimate can point past the real end of the data, so a user may occasionally land on an empty last page; for a staff tool that browses recent rows, that is usually an acceptable trade. ## Search, filters and sorting - **`search_fields`** without a prefix build `icontains` lookups ORed across fields; each search scans. Prefixes make them index-friendly; for real full-text search, `@field` uses the `search` lookup, which on PostgreSQL relies on `django.contrib.postgres`. - **Related `list_filter`s** list every related object; on large related tables use a custom `SimpleListFilter` with a short, fixed set of choices. - **Default ordering** on the model or admin is applied to every page; make sure it matches an index, and exclude costly computed columns from `sortable_by`. ## Diagnosing before tuning 1. Load the page with a query-inspection tool or database logging and list every query and its time. 2. Run the slowest through the database's `EXPLAIN` to see whether it scans. 3. Fix the biggest one first — usually the counts, then search. 4. Re-measure with realistic filters, because staff rarely use the unfiltered list. ## What not to do - Raise `list_per_page` to reduce clicks; it multiplies rendering work. - Remove the model from the admin entirely when two attributes would fix it. - Assume `select_related` fixes counts; joins do not make `COUNT(*)` cheaper. ## A capped count instead of an estimate When the database offers no cheap estimate, or filtered lists are the common case, a paginator can count at most, say, 10,001 rows by counting a sliced queryset (`object_list[:10001].count()`), which stops scanning early. The page then shows at most 100 pages of links, and staff narrow with filters to go further. It is exact for small results and bounded for large ones. ## A checklist for any large-table admin 1. `show_full_result_count = False`. 2. A paginator with an estimated or capped `count`. 3. `list_select_related` covering every foreign key in `list_display`. 4. `search_fields` with `^` or `=` prefixes on indexed columns. 5. `ordering` and `sortable_by` limited to indexed columns. 6. No `date_hierarchy`, or one on an indexed date column with filters that keep it small. 7. Related filters replaced by fixed-choice `SimpleListFilter`s. 8. `raw_id_fields` or `autocomplete_fields` on change forms that point at big tables, so the form does not render a million-option dropdown.

What does a Django admin user lose when show_full_result_count is set to False?
The "(103 total)" part of the result counter on a filtered change list, which needed the extra unfiltered `COUNT(*)`. The page shows "99 results (Show all)" instead. Pagination, filtering and actions keep working; actions are still shown because the admin no longer relies on the full count to decide whether any rows exist.
Why does adding search_fields = ['donor__email'] slow a large Django admin change list?
Without a prefix the admin builds `donor__email__icontains`, a case-insensitive substring match that an ordinary B-tree index cannot serve, plus a join to the donor table and possibly a `DISTINCT`. Prefixing with `^` or `=` turns it into `istartswith` or `iexact`, which suitable indexes can support.

saying these in an interview costs you the question

list_select_related makes the change list's COUNT queries cheap
The admin runs a single COUNT per page regardless of settings
Raising list_per_page is the fix for slow change lists
search_fields without a prefix use an index-friendly startswith lookup
show_full_result_count = False disables pagination

A Django admin change list over a 20-million-row donations table takes seconds per page; which admin options and overrides make it fast?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Where the time goes

The admin's own levers

Replacing the count

Search, filters and sorting

Diagnosing before tuning

What not to do

A capped count instead of an estimate

A checklist for any large-table admin

▾common follow-ups2 questions

▾red flags5 flags

Where the time goes

The admin's own levers

Replacing the count

Search, filters and sorting

Diagnosing before tuning

What not to do

A capped count instead of an estimate

A checklist for any large-table admin

answer

go deeper