more problems with count(*) on large table

Поиск
Список
Период
Сортировка
От Mike Charnoky
Тема more problems with count(*) on large table
Дата
Msg-id 46FD243E.1040205@nextbus.com
обсуждение исходный текст
Ответы Re: more problems with count(*) on large table  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: more problems with count(*) on large table  (Sean Davis <sdavis2@mail.nih.gov>)
Re: more problems with count(*) on large table  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Nico Sabbi
Дата:
Сообщение: row->ARRAY or row->table casting?
Следующее
От: Jeff Davis
Дата:
Сообщение: Re: DAGs and recursive queries