Paginator for PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
from django.contrib.admin.options import IncorrectLookupParameters
from django.contrib.admin.views.main import ChangeList, MAX_SHOW_ALL_ALLOWED
from django.core.paginator import InvalidPage, Paginator
from django.db import connection


class LargeTableChangeList(ChangeList):
    """
    Overrides the count method to get an estimate instead of actual count when not filtered.

    The only change is the try/catch block calculating 'full_result_count'
    """
    def get_results(self, request):
        paginator = self.model_admin.get_paginator(request, self.query_set, self.list_per_page)
        # Get the number of objects, with admin filters applied.
        result_count = paginator.count

        # Get the total number of objects, with no admin filters applied.
        # Perform a slight optimization: Check to see whether any filters were
        # given. If not, use paginator.hits to calculate the number of objects,
        # because we've already done paginator.hits and the value is cached.
        if not self.query_set.query.where:
            full_result_count = result_count
        else:
            try:
                cursor = connection.cursor()
                cursor.execute("SELECT reltuples FROM pg_class WHERE relname = %s",
                    [self.root_query_set.query.model._meta.db_table])
                full_result_count = int(cursor.fetchone()[0])
            except:
                full_result_count = self.root_query_set.count()

        can_show_all = result_count <= MAX_SHOW_ALL_ALLOWED
        multi_page = result_count > self.list_per_page

        # Get the list of objects to display on this page.
        if (self.show_all and can_show_all) or not multi_page:
            result_list = self.query_set._clone()
        else:
            try:
                result_list = paginator.page(self.page_num+1).object_list
            except InvalidPage:
                raise IncorrectLookupParameters

        self.result_count = result_count
        self.full_result_count = full_result_count
        self.result_list = result_list
        self.can_show_all = can_show_all
        self.multi_page = multi_page
        self.paginator = paginator



class LargeTablePaginator(Paginator):
    """
    Overrides the count method to get an estimate instead of actual count when not filtered
    """
    def _get_count(self):
        """
        Changed to use an estimate if the estimate is greater than 10,000
        Returns the total number of objects, across all pages.
        """
        if self._count is None:
            try:
                estimate = 0
                if not self.object_list.query.where:
                    try:
                        cursor = connection.cursor()
                        cursor.execute("SELECT reltuples FROM pg_class WHERE relname = %s",
                            [self.object_list.query.model._meta.db_table])
                        estimate = int(cursor.fetchone()[0])
                    except:
                        pass
                if estimate < 10000:
                    self._count = self.object_list.count()
                else:
                    self._count = estimate
            except (AttributeError, TypeError):
                # 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)

More like this

  1. Capped efficient count paginator by hassa 2 months, 2 weeks ago
  2. Database backup with admin command by jhg 10 months ago
  3. Django Admin Speedup for big tables on postgres by potiuk 6 months, 2 weeks ago
  4. Faster pagination / model object seeking (10x faster infact :o) for larger datasets (500k +) by sleepycal 2 years, 6 months ago
  5. Admin: return to change_list with filter and pagination applied by fx 2 years, 2 months ago

Comments

craigds (on August 27, 2012):

For django 1.4 compatibility, should get rid of the MAX_SHOW_ALL_ALLOWED import and change the LargeTableChangeList class thusly:

class LargeTableChangeList(ChangeList):
    """
    Overrides the count method to get an estimate instead of actual count when not filtered.

    The only change is the try/catch block calculating 'full_result_count'
    """
    def __init__(self, *args, **kwargs):
        super(LargeTableChangeList, self).__init__(*args, **kwargs)
        if django.VERSION < (1, 4):
            from django.contrib.admin.views.main import MAX_SHOW_ALL_ALLOWED
            self.list_max_show_all = MAX_SHOW_ALL_ALLOWED

    def get_results(self, request):
        paginator = self.model_admin.get_paginator(request, self.query_set, self.list_per_page)
        # Get the number of objects, with admin filters applied.
        result_count = paginator.count

        # Get the total number of objects, with no admin filters applied.
        # Perform a slight optimization: Check to see whether any filters were
        # given. If not, use paginator.hits to calculate the number of objects,
        # because we've already done paginator.hits and the value is cached.
        if not self.query_set.query.where:
            full_result_count = result_count
        else:
            try:
                cursor = connection.cursor()
                cursor.execute("SELECT reltuples FROM pg_class WHERE relname = %s",
                    [self.root_query_set.query.model._meta.db_table])
                full_result_count = int(cursor.fetchone()[0])
            except:
                full_result_count = self.root_query_set.count()

        can_show_all = result_count <= self.list_max_show_all
        multi_page = result_count > self.list_per_page

        # Get the list of objects to display on this page.
        if (self.show_all and can_show_all) or not multi_page:
            result_list = self.query_set._clone()
        else:
            try:
                result_list = paginator.page(self.page_num + 1).object_list
            except InvalidPage:
                raise IncorrectLookupParameters

        self.result_count = result_count
        self.full_result_count = full_result_count
        self.result_list = result_list
        self.can_show_all = can_show_all
        self.multi_page = multi_page
        self.paginator = paginator

#

gojomo (on September 2, 2012):

Thanks for this... I adapted this approach to also deal with the same issue in TastyPie's own Paginator. (It's filling in a 'total_count' on list result 'meta' fields, which similarly takes a long time with large tables.) The resulting class:

from tastypie.paginator import Paginator from django.db import connection

# adapted from http://djangosnippets.org/snippets/2593/
# may be supplied as 'paginator_class' in a ModelResource's Meta options
class ApproximateLargeCountPaginator(Paginator):
    def get_count(self):
        if not self.objects.query.where:
            estimate = 0
            try:
                cursor = connection.cursor()
                cursor.execute("SELECT reltuples FROM pg_class WHERE relname = %s",
                    [self.objects.query.model._meta.db_table])
                estimate = int(cursor.fetchone()[0])
            except:
                pass
            if estimate > 10000:
                return estimate
        return super(ApproximateLargeCountPaginator, self).get_count()

#

(Forgotten your password?)