[Django] #17659: django_admin_log searches are slow
Django
--------------------------------------+--------------------
Reporter: anonymous | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: contrib.admin | Version: 1.3
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------------+--------------------
Queries against django_admin_log are slow because the soft-reference
columns are not indexed.
--
Ticket URL: <https://code.djangoproject.com/ticket/17659>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: anonymous | Owner: keeff
Type: | Status: new
Cleanup/optimization | Version: 1.3
Component: contrib.admin | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by keeff):
* owner: nobody => keeff
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:1>
Django
Reporter: anonymous | Owner: keeff
Type: | Status: new
Cleanup/optimization | Version: 1.3
Component: contrib.admin | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Changes (by akaariai):
* cc: anssi.kaariainen@… (added)
Comment:
Adding some indexes makes sense to me. However, it would be nice to
confirm both indexes are actually needed.
Is it possible to provide some data on how these indexes are used in the
queries Django generates for realworld data? EXPLAIN ANALYZE from
PostgreSQL would be perfect.
Multicolumn index could be the best solution here, but unfortunately that
isn't currently supported. If possible, data for multicolumn index too
would be great.
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:2>
Django
Reporter: anonymous | Owner: keeff
Type: | Status: new
Cleanup/optimization | Version: 1.3
Component: contrib.admin | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Comment (by anonymous):
object_id is used in stock django, i.e. history_view() in
contrib/admin/options.py. The index here is a clear win for any who use
django_admin_log.
object_repr is often used in custom forms and other external code, one
common example is to implement a global history search. Doing this via
object_id is not reasonable since this would involve working out the
object_id for all matching objects in every extant model, and even then it
may not be viable to match history for deleted objects. Such forms should
be efficient without needing a local modification to
contrib/admin/models.py
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:3>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by akaariai):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* stage: Unreviewed => Accepted
Comment:
I wonder if it is really necessary to have the index for object_repr alone
though. Every django installation using admin would need to pay the price
of that index, but it is not needed in stock installations. I have written
multiple django apps, and never needed object_repr lookups. You can add
the index by hand if needed.
So, I would say +1 on the object_id index, and more proof for the need of
object_repr index.
Marking as accepted on the basis of need for object_id index. Although
query plans with the index and without it are still missing.
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:4>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by edruid):
This would be nice if it were to be completed some time in the not distant
future.
However I think an index_together on content_type_id and object_id would
be better as a search for object_id without knowing what type of object
one is looking for is a strange query.
I could add a patch for this if it would move things along quicker. If I
do, should I include a migration file?
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:5>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by claudep):
Yes, the patch should include the migration.
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:6>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by adamchainz):
I've just had to migrate our django_admin_log table (4.6 million rows) to
add an index on (content_type_id, object_id) for the history view. I've
also removed the plain content_type_id index since it is then a redundant
as a prefix index.
Importantly, I had to change object_id to a varchar(255) since you can't
index a text column on MySQL (idk about other DBs). You can index a prefix
of up to 255 characters which would suffice (most object_ids are a handful
of characters, being simple integers - being a TextField is presumably for
completeness) - but Django models don't have this option yet...
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:7>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Tim Graham):
#30122 is a duplicate.
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:8>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Ülgen Sarıkavak):
@keeff Are you still interested in this issue? If not so, I'd like to
claim it.
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:9>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 1.3
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Stanislas Guerra):
FYI,
Without the index, the last query of the admin history page took 1350 ms
(1,35s):
{{{
SELECT ••• FROM `django_admin_log` INNER JOIN `auth_user` ON
(`django_admin_log`.`user_id` = `auth_user`.`id`) WHERE
(`django_admin_log`.`content_type_id` = 15 AND
`django_admin_log`.`object_id` = '144887') ORDER BY
`django_admin_log`.`action_time` ASC LIMIT 25
}}}
Whereas with the following index it took 0.93 ms:
{{{
CREATE INDEX django_admin_log_object_id ON django_admin_log (object_id);
}}}
Server version: 10.11.11-MariaDB-0+deb12u1 Debian 12
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:10>
Django
Reporter: anonymous | Owner: keeff
Component: contrib.admin | Version: 5.2
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Stanislas Guerra):
* cc: Stanislas Guerra (added)
* version: 1.3 => 5.2
--
Ticket URL: <https://code.djangoproject.com/ticket/17659#comment:11>