Re: query is taking longer time after a while

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