Re: Question about optimizing access to a table.

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

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

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 have to 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 form of SELECT and DELETE possible.

Herouth