Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#10697 closed (invalid)

contrib.admin is slow with large, complex datasets

Reported by: mrts Owned by: nobody
Component: contrib.admin Version: master
Severity: Keywords: efficient-admin
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Suppose I have a complex admin page that, given the following models:

class A(models.Model):
    name = models.CharField(...)
    # 10 more fields, some of them e.g. TextFields()

    def __unicode__(self):
        return self.name

class B(models.Model):
    name = models.CharField(...)
    # 10 more fields, some of them e.g. TextFields()

    def __unicode__(self):
        return self.name

class C(models.Model):
    name = models.CharField(...)
    a = models.ForeignKey(A)
    b = models.ForeignKey(B)
    is_published = models.BooleanField()
    # 10 more fields, some of them e.g. TextFields()

has to display the following in admin change list:

class CAdmin(admin.ModelAdmin):
    list_display = ('name', 'a', 'b', 'is_published')
    list_filter = ('a',)

The SQL for the change list view is as follows:

  1. SELECT COUNT(*) FROM app_c to calculate the number of pages for pagination,
  2. SELECT * FROM app_c ORDER BY app_a.id DESC LIMIT 100 OFFSET 300 to show the objects on a particular page,
  3. for each result int the previous object set, SELECT * FROM app_a and SELECT * FROM app_b to display the string representations of a and b. (Actually, as a is used in filtering, it will not generate the extra queries.)

This can be improved by:

  1. making COUNT(*) optional (see #8408),
  2. use QuerySet.only() to only select the non-foreign key fields that are actually used (name and is_published in this case),
  3. use a join to avoid the n additional queries for foreign keys (the display fields have to be explicitly controlled by list_display = ('name', 'a__name', 'b__name', 'is_published'), see #3400).

As a result, only the following single query should be executed:

SELECT app_c.name, app_a.name, app_b.name, app_c.is_published
    FROM app_c, app_a, app_b
    WHERE app_c.a_id = app_a.id AND app_c.b_id = app_b.id
    ORDER BY app_c.id DESC LIMIT 100 OFFSET 300

Change History (4)

comment:1 Changed 6 years ago by oyvind

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 6 years ago by mrts

Indeed, seems it should be used implicitly. However, Django debug toolbar displays that there are indeed n extra queries in practice.

Nevertheless, the fact that "lookup separator" __ can not be used in list_display is limiting -- as of now, __unicode__ is plainly called on all related objects, which is suboptimal if e.g. I only need to display the name field.

comment:3 Changed 6 years ago by jacob

  • Resolution set to invalid
  • Status changed from new to closed

This is two things:

  • A duplicate of a couple of tickets (#8408, #3400).
  • A request for a feature (list_select_related) that already exists.

Neither warrants a separate ticket.

comment:4 Changed 6 years ago by mrts

  • Keywords efficient-admin added

I've split this up into the following tickets:

  • document ModelAdmin.queryset, listing the recipe to speed up the admin changelist view: #10712,
  • as the lookup separator for list_display is not a duplicate of #3400, I've filed it separately as #10743,
  • #10722 was causing the n queries (it doesn't fall into the "a feature that already exists" category), proposed #10742 to overcome the problem.

Additionally, #10710 and #10733 popped up as deficiencies in only() when exploring this.

They all have been grouped under the efficient-admin keyword.

Note: See TracTickets for help on using tickets.
Back to Top