Re: How to Find Cause of Long Vacuum Times - NOOB Question

On Sat, May 05, 2007 at 09:52:56PM -0700, Yudhvir Singh Sidhu wrote:
> Here is what I think the story is:
> a. Large amounts of rows are added to and deleted from a table - daily.
> With this much activity, the statistics get out of whack easily. That's
> where ANALYZE or VACUUM ANALYZE would help with query speed.

You are still confusing ANALYZE and VACUUM. Those are distinct operations,
and help for different reasons.

Deleting rows leaves "dead rows" -- for various reasons, Postgres can't
actually remove them from disk at the DELETE point. VACUUM scans through the
disk, searching for dead rows, and actually marks them as removed. This
results in faster query times since there will be less data overall to search
for.

ANALYZE updates the statistics, as mentioned. Yes, by adding or deleting a
lot of data, the estimates can get out of whack, leading to bad query plans.

> b. If ANALYZE does not have a direct impact on vacuum times, what does?
> Meaning what in this EXPLAIN/ANALYZE and Indexing world would have a
> direct impact?

Improving your vacuum speed is overall not that easy (although there are
options you can tweak, and you can of course improve your hardware). The
simplest thing to do is simply to vacuum more often, as there will be less
work to do each time. It's a bit like cleaning your house -- it might be
less work to clean it once a year, but it sure is a better idea in the long
run to clean a bit every now and then. :-)

/* Steinar */
--
Homepage: http://www.sesse.net/