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