From: Kevin Kempter <kevin@kevinkempterllc.com>
To: pgsql-performance@postgresql.org
Subject: Re: Vacum Analyze problem
Date: Tue, 4 Sep 2007 11:45:21 -0600
Message-ID: <200709041145.21363.kevin@kevinkempterllc.com> (raw)
In-Reply-To: <1275.192.168.1.229.1188926827.squirrel@webmail.akyasociados.com.ar>
References: <1275.192.168.1.229.1188926827.squirrel@webmail.akyasociados.com.ar>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kevin@kevinkempterllc.com
Subject: Re: Vacum Analyze problem
In-Reply-To: <200709041145.21363.kevin@kevinkempterllc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox