The django admin change_list template causes the execution of the 'select count(*) from table_name' type query which can be very slow when the table has a few million entries. The problem is better described at https://code.djangoproject.com/ticket/8408 The following custom paginator will solve this issue by caching the row count for a short per…

paginator

import collections
from math import ceil
from django.core.paginator import Page
from django.core.cache import cache


# To use the paginator, add the following to your admin class:
# from myapp import CachingPaginator
#
# ...
# ...
# paginator = CachingPaginator
#

class CachingPaginator(Paginator):
    '''
    A custom paginator that helps to cut down on the number of 
    SELECT COUNT(*) form table_name queries. These are really slow, therefore
    once we execute the query, we will cache the result which means the page
    numbers are not going to be very accurate but we don't care
    '''

    def _get_count(self):
        """
        Returns the total number of objects, across all pages.
        """
        
        if self._count is None:
            try:
                key = "adm:{0}:count".format( hash(self.object_list.query.__str__()) )
                self._count = cache.get(key, -1);
                if self._count == -1 :
                    if not self.object_list.query.where:
                        # This query that avoids a count(*) alltogether is 
                        # stolen from https://djangosnippets.org/snippets/2593/
                        cursor = connection.cursor()
                        cursor.execute("SELECT reltuples FROM pg_class WHERE relname = %s",
                            [self.object_list.query.model._meta.db_table])                        
                        self._count = int(cursor.fetchone()[0])
                    else :
                        self._count = self.object_list.count()
                    cache.set(key, self._count, 3600)
                    
            except :
                # AttributeError if object_list has no count() method.
                # TypeError if object_list.count() requires arguments
                # (i.e. is of type list).
                self._count = len(self.object_list)
        return self._count
    count = property(_get_count)

The original version of this custom paginator still used the select count() an alternative method that does not involve a count() query was discovered at https://djangosnippets.org/snippets/2593/ . If you query on the pg_class table make sure that you regularly vacuum the table that corresponds to your model.

In line 3 it should be:

from django.core.paginator import Paginator

Hey--mind if I turn this into a pip package?

Also--for people looking for something similar with mysql here's a pile of code: http://stackoverflow.com/questions/10433173/prevent-django-admin-from-running-select-count-on-the-list-form

Double also--how to use a paginator: http://stackoverflow.com/a/39849179/350306

Hi, e4c5, I was trying to solve the problem with this paginator, but our admin uses the filter frequently, the paginator would not fetch the estimated number from reltuples, so it normally would still take 15 - 30 s to load the page with 1.5 million records, do you have any idea for such situation?

Sadly it doesn't work with PostgreSQL 13.4. It returns zero always (bu it's millions of records in the table actually)