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@xxxxxxxxxxxxx> wrote: > "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes: >> Sean Chen <zyschen@xxxxxxxxx> 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
