#25375: Paginator generates suboptimal queries
-------------------------------------+-------------------------------------
Reporter: Alexandru | Owner: nobody
Mărășteanu |
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Stefan Wehrmeyer):
The Paginator needs to make two queries: the count query and the query for
the page objects.
If the queryset you use in your Paginator satisfies
[https://github.com/django/django/blob/0af14b2eaa0bf0821a8aacc8486489a1eb348397/django/db/models/sql/query.py#L412-L413
one of these conditions] the count query will run as a subquery, so this
query:
{{{
SELECT COUNT(*) FROM (SELECT ...);
}}}
instead of
{{{
SELECT COUNT(*) AS "__count" FROM ...;
}}}
In my case the first query takes double the amount of time of the second
query.
The row count can be independent of annotations. So if you make the
Paginator do the count query on a queryset without annotations (resulting
in a more performant count query) and then doing the actual pagination
with the annotated queryset may result in a small performance win.
Annotations can influence row count (e.g. when filtering by annotation)
and you still may have `groupby()` or `distinct()` on your queryset that
influences counts and forces a count with a subquery. So I'm not sure if
there's a general way for the Paginator (or any `.count()` call) to run
the more performant query.
--
Ticket URL: <https://code.djangoproject.com/ticket/25375#comment:8>
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/064.90faeac875fc852e80314a030f3f85de%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
