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

1 Answers

0
dirkgroten On Best Solutions

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.