From: Yudhvir Singh Sidhu <ysidhu@gmail.com>
To: Yudhvir Singh Sidhu <ysidhu@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
Date: Sun, 06 May 2007 02:35:30 -0700
Message-ID: <463DA162.8050605@gmail.com> (raw)
In-Reply-To: <20070506091707.GB22451@uio.no>
References: <463D0BD5.3010404@gmail.com>
<20070505234014.GA15522@uio.no>
<463D5F28.5060006@gmail.com>
<20070506091707.GB22451@uio.no>
Steinar H. Gunderson wrote:
> 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 */
>
Thanks for the clarification Steingar,
I'll try some of the things we discussed out on Monday and will let you
guys know what happens. I know I am confusing some concepts but I am new
to this db and to tuning in general. I am excited about this new
adventure and really appreciate the level of support I have seen.
Yudhvir
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: ysidhu@gmail.com
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
In-Reply-To: <463DA162.8050605@gmail.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