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@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Sean Chen <zyschen@gmail.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
>