Re: A question about Vacuum analyze

On fös, 2006-02-17 at 12:06 -0500, Emi Lu wrote:
> >>>>In another way, whenever we "delete/truncate and then insert" data into
> >>>>a table, it is better to "vacuum anaylze"?
> >>>>
> ...
> >>
> >>So, your suggestion is that after the population of table A, the query
> >>planner should be able to find the most efficient query plan because we
> >>do truncate but not delete, and we do not need to do vacuum analyze at
> >>all, right?
> >>
> >>
> ...
>
> Thank you gnari for your answer. But I am a bit confused about not running vacuum but only "analyze". Can I seperate these two operations? I guess "vacuum analyze" do both vacuum and analyze.
> Or "EXPLAIN ANALYZE" can do it for me?

VACUUM ensures that dead rows can be reused. Dead rows
are created by DELETE and UPDATE.
If you have done a significant number of DELETEs
or UPDATEs, you might want to VACUUM

ANALYZE collect statistical information about
your tables. this helps the planner make good plans.
After having changed your data significantly, you
might want to ANALYZE, for example after lots of
INSERTs, UPDATEs or DELETEs

TRUNCATE does not create dead rows, so you do
not need to VACUUM just because of that, but
you still might have to ANALYZE.

If you TRUNCATE a table and then repopulate it
with similar data as before, you do not have to
ANALYZE, as plans based on the old statistics
would assumedly be just as good.

EXPLAIN dislays the plan that will be chosen
for a query, along with some estimated cost
information.

EXPLAIN ANALYZE actually executes the query, and
shows same info as EXPLAIN, and in addition actual
cost information

Hope this makes it more clear

gnari