Search Postgresql Archives

more problems with count(*) on large table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux