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

Mike Charnoky 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';
>
>   count
> ----------
>  14150928
> (1 row)
>
> What is going on?  I analyzed the table before running the query and
> have no reason to believe that the amount of data added to the table
> varies much from day to day.  No data has been deleted from the table
> yet, just added.
>
> Here is some config info:
>
> PostgreSQL 8.1.8 on Fedora 3

Have you vacuumed recently?

Sean