DBIx::Class generic resultset method to take the current query and estimate a count of the rows returned. Use `->estimate_count` in place of `->count` to get a fast, approximate count of complex queries.

ResultSet.pm

Add the following to your base ResultSet class:

# fast row estimation function for postgres
# similar to ->count, but may be much faster for Pg < 9.2
# uses query planner row count estimation, accuracy depends on when
# ANALYZE/autovacuum was last performed on the table
# more info: http://wiki.postgresql.org/wiki/Count_estimate
sub estimate_count {
    my ($self) = @_;

    # we're going to perform an EXPLAIN on the current query
    my $subquery = $self->as_query;
    # $subquery contains query string and bind values
    my ($subquery_str, @subquery_bind) = @{ $$subquery };
    # bind vals are in a wacky DBIC/SQLA form. grab just the raw vals
    my @subquery_bind_raw = map { $_->[1] } @subquery_bind;

    # execute the EXPLAIN with DBI
    my $res = $self->result_source->schema->storage->dbh_do(
        # query
        sub {
            my ($storage, $dbh, $_subquery_str, @_subquery_bind) = @_;

            my $sth = $dbh->prepare("EXPLAIN $_subquery_str");
            my $rv = $sth->execute(@_subquery_bind);

            unless ($rv) {
                # failure
                warn "Failed to EXPLAIN query. Error: " .
                    $sth->errstr . ". Query: $_subquery_str";
                return;
            }

            # EXPLAIN results go in $r
            my $r = $sth->fetchall_arrayref({});
            # top-level (total) query planner estimate is in $r->[0]
            # it contains the final row estimate
            my $total_plan = $r->[0];

            unless ($total_plan && $total_plan->{'QUERY PLAN'}) {
                warn "Failed to get total query plan from estimate_count EXPLAIN";
                return;
            }

            my ($estimate) = $total_plan->{'QUERY PLAN'} =~ /rows=(\d+)/;
            return $estimate;
        },

        # passed as args to the above coderef
        $subquery_str,
        @subquery_bind_raw,
    );

    return $res;
}