In response to Sam Mason <sam@xxxxxxxxxxxxx>: > On Tue, Sep 29, 2009 at 03:13:49PM +0200, Brian Modra wrote: > > 2009/9/29 Sam Mason <sam@xxxxxxxxxxxxx>: > > > Plain vacuum should allow things to reach a steady state after > > > a while, > > > > If there are a lot of deletes, then likely the index parameters are > > not the best. > > My interpretation of the OPs problem was that the inserts and deletes > were happening at similar rates. Thus this won't be a problem. There has (over the last few years) been a lot of speculation from people who think that indexes may suffer performance degradation under some workloads. I've yet to see any actual evidence. So, for my part, if the OP could run an explain analyze on the query, then reindex the tables involved, then another explain analyze for comparison purposes, I'd be very interested to see the results. > > ANALYSE yourtable; > > > > Then, reindex (or create new index followed by drop index and rename - > > if you want to leave the index online. > > Analyse is just about collecting statistics for the planner, I'm not > sure why you'd want to run it before a reindex. Autovacuum was being > run, so it's not going to make much difference is it? Unless he's getting a crappy plan because the stats are getting out of date. That could be determined by explain; analyze; explain, which would allow you to see if you're getting different plans. The OP did mention that he's using autovac, which will take care of both vacuum and analyze for him. However, he didn't provide his autovac config, and it happens at times that the defaults are not aggressive enough to keep a table well-maintained. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
