tomrevam 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.
Is one client connection making all the inserts/deletes?
Are you holding a transaction open for long periods of time?
Can you post an explain analyze'es'es for (1) when its quick and (2)
when its slow?
( what is the plural of explain analyze? :-) )
-Andy