Skip site navigation (1) Skip section navigation (2)

Re: query is taking longer time after a while

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: tomrevam <tomer(at)fabrix(dot)tv>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: query is taking longer time after a while
Date: 2009-09-30 02:17:22
Message-ID: dcc563d10909291917r605f758dua6acfd7a690089f5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

Responses

pgsql-general by date

Next:From: tomrevamDate: 2009-09-30 06:26:54
Subject: Re: query is taking longer time after a while
Previous:From: Vick KheraDate: 2009-09-30 02:01:36
Subject: Re: do I need a rollback() after commit that fails?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group