Skip to content

Instantly share code, notes, and snippets.

@e4c5
Last active January 7, 2022 14:29
Show Gist options
  • Save e4c5/6852723 to your computer and use it in GitHub Desktop.
Save e4c5/6852723 to your computer and use it in GitHub Desktop.
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…
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)
@e4c5
Copy link
Author

e4c5 commented Nov 1, 2014

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.

@Shellbye
Copy link

In line 3 it should be:

from django.core.paginator import Paginator

@BenLiyanage
Copy link

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

@BenLiyanage
Copy link

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

@BenLiyanage
Copy link

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

@heyixian44
Copy link

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?

@1st
Copy link

1st commented Jan 7, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment