Re: vacuum performance on insert

hi, thank you for the reply.

I ran a number of tests to try to make sense of this.

When I ran with or without vacuum, the number of disk io operations,
cache operations etc. gathered from pg_stat table for the insertions
are pretty much the same.

So I don't see vacuum reduce disk io operations.

Now from what you mentioned below, do you know what's the cost of
postgres requesting new disk space from OS?

I'm seeing a big performance difference with vacuum, but I need a
proof to show it's the requesting new space operation that was the
problem, not disk io, etc. since I would think disk could be expensive
as well.

Thanks,
Sean

On Thu, Aug 5, 2010 at 2:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Sean Chen <zyschen(at)gmail(dot)com> wrote:
>>> 1, delete records ...
>>> 2, insert records ...
>>>
>>> if I add "vacuum analyze" in-between this two steps, will it help
>>> on the performance on the insert?
>
>> Assuming there are no long-running transactions which would still be
>> able to see the deleted rows, a VACUUM between those statements
>> would allow the INSERT to re-use the space previously occupied by
>> the deleted rows, rather than possibly needing to allocate new space
>> from the OS.
>
> But on the other side of the coin, the ANALYZE step is probably not very
> helpful there.  Better to do that after you've loaded the new data.
>
>                        regards, tom lane
>