Re: postgresql performance degradation over time....

Bruno Wolff III wrote:
> On Sat, Aug 27, 2005 at 18:19:54 +0530,
>   sunil arora <arora.sunil@gmail.com> wrote:
>
>>Bruno,
>>thanks for the reply,
>>we did run vaccum on it.. and we do it regulary to maintain its
>>performance but its not giving the expected results.
>
>
> Did you do VACUUM FULL or just plain VACUUM?
>
>
>>I dont know but if we delete the entire database and restore it with
>>the dump, then things seems to improve a _LOT_.
>>Isnt vaccum suppose to do the same task for us ??
>>what could be going any idea ??
>
>
> It sounds like you have a lot of dead tuples or index bloat. I think 7.4
> had the main index bloat issue fixed, but I think that it was still possible
> to get bloated indexes in some circumstances. So it might be worth trying
> to reindex the tables.
>
> Note that plain VACUUM only does the job it is supposed to if your FSM
> setting is large enough to handle all of the dead tuples in a table. It
> also doesn't move valid tuples around to allow the underlying files to
> be reduced to the minimum size needed. If things have gotten bad enough
> you want to do a VACUUM full. (Cluster can be a faster way to do this,
> but for only a couple of Gigs of data, it may not be worth the trouble.)
>
Also dont forget to run ANALYZE after your vacuum, or simply VACUUM
[FULL] ANALYZE;