Re: query is taking longer time after a while

In response to Sam Mason <sam(at)samason(dot)me(dot)uk>:

> On Tue, Sep 29, 2009 at 03:13:49PM +0200, Brian Modra wrote:
> > 2009/9/29 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> > > 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/