Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7D2B69FB46A for ; Wed, 18 Jul 2007 08:32:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 41528-06-2 for ; Wed, 18 Jul 2007 08:32:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by postgresql.org (Postfix) with ESMTP id DF3649FA410 for ; Wed, 18 Jul 2007 08:32:11 -0300 (ADT) Received: from osgiliath.mathom.us ([72.66.0.221]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JLD00BL6HCYAIV3@vms042.mailsrvcs.net> for pgsql-performance@postgresql.org; Wed, 18 Jul 2007 06:31:46 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by osgiliath.mathom.us (Postfix) with ESMTP id 1FDE17155B for ; Wed, 18 Jul 2007 07:31:45 -0400 (EDT) Received: from osgiliath.mathom.us ([127.0.0.1]) by localhost (osgiliath.home.mathom.us [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 2bSod39QLs-O for ; Wed, 18 Jul 2007 07:31:43 -0400 (EDT) Received: by osgiliath.mathom.us (Postfix, from userid 1000) id 8145871575; Wed, 18 Jul 2007 07:31:43 -0400 (EDT) Date: Wed, 18 Jul 2007 07:31:43 -0400 From: Michael Stone Subject: Re: insert vs select into performance In-reply-to: <469D2D59.9030106@ifi.uio.no> To: pgsql-performance@postgresql.org Mail-followup-to: pgsql-performance@postgresql.org Message-id: <20070718113141.GH12481@mathom.us> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii; format=flowed Content-disposition: inline X-Virus-Scanned: Debian amavisd-new at mathom.us X-Pgp-Fingerprint: 53 FF 38 00 E7 DD 0A 9C 84 52 84 C5 EE DF 7C 88 References: <469D1AD3.9030004@ifi.uio.no> <0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net> <5666.1184704280@sss.pgh.pa.us> <469D2D59.9030106@ifi.uio.no> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/210 X-Sequence-Number: 25894 On Tue, Jul 17, 2007 at 10:58:01PM +0200, Thomas Finneid wrote: >I am not sure I understand you correctly here, are you saying that >SELECT INTO in 8.1 disables WAL logging and uses just a single fsync at >the end? in that case it means that I could disable WAL as well and >achieve the same performance, does it not? Yes. The difference is that the select into optimization just means that if the system crashes the data you're inserting is invalid (and is properly cleaned up), and disabling the WAL means that if the system crashes everything is invalid (and can't be cleaned up). Mike Stone