Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 474CD2E00CF for ; Mon, 10 Mar 2008 09:16:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64801-01 for ; Mon, 10 Mar 2008 09:16:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [202.61.230.242]) by postgresql.org (Postfix) with ESMTP id 86AE32E0073 for ; Mon, 10 Mar 2008 09:16:42 -0300 (ADT) Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by www.postnewspapers.com.au (Postfix) with ESMTP id C14EECD7AF; Mon, 10 Mar 2008 21:16:36 +0900 (WST) Received: from localhost (access [127.0.0.1]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 9B62A1E0435; Mon, 10 Mar 2008 21:16:36 +0900 (WST) X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au Received: from mail.postnewspapers.com.au ([127.0.0.1]) by localhost (access.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id bjYe6CIs26Rr; Mon, 10 Mar 2008 21:16:34 +0900 (WST) Received: from [203.161.97.213] (203.161.97.213.static.amnet.net.au [203.161.97.213]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "Craig Ringer", Issuer "POST Certificate Authority" (verified OK)) by mail.postnewspapers.com.au (Postfix) with ESMTP id F40EB1E0434; Mon, 10 Mar 2008 21:16:33 +0900 (WST) Message-ID: <47D5269B.8060103@postnewspapers.com.au> Date: Mon, 10 Mar 2008 21:16:27 +0900 From: Craig Ringer User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: Heikki Linnakangas Cc: pgsql-performance@postgresql.org Subject: Re: Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit References: <47D505AF.4040101@postnewspapers.com.au> <47D5150C.8090802@enterprisedb.com> In-Reply-To: <47D5150C.8090802@enterprisedb.com> X-Enigmail-Version: 0.95.6 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/195 X-Sequence-Number: 29149 Heikki Linnakangas wrote: > You must be having an exception handler block in that pl/pgsql > function, which implicitly creates a new subtransaction on each > invocation of the exception handler block, so you end up with hundreds > of thousands of committed subtransactions. I've just confirmed that that was indeed the issue, and coding around the begin block dramatically cuts the runtimes of commands executed after the big import function. Thanks again! -- Craig Ringer