#35637: Paginator executes additional SQL query when using QuerySet
-------------------------------------+-------------------------------------
Reporter: amir-mahdih86 | Type:
| Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 5.0 | Severity: Normal
Keywords: Paginator, | Triage Stage:
Queryset, Performance, SQL | Unreviewed
Queries |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
When using Django's `Paginator` class with a `QuerySet`, it executes an
additional SQL count query to determine the total count of items. This
does not happen when a `list` is passed to the `Paginator`.
**Steps to Reproduce:**
1. Create a view that uses `Paginator` with a `QuerySet` object.
2. Compare the SQL queries executed with those executed when a `list` is
used instead.
**Expected Behavior:**
The `Paginator` shouldn't execute an additional query to count a
QuerySet's items while it can use `len()` to do that.
**Actual Behavior:**
An additional query is executed when a `QuerySet` is passed while it's
possible to convert `QuerySet` to a `list` before passing it to
`Paginator` with no difference in returned object but 1 less database
query execution.
**Example Code:**
{{{
# This code is simplified to be smaller
# This bellow view executes 3 SQL queries
class PostList(APIView):
def get(self, request):
posts = Post.objects.filter(is_published=True)
paginator = Paginator(posts, 10)
posts = paginator.page(1)
serializer = PostSerializer(posts, many=True)
return Response(serializer.data)
# But this one executes 2 SQL queries
class PostList(APIView):
def get(self, request):
posts = list(Post.objects.filter(is_published=True))
paginator = Paginator(posts, 10)
posts = paginator.page(1)
serializer = PostSerializer(posts, many=True)
return Response(serializer.data)
}}}
**My Solution:**
A simple solution is to modify the `__init__` method of the `Paginator`
class to convert `object_list` to a `list` object.
This is just a simple solution and it may be necessary to enhance the
problem in a deeper layer.
Here is the modified version of that method:
{{{
def __init__(
self,
object_list,
per_page,
orphans=0,
allow_empty_first_page=True,
error_messages=None,
):
self.object_list = list(object_list)
self._check_object_list_is_ordered()
self.per_page = int(per_page)
self.orphans = int(orphans)
self.allow_empty_first_page = allow_empty_first_page
self.error_messages = (
self.default_error_messages
if error_messages is None
else self.default_error_messages | error_messages
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35637>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070190f381df72-16d1f284-e429-4402-9535-fc859ef4ffa1-000000%40eu-central-1.amazonses.com.
