From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: Yudhvir Singh Sidhu <ysidhu@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
Date: Sun, 6 May 2007 11:17:07 +0200
Message-ID: <20070506091707.GB22451@uio.no> (raw)
In-Reply-To: <463D5F28.5060006@gmail.com>
References: <463D0BD5.3010404@gmail.com>
<20070505234014.GA15522@uio.no>
<463D5F28.5060006@gmail.com>
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/
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: sgunderson@bigfoot.com, ysidhu@gmail.com
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
In-Reply-To: <20070506091707.GB22451@uio.no>
* 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