Re: Question about optimizing access to a table.

Herouth Maoz <herouth(at)unicell(dot)co(dot)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