#25375: Paginator generates suboptimal queries
----------------------------------------------+--------------------
Reporter: alexei | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Hello,
I'm using Django Rest Framework and have the following situation:
I have two models, `Article` and `Comment`:
{{{
class Article(models.Model):
# ...
class Comment(models.Model):
article = models.ForeignKey(Article, related_name='comments')
# ...
}}}
And an articles viewset:
{{{
class Articles(viewsets.ModelViewSet):
model = Article
serializer_class = ArticleSerializer
paginate_by = 50
paginate_by_param = 'limit'
def get_queryset(self):
return Article.objects.annotate(comments_count=Count('comments'))
}}}
When doing a simple listing, the database will be queried two times: one
to count the result set, the other to fetch the results:
{{{
SELECT
COUNT(*)
FROM (
SELECT
`article`.`id` AS `id`,
COUNT(`article_comment`.`id`) AS `comments_count`
FROM `article`
LEFT OUTER JOIN `article_comment` ON (`article`.`id` =
`article_comment`.`article_id`)
GROUP BY `article`.`id`
ORDER BY NULL
)
subquery;
}}}
And
{{{
SELECT
`article`.`id` AS `id`,
COUNT(`article_comment`.`id`) AS `comments_count`
FROM `article`
LEFT OUTER JOIN `article_comment` ON (`article`.`id` =
`article_comment`.`article_id`)
GROUP BY `article`.`id`
ORDER BY `article`.`id` DESC
LIMIT 50;
}}}
respectively.
If you look at (and `EXPLAIN`) the first query, the one that does the
counting, you'll notice it loads up *all* records and then starts counting
them, a process which may take up all resources and eventually crash the
machine. It did so in my case, with only 50000 records.
I figured this has something to do with Django, not DRF.
--
Ticket URL: <https://code.djangoproject.com/ticket/25375>
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/049.c0e86416342ca33d1b6b75879b147799%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
