Document that admin changelist adds `pk` to ordering

[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!

[Tim Graham]

Do you have a suggestion of what to do?

[Tim Graham]

Absent another proposal, the behavior could be documented.

[Simon Charette]

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.

[Taha Jahangir]

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)

[Hasan Ramezani]

PR

[Carlton Gibson]

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.

[Hasan Ramezani]

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

[Carlton Gibson]

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.

[Hasan Ramezani]

I added requested changes in the PR

[Tim Graham <timograham@…>]

In f2b46023:

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

Backport of f63811f4813f0e0439e140a97eeba18a5017e858 from master.

[Tim Graham <timograham@…>]

In f63811f4:

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