Re: Question about optimizing access to a table.

On 10/12/2013, at 20:55, Kevin Grittner wrote:

> 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.

Thank you. Indeed, I failed to mention which version of PostgreSQL I was on.  9.1.2 in this case. Do you mean that I
haveto go to 9.3.x or simply to 9.1.11? 

> 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.


Thank you. I think it's more a matter of design than an issue with the query. The queries themselves are the simplest
formof SELECT and DELETE possible. 

Herouth