On Tue, Sep 29, 2009 at 5:23 AM, tomrevam <tomer(at)fabrix(dot)tv> wrote:
> I have a table with 5 million rows. 10 inserts and deletes are performed on
> this table every second.
> The table has indexes on the columns I use to query it, and the query is
> returning about a 1000 rows. Initially the query takes a very short time
> (order of miliseconds), after a few hours it takes hundreds of miliseconds,
> and after a few days it can take more than 10 seconds. When this happens it
> also blocks all other operations on the database and I see very long times
> for all of them.
> I thought this may be caused by the indexes not remaining in the memory, but
> I increased the shared_buffers to 0.5 GB and this didn't seem to help.
Just wondering, what version of pgsql are you running? I noticed a
lot less degradation from heavily updated tables when I went to 8.3
and set the fill % for tables / indexes to 90% or so. If you're
running an older version, the upgrade to 8.3 may well be worth the
In response to
pgsql-general by date
|Next:||From: tomrevam||Date: 2009-09-30 06:26:54|
|Subject: Re: query is taking longer time after a while|
|Previous:||From: Vick Khera||Date: 2009-09-30 02:01:36|
|Subject: Re: do I need a rollback() after commit that fails?|