DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type;
	b=nq6azFZpYC9zJRLW9lGBRofjVRe2HZEOJfVxp7O6GHXs4pGCKN2pDufn22YY4kCCW0
	0yoVdwVifc5YlflUQCcJNH1WnRn3N4VjKGlGadZEHpYfVaL4L51zxKubYJVqUZF6D7kI
	nUZap5SgKl5BOkoD0RU4LqxtwruTvcUePLq8Q=
Message-ID: <4CB46E8D.1010800@gmail.com>
Date: Tue, 12 Oct 2010 10:19:57 -0400
From: Joe Uhl <joeuhl@gmail.com>
User-Agent: Postbox 2.0.0 (Macintosh/20100928)
MIME-Version: 1.0
To: Neil Whelchel <neil.whelchel@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow count(*) again...
References: <201010091626.18636.neil.whelchel@gmail.com>
	<201010111254.58479.neil.whelchel@gmail.com>
	<4CB3A2CC.9070009@vmsinfo.com>
	<201010111936.46207.neil.whelchel@gmail.com>
In-Reply-To: <201010111936.46207.neil.whelchel@gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090601040705070507060403"

This is a multi-part message in MIME format.
--------------090601040705070507060403
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>> The biggest single problem with "select count(*)" is that it is
>> seriously overused. People use that idiom to establish existence, which
>> usually leads to a performance disaster in the application using it,
>> unless the table has no more than few hundred records. SQL language, of
>> which PostgreSQL offers an excellent implementation,  offers [NOT]
>> EXISTS clause since its inception in the Jurassic era. The problem is
>> with the sequential scan, not with counting. I'd even go as far as to
>> suggest that 99% instances of the "select count(*)" idiom are probably
>> bad use of the SQL language.
>
> I agree, I have seen many very bad examples of using count(*). I will go so
> far as to question the use of count(*) in my examples here. It there a better
> way to come up with a page list than using count(*)? What is the best method
> to make a page of results and a list of links to other pages of results? Am I
> barking up the wrong tree here?
One way I have dealt with this on very large tables is to cache the 
count(*) at the application level (using memcached, terracotta, or 
something along those lines) and then increment that cache whenever you 
add a row to the relevant table.  On application restart that cache is 
re-initialized with a regular old count(*).  This approach works really 
well and all large systems in my experience need caching in front of the 
DB eventually.  If you have a simpler system with say a single 
application/web server you can simply store the value in a variable, the 
specifics would depend on the language and framework you are using.

Another more all-DB approach is to create a statistics tables into which 
you place aggregated statistics rows (num deleted, num inserted, totals, 
etc) at an appropriate time interval in your code.  So you have rows 
containing aggregated statistics information for the past and some tiny 
portion of the new data happening right now that hasn't yet been 
aggregated.  Queries then look like a summation of the aggregated values 
in the statistics table plus a count(*) over just the newest portion of 
the data table and are generally very fast.

Overall I have found that once things get big the layers of your app 
stack start to blend together and have to be combined in clever ways to 
keep speed up.  Postgres is a beast but when you run into things it 
can't do well just find a way to cache it or make it work together with 
some other persistence tech to handle those cases.


--------------090601040705070507060403
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body style="font-size: medium;" bgcolor="#ffffff" text="#000000">
    <blockquote cite="mid:201010111936.46207.neil.whelchel@gmail.com"
      type="cite">
      <blockquote type="cite">
        <pre wrap="">The biggest single problem with "select count(*)" is that it is
seriously overused. People use that idiom to establish existence, which
usually leads to a performance disaster in the application using it,
unless the table has no more than few hundred records. SQL language, of
which PostgreSQL offers an excellent implementation,  offers [NOT]
EXISTS clause since its inception in the Jurassic era. The problem is
with the sequential scan, not with counting. I'd even go as far as to
suggest that 99% instances of the "select count(*)" idiom are probably
bad use of the SQL language.
</pre>
      </blockquote>
      <pre wrap=""><!---->
I agree, I have seen many very bad examples of using count(*). I will go so 
far as to question the use of count(*) in my examples here. It there a better 
way to come up with a page list than using count(*)? What is the best method 
to make a page of results and a list of links to other pages of results? Am I 
barking up the wrong tree here?
</pre>
    </blockquote>
    One way I have dealt with this on very large tables is to cache the
    count(*) at the application level (using memcached, terracotta, or
    something along those lines) and then increment that cache whenever
    you add a row to the relevant table.&nbsp; On application restart that
    cache is re-initialized with a regular old count(*).&nbsp; This approach
    works really well and all large systems in my experience need
    caching in front of the DB eventually.&nbsp; If you have a simpler system
    with say a single application/web server you can simply store the
    value in a variable, the specifics would depend on the language and
    framework you are using.<br>
    <br>
    Another more all-DB approach is to create a statistics tables into
    which you place aggregated statistics rows (num deleted, num
    inserted, totals, etc) at an appropriate time interval in your
    code.&nbsp; So you have rows containing aggregated statistics information
    for the past and some tiny portion of the new data happening right
    now that hasn't yet been aggregated.&nbsp; Queries then look like a
    summation of the aggregated values in the statistics table plus a
    count(*) over just the newest portion of the data table and are
    generally very fast.<br>
    <br>
    Overall I have found that once things get big the layers of your app
    stack start to blend together and have to be combined in clever ways
    to keep speed up.&nbsp; Postgres is a beast but when you run into things
    it can't do well just find a way to cache it or make it work
    together with some other persistence tech to handle those cases.<br>
    <br>
    <br>
  </body>
</html>

--------------090601040705070507060403--