skip to content

In Django's admin, what do show_facets and date_hierarchy add to a change list, and what extra queries does each one cost?

level: middleimportance: nice to knowfreq 28%

answer

  1. counts next to filter choices
  2. three enum values, one default
  3. a query-string toggle
  4. year, month, day drill-down
  5. min and max first

basics

~20 s

show_facets (Django 5.0+) shows per-choice row counts in the filter sidebar, at one aggregate query per filter; the default ALLOW computes them only when _facets is in the URL. date_hierarchy adds a date drill-down: a min/max query plus a distinct-dates query.

solid answer

~40 s

**Facets**, added in Django 5.0, are counts next to each `list_filter` choice, recomputed for the filters currently applied. `ModelAdmin.show_facets` takes `admin.ShowFacets.ALLOW` (the default: a toggle link adds `_facets` to the URL), `ALWAYS` or `NEVER`. Each filter costs an aggregate query over the queryset filtered by all the *other* active filters; a `SimpleListFilter`'s `queryset()` is applied once per choice to build that aggregate. The docs warn this can hurt on large tables and suggest `NEVER`. `date_hierarchy = "opened_at"` adds a year, month and day drill-down above the list; with no date selected it runs a `MIN`/`MAX` aggregate to pick the starting level, then a distinct `dates()`/`datetimes()` query for the links. An index on the date column keeps both cheap.

code

python · 17 lines
python
from django.contrib import admin

from helpdesk.models import ArchivedTicket, Ticket


@admin.register(Ticket)
class TicketAdmin(admin.ModelAdmin):
    list_filter = ["status", "priority"]
    show_facets = admin.ShowFacets.ALWAYS  # small, hot queue
    date_hierarchy = "opened_at"


@admin.register(ArchivedTicket)
class ArchivedTicketAdmin(admin.ModelAdmin):
    list_filter = ["status"]
    show_facets = admin.ShowFacets.NEVER  # millions of rows
    date_hierarchy = "closed_at"  # indexed column

go deeper

for a junior

Know that facets show counts beside filter choices and that date_hierarchy adds a year, month and day navigation bar.

for a middle

Explain ALLOW, ALWAYS and NEVER, the _facets toggle, and the min/max plus distinct-dates queries behind date_hierarchy.

for a senior

Show you size these features per table: facets on small queues, NEVER on archives, indexes on the drill-down column.

for a principal

Weigh convenience features in internal tools against database load that grows with data and with the number of staff.

## Facets: counts beside filter choices Since **Django 5.0**, the admin can show a **facet count** beside each choice in the filter sidebar: "Open (312)", "Pending (48)". The counts reflect the other filters already applied, so an agent filtering by priority "High" sees how many high-priority tickets are in each status. `ModelAdmin.show_facets` controls it with the `admin.ShowFacets` enum: | Value | Behaviour | |---|---| | `ShowFacets.ALLOW` (default) | counts appear only when the `_facets` query-string parameter is present; the change list shows a toggle link that adds or removes it | | `ShowFacets.ALWAYS` | counts are always computed and shown | | `ShowFacets.NEVER` | counts are never computed, and no toggle is offered | ### What facets cost For each filter in `list_filter`, the change list builds the queryset filtered by every **other** active parameter and runs one **aggregate query** with a conditional `Count` per choice. So: - the number of extra queries grows with the number of filters; - each query scans whatever the other filters leave, which on an unfiltered 10-million-row ticket table is everything; - a `SimpleListFilter` computes its counts by calling its own `queryset()` for every choice and counting rows whose primary key is in that result, so an expensive `queryset()` gets more expensive; - a related-field filter with many choices produces a wide aggregate. The documentation's advice is direct: on large datasets, consider `ShowFacets.NEVER`. `ALLOW` is a sensible middle ground because the cost is only paid when someone clicks the toggle, but it is still one click away for every staff user. ## date_hierarchy: a date drill-down `date_hierarchy` names one `DateField` or `DateTimeField` (a related path such as `customer__signed_up_at` works too). The change list then shows a navigation bar: years, then months within a year, then days within a month. How it queries: 1. With **no** date selected, it runs an aggregate for the `MIN` and `MAX` of the field over the current queryset. If all rows fall in one year (or one month), it starts at that level instead of listing years. 2. It then asks for the distinct truncated dates at the chosen level, using `QuerySet.dates()` for a `DateField` or `QuerySet.datetimes()` for a `DateTimeField`, to build the links. 3. Choosing a link adds year/month/day parameters that filter the main query. With `USE_TZ = True`, `datetimes()` truncates in the current time zone, which is why the docs point to its caveats for time zone support. ## Tuning both - Index the `date_hierarchy` column; both the min/max aggregate and the distinct truncation benefit. - Keep `list_filter` to the filters staff actually use; each one is a facet query when facets are on. - Prefer `RelatedOnlyFieldListFilter` for related filters on big tables so both the choice list and the facet aggregate stay small. - Set `show_facets = admin.ShowFacets.NEVER` on the largest models, and `ALWAYS` only where the table is small and the counts are the point. ## Facets and custom filters A `SimpleListFilter` gets facet counts for free: the admin applies its `queryset()` for each choice and counts the matching primary keys. If `queryset()` returns `None` for a choice, that choice shows `(-)` instead of a number. Two practical consequences follow. First, a filter whose `queryset()` is expensive (a subquery over replies, say) is paid once per choice while facets are on. Second, `lookups()` itself runs on every page load regardless of facets, so a `lookups()` that queries the database for its choices adds a query even with `NEVER`. ## The support-ticket queue For a queue of a few thousand open tickets, `ALWAYS` gives agents instant counts per status and priority at negligible cost. For the archive of every ticket ever closed, `NEVER` plus `date_hierarchy = "closed_at"` on an indexed column keeps the page responsive while still letting staff jump to a month.

  • With Django's default show_facets, how does a staff user turn facet counts on?
    The default is `ShowFacets.ALLOW`: the change list shows a link that adds the `_facets` parameter to the URL, and the counts are computed only while it is present. Removing the parameter through the same toggle turns them off. With `NEVER` the link is not shown and counts are never computed.

saying these in an interview costs you the question

  • Facet counts are cached, so enabling them adds no queries
  • show_facets defaults to ALWAYS, showing counts on every page load
  • date_hierarchy accepts any field, including CharField timestamps
  • Facet counts ignore the filters the user has already applied
  • Facets have been in the admin since its first releases