Plasticx Blog

When a PostgreSQL database table has many rows of data, selecting the count(*) of the rows can be very slow. For instance, a table with over one million rows on an average performing virtual host can take over 5 seconds to complete. This is because PostgreSQL walks through (scans) all the rows in the table. It can be faster if the count(*) includes conditions on table columns that are indexed.

Assuming a Rails application is using the will_paginate plugin to enumerate over the large table, the rendering of each page will take many seconds to complete. This is due to the expense of scanning all the rows in the table as count(*) will be used in the calculation of the will_paginate navigation. Slow Counting is a known and accepted slow performing operation in PostgreSQL

If the Rails application is paginating over all the rows in a table, then a fast approximation technique can be used in place of the count(*) operation. Assuming a Foo model we mark its conditions as ‘1=1’ so that the will_paginate plugin will use our approximation. The pagination would look like the following

Foo.paginate(:page => params[:page], :conditions => "1=1")

In a Rails initializer that we call config/initializers/will_paginate_postgresql_count.rb we use a Rails alias method chain to implement our own wp_count method. wp_count is a protected method defined in WillPaginate::Finder::ClassMethods that is used to determine the count of rows in a table, this determines how the will_paginate navigation is rendered. It is selecting the reltuples value from PostgreSQL’s pg_class where relkind equals ‘r’ and relname is the name of the table used by our Foo class. The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table.. The pg_class approximation will only be used when the conditions given to paginate are ‘1=1’, otherwise the original wp_count method is called. The code is saved in a Gist and listed below below

will_paginate_postgresql_count.rb Gist

# add this file as config/initializers/will_paginate_postgresql_count.rb
# in a Rails application
 
module WillPaginate
  module Finder
    module ClassMethods
 
      # add '1=1' to paginate conditions as marker such that the select from the pg_class
      # is used to approximate simple rows count, e.g.
      # Foo.paginate(:page => params[:page], :conditions => "1=1")
      def wp_count_with_postgresql(options, args, finder, &block)
        if options[:conditions] == ["1=1"] || options[:conditions] == "1=1"
          # counting rows in PostgreSQL is slow so use the pg_class table for
          # approximate rows count on simple selects
          # http://wiki.postgresql.org/wiki/Slow_Counting
          # http://www.varlena.com/GeneralBits/120.php
          ActiveRecord::Base.count_by_sql "SELECT (reltuples)::integer FROM pg_class r WHERE relkind = 'r' AND relname = '#{self.table_name}'"
        else
          wp_count_without_postgresql(options, args, finder, &block)
        end
 
      end
 
      alias_method_chain :wp_count, :postgresql
    end
  end
end