CODEWITHSUNDEEP
djangopaginationquery-optimizationdjango-queryset
NKay
NKay

Reputation: 140

Django pagination query duplicated, double the time

In my current project I want to do some filtering and ordering on a queryset and show it to the user in a paginated form.
This works fine, however I am not comfortable with the performance.
When I use and order_by statement either explicitly or implicitly with the model Meta ordering, I can see in the Debug toolbar that this query is essentially executed twice.
Once for the paginator count (without the ORDER BY) and once to fetch the objects slice (with ORDER BY).
From my observation this leads to doubling the time it takes.

Is there any way this can be optimized?

Below is a minimal working example, in my actual app I use class based views.

class Medium(models.Model):
    title = models.CharField(verbose_name=_('title'),
                             max_length=256,
                             null=False, blank=False,
                             db_index=True,
                             )
    offered_by = models.ForeignKey(Institution,
                                   verbose_name=_('Offered by'),
                                   on_delete=models.CASCADE,
                                   )
    quantity = models.IntegerField(verbose_name=_('Quantity'),
                                   validators=[
                                       MinValueValidator(0)
                                   ],
                                   null=False, blank=False,
                                   )
    deleted = models.BooleanField(verbose_name=_('Deleted'),
                                  default=False,
                                  )


def index3(request):
    media = Medium.objects.filter(deleted=False, quantity__gte=0)
    media = media.exclude(offered_by_id=request.user.institution_id)
    media = media.filter(title__icontains="funktion")
    media = media.order_by('title')
    paginator = Paginator(media, 25)
    media = paginator.page(1)
    return render(request, 'media/empty2.html', {'media': media})

Debug toolbar sql timings

Upvotes: 1

Views: 1045

Answers (1)

dirkgroten
dirkgroten

Reputation: 20682

The query is not exactly duplicated: One is a COUNT query, the other one fetches the actual objects for the specific page requested. This is unavoidable, since Django's Paginator needs to know the total number of objects. However, if the queryset media isn't too large, you can optimise by forcing the media Queryset to be evaluated (just add a line len(media) before you define the Paginator).

But note that if media is very large, you might not want to force media to be evaluated as you're loading all the objects into memory.

Upvotes: 1

Related Questions