Обсуждение: Running analysis as part of a stored proc

Is it advisable to include a VACUUM ANALYZE <table name> within a stored
procedure that runs as part of a batch every night?

Kind regards
Yiannis

Ioannis Anagnostopoulos <ioannis@anatec.com> writes:
> Is it advisable to include a VACUUM ANALYZE <table name> within a stored
> procedure that runs as part of a batch every night?

If you're envisioning this as cleanup after the day's activities,
autovacuum will almost certainly make that unnecessary.

The typical cases where you need a manual vacuum or analyze in a stored
proc are where you need the cleanup or new stats immediately and can't
wait for autovacuum to get around to it.  So if this is a step in a
process where you just modified the table heavily and you need the
cleanup done before you get to the next step, then yes it'd make sense.

            regards, tom lane

On 28/07/2012 21:00, Tom Lane wrote:
> Ioannis Anagnostopoulos <ioannis@anatec.com> writes:
>> Is it advisable to include a VACUUM ANALYZE <table name> within a stored
>> procedure that runs as part of a batch every night?
> If you're envisioning this as cleanup after the day's activities,
> autovacuum will almost certainly make that unnecessary.
>
> The typical cases where you need a manual vacuum or analyze in a stored
> proc are where you need the cleanup or new stats immediately and can't
> wait for autovacuum to get around to it.  So if this is a step in a
> process where you just modified the table heavily and you need the
> cleanup done before you get to the next step, then yes it'd make sense.
>
>             regards, tom lane
>
Hi Tom and thank you. The second scenario is exactly what I had in my
mind. As the tables have
constant inserts, it might be a good idea to run the analyse just before
executing the night batch.

Kind Regards
Yiannis