Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#29943 closed Cleanup/optimization (fixed)

Document that admin changelist adds `pk` to ordering

Reported by: Taha Jahangir Owned by: Hasan Ramezani
Component: Documentation Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Taha Jahangir)

Consider a simple model with this definition for model and admin:

class MyModel(models.Model):
    class Meta:
        ordering = ('-created',)

    created = models.DateTimeField(default=now, db_index=True)
    message = models.CharField(max_length=20)


@admin.register(MyModel)
class MyModelAdmin(admin.ModelAdmin):
    pass

We created a model with the indexed created field, and ordering field set to it . It should works nicely. But if the tables go large, the listing will be slow, because the generated query is like:

SELECT "myapp_mymodel"."id", "myapp_mymodel"."created", "myapp_mymodel"."message" FROM "myapp_mymodel" ORDER BY "myapp_mymodel"."created" DESC, "myapp_mymodel"."id" DESC LIMIT 100;

And the database (in my case, postgresql) WILL NOT use the created index and the query becomes very slow.

I treat this as a bug, because the default behavior of admin module is not sensible (and not documented), and will result in performance bug in normal setups , and it cannot be changed in a simple manner (without copying/monkey-patching of ChangeList.get_ordering method).

A stackoverflow topic about this behavior:
https://stackoverflow.com/questions/32419190/django-admin-incorrectly-adds-order-by-into-query

The issue (and commit) when this behavior is introduced (~7 years ago)
#17198 -- Ensured that a deterministic order is used across all database backends

In reply to https://code.djangoproject.com/ticket/17198#comment:14 :
In our cases (a ~2M row table), the duration of count(*) query is ~300ms, but viewing the 2000th page (LIMIT 100 OFFSET 200000) is 8s!

Change History (12)

comment:1 by Taha Jahangir, 6 years ago

Description: modified (diff)

comment:2 by Tim Graham, 6 years ago

Easy pickings: unset

Do you have a suggestion of what to do?

comment:3 by Tim Graham, 6 years ago

Component: contrib.adminDocumentation
Summary: Slow admin chanelist query (because of adding `pk` to ordering)Document that admin changelist adds `pk` to ordering
Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization

Absent another proposal, the behavior could be documented.

comment:4 by Simon Charette, 6 years ago

Taha, out of curiosity does adding a composite Index(fields=('-created', 'id')) index instead of using db_index=True helps anyhow? It shouldn't be significantly larger than the created index db_index=True created and provide a total ordering on your table.

comment:5 by Taha Jahangir, 6 years ago

There are two proposals for this issue:

1) Adding a variable (perhaps a property on ModelAdmin) to easily enable/disable this behavior.
2) If we think in a larger scope, django-admin could have a huge-table mode. There are also other issues with large tables (like querying for distinct values in filters or displaying a long list of related items when filtering by foreign-key)

comment:6 by Hasan Ramezani, 6 years ago

Has patch: set
Owner: changed from nobody to Hasan Ramezani
Status: newassigned

PR

Version 0, edited 6 years ago by Hasan Ramezani (next)

comment:7 by Carlton Gibson, 6 years ago

Patch needs improvement: set

Ref PR: The suggestion to index both (e.g.) -created and id was made here and on #17198 comment:2, so should probably be mentioned in any documentation fix.

Last edited 6 years ago by Carlton Gibson (previous) (diff)

comment:8 by Hasan Ramezani, 6 years ago

Patch needs improvement: unset

the comment from the mentioned ticket added to note.
I also checked the override get_ordering(), so the behavior doesn't change.

comment:9 by Carlton Gibson, 6 years ago

Patch needs improvement: set

Patch needs updating to reflect changes in f84ad16ba4bcf5fce6fc76593e0606573dec4697 (which was ref #17198)

Rough outline of the change needed is something like:

  • If no total ordering existing, pk is added.
  • If this causes performance issues, add composite index.

comment:10 by Hasan Ramezani, 6 years ago

Patch needs improvement: unset

I added requested changes in the PR

comment:11 by Tim Graham <timograham@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In f2b46023:

[2.2.x] Fixed #29943 -- Doc'd that admin changelist may add pk to ordering.

Backport of f63811f4813f0e0439e140a97eeba18a5017e858 from master.

comment:12 by Tim Graham <timograham@…>, 6 years ago

In f63811f4:

Fixed #29943 -- Doc'd that admin changelist may add pk to ordering.

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