Django Rest Framework pagination extremely slow count

The issue is, that the query used to count is the same potentially complex one as used to fetch the data. That's rather wasteful. PageNumberPagination uses Django's own Paginator internally.

To make the query for the count simpler override the paginator class DRF uses:

from django.core.paginator import Paginator
from django.utils.functional import cached_property
from rest_framework.pagination import PageNumberPagination

class FasterDjangoPaginator(Paginator):
    @cached_property
    def count(self):
        # only select 'id' for counting, much cheaper
        return self.object_list.values('id').count()


class FasterPageNumberPagination(PageNumberPagination):
    django_paginator_class = FasterDjangoPaginator

Override the get_paginated_response method of your pagination class, and do not include the count. You can refer to the base implementation of the PageNumberPagination class to see what you should return.

from rest_framework.pagination import PageNumberPagination
from collections import OrderedDict # requires Python 2.7 or later

class PageNumberPaginationWithoutCount(PageNumberPagination):
    # Set any other options you want here like page_size

    def get_paginated_response(self, data):
        return Response(OrderedDict([
            ('next', self.get_next_link()),
            ('previous', self.get_previous_link()),
            ('results', data)
        ]))

Then in your settings.py, set DEFAULT_PAGINATION_CLASS to your new pagination class.

DEFAULT_PAGINATION_CLASS = 'path.to.PageNumberPaginationWithoutCount'

This approach is used in the example in the pagination docs.

Edit: from the comments below it sounds like this might not be enough to prevent the slow sql query, so you might need to override paginate_queryset as well.