HttpBots.com

django admin tables with large postgre sql tables

Django admin page likes to do counts on tables in its change list view so that it can draw a pretty pagination widget at the bottom. These two facts make for a miserable admin experience when you have more than a handful of rows in your database. One visitor to the admin site will at best have to wait for a while before the page loads, several will most likely bring the database server to its knees. Here is patch for Posgre SQL based on Django admin with large tables article.

Add this code to admin.py:

from django.db.models.query import QuerySet
from django.db import connections

class ApproxCountQuerySet(QuerySet):
    '''Counting all rows is very expensive on large tables. This
    is a replacement for QuerySet that returns an approximation if count()
    is called with no additional constraints. In all other cases it should
    behave exactly as QuerySet.'''

    def count(self):
        """
        Performs a SELECT COUNT() and returns the number of records as an
        integer.

        If the QuerySet is already fully cached this simply returns the length
        of the cached results set to avoid multiple SELECT COUNT(*) calls.
        """
        if self._result_cache is not None and not self._iter:
            return len(self._result_cache)

        query = self.query

        if (not query.where) and \
                query.high_mark is None and \
                query.low_mark == 0 and \
                (not query.select) and \
                (not query.group_by) and \
                (not query.having) and \
                (not query.distinct):
            # If query has no constraints, we would be simply doing
            # "SELECT COUNT(*) FROM foo". Monkey patch so the we
            # get an approximation instead.
            cursor = connections[self.db].cursor()
            cursor.execute("SELECT reltuples  FROM pg_class r WHERE relkind = 'r' AND relname = '%s'" % self.model._meta.db_table)
            
            return int(cursor.fetchall()[0][0])
        else:
            return self.query.get_count(using=self.db)

class FooAdmin(admin.ModelAdmin): 
	def queryset(self, request): 
		qs = super(FooAdmin, self).queryset(request) 
		return qs._clone(klass=ApproxCountQuerySet) 

Where FooAdmin your model admin class

Other links: Counting rows in postgresql

Author: ranux 11.08.11 17:32 Votes:1 1