more problems with count(*) on large table

From: Mike Charnoky <noky(at)nextbus(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: more problems with count(*) on large table
Date: 2007-09-28 15:56:46
Message-ID: 46FD243E.1040205@nextbus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2007-09-28 16:28:57 Re: DAGs and recursive queries
Previous Message Nico Sabbi 2007-09-28 15:37:38 row->ARRAY or row->table casting?