#29943: Slow admin chanelist query (because of adding `pk` to ordering)
-------------------------------+--------------------------------------
Reporter: Taha Jahangir | Owner: nobody
Type: Bug | Status: new
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------+--------------------------------------
Description changed by Taha Jahangir:
Old description:
> 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).
New description:
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!
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29943#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.0f215a998c302b80c5321678ff27cf3c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
