more problems with count(*) on large table - Mailing list pgsql-general

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

shared_buffers = 8000
temp_buffers = 1000
work_mem = 16384
maintenance_work_mem = 262144
max_fsm_pages = 500000
max_fsm_relations = 30000


Mike

pgsql-general by date:

Previous
From: Nico Sabbi
Date:
Subject: row->ARRAY or row->table casting?
Next
From: Jeff Davis
Date:
Subject: Re: DAGs and recursive queries