Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C43E16324CA for ; Thu, 5 Aug 2010 15:11:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 38384-03 for ; Thu, 5 Aug 2010 18:11:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 98B10632234 for ; Thu, 5 Aug 2010 15:11:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o75IB8Qm026789; Thu, 5 Aug 2010 14:11:08 -0400 (EDT) To: "Kevin Grittner" cc: "Sean Chen" , pgsql-performance@postgresql.org Subject: Re: vacuum performance on insert In-reply-to: <4C5AB5140200002500034276@gw.wicourts.gov> References: <4C5AB5140200002500034276@gw.wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Thu, 05 Aug 2010 12:56:52 -0500" Date: Thu, 05 Aug 2010 14:11:08 -0400 Message-ID: <26788.1281031868@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201008/71 X-Sequence-Number: 39914 "Kevin Grittner" writes: > Sean Chen 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