Re: Vacum Analyze problem

> On Tuesday 04 September 2007 11:27:07 rafael@akyasociados.com.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@postgresql.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