Opened 14 years ago
Last modified 6 years ago
#13871 new New feature
contrib.admin:list_editable - ForeignKey performance is O(m*n)
Reported by: | chadc | Owned by: | nobody |
---|---|---|---|
Component: | contrib.admin | Version: | dev |
Severity: | Normal | Keywords: | list_editable, admin, ForeignKey, admin efficiency |
Cc: | chadcogar@…, kissgyorgy@… | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Overview
Including ForeignKey fields in list_editable results in m*n database queries where m is the number of ForeignKey fields in list_editable and n is the number of rows in the changelist.
Description
I have been experiencing poor performance with the admin changelist when list_editable includes ForeignKey fields. In particular, rendering the changelist requires O(m*n) database queries where m is the number of ForeignKey fields included in list_editable and n is the number of rows in the changelist. The problem, as I understand it, stems from the fact that the choices for the ForeignKey widgets are not cached. So, when each ForeignKey widget is rendered, it queries the database to retrieve the list of possible values.
My solution to this problem has been to override the Select widget with a CachedSelect widget in the admin model. However, as Jeremy Dunck noted in django-developers (link below), this may stem from a more general problem with ModelFormSet. I have ticketed this under contrib.admin for now, but I hope that Jeremy will update it as any larger issues become clear.
Example
class Host(models.Model): name = models.CharField(max_length=128, unique=True) class Account(models.Model): host = models.ForeignKey(Host, related_name="accounts") name = models.CharField(max_length=128) class AccountAdmin(admin.ModelAdmin): list_display = ('name', 'host') list_editable = ('host',) list_per_page = 25 admin.site.register(Account, AccountAdmin)
Rendering the ForeignKey widgets in this example requires n*m=25*1=25 database queries:
SELECT "hosts_host"."id", "hosts_host"."name" FROM "hosts_host" ORDER BY "hosts_host"."name" ASC
Total time: 330 ms
Numer of queries: 25
Related Links
django-developers: http://groups.google.ca/group/django-developers/browse_thread/thread/76066baed6ba70dc
django-users: http://groups.google.ca/group/django-users/browse_thread/thread/7b63fd40c891ec19
Change History (10)
comment:1 by , 14 years ago
comment:2 by , 14 years ago
Hmm. I had just intended to use CachedSelect as a temporary measure -- for my own purposes -- until the actual problem is addressed. I included it for the sake of anyone else who might be interested in a temporary fix. As I mentioned above, Jeremy seemed to indicate that there is some larger issue here of which I am not fully aware. I hope that someone with more knowledge of the problem might be willing to take a look.
In any case:
- How could you implement additional filtering? I simply use formfield_overrides = {models.ForeignKey:{'widget':CachedSelect()}} on any AdminForm with an editable ForeignKey. I have no idea how any additional filtering could be implemented, so please let me know. Also, would it use the same naming convention?
- The cache is invalidated when it hits name == 'form-0-<model_name>'.
comment:3 by , 14 years ago
Triage Stage: | Unreviewed → Accepted |
---|
I can confirm this is actually happening.
Regarding the additional filtering, maybe you need to filter the hosts list by some property of each Account.
comment:4 by , 14 years ago
Severity: | → Normal |
---|---|
Type: | → New feature |
follow-up: 6 comment:5 by , 14 years ago
Easy pickings: | unset |
---|
It also happens when you try to add a object that has a foreign key with too many possible values.
Like, if I have a table users, with 700 entries, and a models that has users as a foreign key, when I try adding an entry for this models, it will make +700 sql queries to fill the select.
Is there any fix for this yet?
comment:6 by , 14 years ago
Replying to uriel.bertoche:
It also happens when you try to add a object that has a foreign key with too many possible values.
Like, if I have a table users, with 700 entries, and a models that has users as a foreign key, when I try adding an entry for this models, it will make +700 sql queries to fill the select.
Is there any fix for this yet?
That is fairly unrelated -- use raw_id_fields. This issue is about list_editable. The problem you're describing is on a different admin screen.
comment:8 by , 9 years ago
Cc: | added |
---|
comment:9 by , 9 years ago
Version: | 1.2 → master |
---|
comment:10 by , 6 years ago
Using autocomplete_fields
improves things slightly: it still makes the same number of database queries but these queries return only 1 row instead of the entire table.
Keep in mind the formset is using forms that can apply additional filtering to the data sets thus making each choice set unique. If you want to introduce some sort of caching, you pretty much have to cache by serialized query params. Also you need to make sure the cache is thread-safe and does not outlive the formset (your CachedSelect does not seem to ever invalidate the cache).