[Django] #37287: Add Index on LogEntry.action_time to optimize admin dashboard performance

[Django]

#37287: Add Index on LogEntry.action_time to optimize admin dashboard performance
-------------------------------------+-------------------------------------
Reporter: Ali Rafiei | Type:
| Cleanup/optimization
Status: new | Component:
| contrib.admin
Version: dev | Severity: Normal
Keywords: | Triage Stage:
admin,index,logentry,performance | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Problem & Motivation

In high-traffic production environments, the django_admin_log table often
grows to millions of rows. Every time a user loads the main Django Admin
Index (dashboard) page, the framework displays the "Recent actions"
sidebar. This forces the following query to execute:

SELECT ... FROM "django_admin_log" ORDER BY
"django_admin_log"."action_time" DESC LIMIT 10;

Because LogEntry explicitly defines ordering = ['-action_time'] in its
Meta options but lacks any indexing on the action_time field, database
engines must perform a highly inefficient sequential table scan to
retrieve these 10 rows. This causes severe CPU spikes and long page-load
delays on large datasets, as highlighted in recent community discussions
(e.g., Django Forum thread #41943).

Historical Context & Precedent

The structural performance limitations of django_admin_log are well-
documented. Similar bottlenecks regarding missing indexes were flagged in
**Ticket #17659** and **Ticket #36414**.

While previous community efforts to index django_admin_log faced
roadblocks—specifically regarding the object_id column because MySQL
struggles to index unbounded text columns—**action_time is a standard
DateTimeField**. Adding an index here carries no cross-database
compatibility issues and is universally supported across PostgreSQL,
MySQL, SQLite, and Oracle.

Proposed Solution
To address this bottleneck, I propose adding an explicit descending index
on action_time directly to LogEntry.Meta.indexes (e.g.,
models.Index(fields=['-action_time'] and generate the corresponding core
migration for contrib.admin. This targets an un-bypassable query built
into Django's default UI and guarantees predictable performance scaling
for enterprise installations.
--
Ticket URL: <https://code.djangoproject.com/ticket/37287>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37287: Add Index on LogEntry.action_time to optimize admin dashboard performance
-------------------------------------+-------------------------------------

Reporter: Ali Rafiei | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: contrib.admin | Version: dev
Severity: Normal | Resolution:

Keywords: | Triage Stage:
admin,index,logentry,performance | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Ali Rafiei:

Old description:

New description:

Problem & Motivation

In high-traffic production environments, the django_admin_log table often
grows to millions of rows. Every time a user loads the main Django Admin
Index (dashboard) page, the framework displays the "Recent actions"
sidebar. This forces the following query to execute:

SELECT ... FROM "django_admin_log" ORDER BY
"django_admin_log"."action_time" DESC LIMIT 10;

Because LogEntry explicitly defines ordering = ['-action_time'] in its
Meta options but lacks any indexing on the action_time field, database
engines must perform a highly inefficient sequential table scan to
retrieve these 10 rows. This causes severe CPU spikes and long page-load
delays on large datasets, as highlighted in recent community discussions

(e.g., Django Forum thread https://forum.djangoproject.com/t/strange-
behaviour-for-django-5-0-long-loading-times-and-high-postgres-cpu-load-
only-admin/41943).

Historical Context & Precedent

The structural performance limitations of django_admin_log are well-
documented. Similar bottlenecks regarding missing indexes were flagged in
**Ticket #17659** and **Ticket #36414**.

While previous community efforts to index django_admin_log faced
roadblocks—specifically regarding the object_id column because MySQL
struggles to index unbounded text columns—**action_time is a standard
DateTimeField**. Adding an index here carries no cross-database
compatibility issues and is universally supported across PostgreSQL,
MySQL, SQLite, and Oracle.

Proposed Solution
To address this bottleneck, I propose adding an explicit descending index
on action_time directly to LogEntry.Meta.indexes (e.g.,
models.Index(fields=['-action_time'] and generate the corresponding core
migration for contrib.admin. This targets an un-bypassable query built
into Django's default UI and guarantees predictable performance scaling
for enterprise installations.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/37287#comment:1>

[Django]

#37287: Add Index on LogEntry.action_time to optimize admin dashboard performance
-------------------------------------+-------------------------------------

Reporter: Ali Rafiei | Owner: Ali
Type: | Rafiei
Cleanup/optimization | Status: assigned

Component: contrib.admin | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage:
admin,index,logentry,performance | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Ali Rafiei):

* owner: (none) => Ali Rafiei
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/37287#comment:2>

[Django]

#37287: Add Index on LogEntry.action_time to optimize admin dashboard performance
-------------------------------------+-------------------------------------
Reporter: Ali Rafiei | Owner: Ali
Type: | Rafiei
Cleanup/optimization | Status: assigned
Component: contrib.admin | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage:
admin,index,logentry,performance | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ali Rafiei):

Patch: https://github.com/alirafiei75/django/tree/ticket_37287

Adds a descending index on LogEntry.action_time (admin.0004). The admin
"Recent actions" sidebar always runs ORDER BY action_time DESC LIMIT 10
with no matching index.

Benchmark: SQLite, 200,000 rows, same query as the dashboard:
without index: ~17.2 ms, SCAN + TEMP B-TREE FOR ORDER BY
with DESC index: ~0.007 ms, covering index scan
--
Ticket URL: <https://code.djangoproject.com/ticket/37287#comment:3>

[Django]

#37287: Add Index on LogEntry.action_time to optimize admin dashboard performance
-------------------------------------+-------------------------------------
Reporter: Ali Rafiei | Owner: Ali
Type: | Rafiei

Cleanup/optimization | Status: closed
Component: contrib.admin | Version: dev
Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage:
admin,index,logentry,performance | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => duplicate
* status: assigned => closed

Comment:

Thank you for the ticket!
I think this is a duplicate of #17659
--
Ticket URL: <https://code.djangoproject.com/ticket/37287#comment:4>

[Django]

#37287: Add Index on LogEntry.action_time to optimize admin dashboard performance
-------------------------------------+-------------------------------------
Reporter: Ali Rafiei | Owner: Ali
Type: | Rafiei
Cleanup/optimization | Status: closed
Component: contrib.admin | Version: dev
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
admin,index,logentry,performance | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ali Rafiei):

Thanks Sarah. I'll follow up on #17659.
--
Ticket URL: <https://code.djangoproject.com/ticket/37287#comment:5>