Slow admin chanelist query (because of adding `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!