Re: query is taking longer time after a while

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

> 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?

> > Why not just do:
> >
> >   REINDEX TABLE yourbigtable;
>
> Thats OK if the table can be taken offline. REINDEX locks the index
> while in progress.

Good point, forgot about that.

--
  Sam  http://samason.me.uk/