> On Tuesday 04 September 2007 11:27:07 rafael@xxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
