Herouth Maoz <herouth@unicell.co.il> wrote:
> The problem starts when our partner has some glitch, under high
> load, and fails to send back a few hundred thousand reports. In
> that case, the table grows to a few hundred records, and they are
> not deleted until they hit their expiry date, at which point the
> "garbage collector" takes care of them and everything goes back
> to normal. When it contains hundreds of thousands of records,
> performance deteriorates considerably-
First, make sure that you are on the latest minor release of
whatever major release you are running. There were some serious
problems with autovacuum's table truncation when a table was used
as a queue and size fluctuated. These are fixed in the latest set
of minor releases.
If that doesn't clear up the problem, please post an actual slow
query to the pgsql-performance list, with its EXPLAIN ANALYZE
output and other details, as suggested here:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
People will be able to provide more useful and specific advice if
they have the additional detail.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company