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 Patryk Zawadzki, 14 years ago

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).

comment:2 by chadc, 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:

  1. 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?
  1. The cache is invalidated when it hits name == 'form-0-<model_name>'.

comment:3 by Joni, 14 years ago

Triage Stage: UnreviewedAccepted

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 Graham King, 14 years ago

Severity: Normal
Type: New feature

comment:5 by uriel.bertoche, 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?

in reply to:  5 comment:6 by Jeremy Dunck, 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:7 by Aymeric Augustin, 13 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:8 by Kiss György, 9 years ago

Cc: kissgyorgy@… added

comment:9 by Kiss György, 9 years ago

Version: 1.2master

comment:10 by Aymeric Augustin, 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.

Last edited 6 years ago by Aymeric Augustin (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top