Hi, I'm using PostgreSQL 8.1.8 and am having trouble with a table which contains a large amount of data. Data is constantly being inserted into the table, roughly a million inserts per hour at peak. The table currently has about 100 million entries which take up 14G of space (24G with indices). The problem in nutshell: I noticed that certain queries were excruciatingly slow, despite the use of an index. A vacuum analyze of the table would not complete (despite running for 2 days). A reindex also failed to complete after one day. The details: I was trying to perform a count(*) based on a timestamp field in the table (which is indexed). An EXPLAIN ANALYZE showed a high cost even though an index scan was used. I tried to VACUUM ANALYZE the table, thinking this might help. Yes, autovacuum is turned on, but since pg8.1 does not store info about when a table was last vacuumed, I decided to run this manually. After several hours, the vacuum did not complete. So, I disabled the process which was writing to this table and tried "set vacuum_cost_delay=0" before vacuuming. After two days, the vacuum did not complete, so I stopped it and tried to reindex the table, thinking that indices were corrupted. This also failed to complete after one day. At this point, I'm at a loss. I've searched the archives for similar problems, but none of the suggestions have worked. Is the data in this table corrupted? Why are both vacuum and reindex failing to complete? Is there some sort of fine-tuning I should pay attention to? Any help is much appreciated. Mike ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
