Re: query is taking longer time after a while

On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote:
> 2009/9/29 tomrevam <tomer(at)fabrix(dot)tv>:
> > My DB is auto-vacuuming all the time. The specific table I'm talking about
> > gets vacuumed at least every 2 hours (usually a little more frequently than
> > that).
> > Deletes are happening on the table at about the same rate as inserts (there
> > are also some updates).
>
> The index quite likely is in a poor state.

Really? Plain vacuum should allow things to reach a steady state after
a while, doing a large delete will put things out of kilter, but that
doesn't sound to be the case here. Vacuum full can also cause things to
go amiss, but if it's just regular vacuums then things should be OK.

What do you get out of vacuum analyse verbose? for this table?

> You could try this:
>
> analyse ....
> create index ... (same parameters as existing index)
> delete the old index.
> rename the new index to the same name as the old one
> repeat this for all indexes.

Why not just do:

REINDEX TABLE yourbigtable;

No need to worry about rebuilding foreign key constraints or anything
like that then.

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