Re: Vacum Analyze problem

> On Tuesday 04 September 2007 11:27:07 rafael(at)akyasociados(dot)com(dot)ar wrote:
>> Hello everyone:
>>
>> I wanted to ask you about how the VACUUM ANALYZE works. is it
>> possible
>> that something can happen in order to reset its effects forcing to
>> execute the VACUUM ANALYZE comand again? i am asking this because i am
>> struggling with a query which works ok after i run a VACUUM ANALYZE,
>> however, sudennly, it starts to take forever (the execution of the
>> query) until i make another VACUUM ANALYZE, and so on ...
>> I'd like to point that i am a novice when it comes to non basic
>> postgresql performance related stuff.
>>
>> Thank you all in advance
>>
>> Rafael
>>
>>
>>
>> ---------------------------(end of
>> broadcast)--------------------------- TIP 1: if posting/reading
>> through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
>> your message can get through to the mailing list cleanly
>
> Rafael;
>
> Vacuum Analyze performs 2 tasks at once.
>
> 1) Vacuum - this analyzes the table pages and sets appropriate dead row
> space (those from old updates or deletes that are not possibly needed
> by any existing transactions) as such that the db can re-use
> (over-write) that space.
>
> 2) Analyze - Like an Oracle compute stats, updates the system catalogs
> with current table stat data.
>
> The Vacuum will improve queries since the dead space can be re-used and
> any dead space if the table you are having issues with is a high
> volume table then the solution is generally to run vacuum more often -
> I've seen tables that needed a vacuum every 5 minutes due to
> significant sustained churn.
>
> The Analyze of course is key for the planner, if the table is growing
> rapidly then running analyze more often will help, if however there is
> lots of churn but little change in the data (i.e. lots of inserts
> followed by delete's of the same rows) then a straight vacuum is
> probably what you need. If the data is changing rapidly then bumping
> up the default_statistics_target value may help - you can bump the
> default_statistics_target for a single table in the pg_autovacuum
> system catalog table.
>
> Hope this helps...
>
> /Kevin
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faq

Thank you all for the information. I'll get to work on it and see what
happends.
Thanks again

Rafael