id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25375	Paginator generates suboptimal queries	Alexandru Mărășteanu	nobody	"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."	Bug	closed	Database layer (models, ORM)	1.7	Normal	needsinfo		Alexandru Mărășteanu	Unreviewed	0	0	0	0	0	0