Re: more problems with count(*) on large table

On 9/28/07, Mike Charnoky <noky@nextbus.com> wrote:
> Hi,
>
> I am still having problems performing a count(*) on a large table.  This
> is a followup from a recent thread:
>
> http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
>
> Since the last time these problems happened, we have tweaked some
> postgresql config parameters (fsm, etc).  I also recreated the large
> table, with the assumption it was somehow corrupted.
>
> Now, certain count(*) queries are failing to complete for certain time
> ranges (I killed the query after about 24 hours).  The table is indexed
> on a timestamp field.  Here is one query that hangs:
>
> select count(*) from mytable where evtime between '2007-09-26' and
> '2007-09-27';
>
> However, this query runs successfully and takes 2 minutes:
>
> select count(*) from mytable where evtime between '2007-09-25' and
> '2007-09-26';

I had a similar problem pop up recently in my medium large reporting
database.  I found the problem solved by two things, one was upping
the stats target on the time column to 100, and the other was running
regularly scheduled analyzes on the large reporting table, about once
every 8 hours.

autovacuum works fine otherwise, but was not running analyze often enough