X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 028B132A341 for ; Wed, 20 Oct 2004 18:21:16 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66940-04 for ; Wed, 20 Oct 2004 17:21:11 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id F400E32A31C for ; Wed, 20 Oct 2004 18:21:11 +0100 (BST) Received: from [134.22.69.212] (dyn-69-212.tor.dsl.tht.net [134.22.69.212]) by tht.net (Postfix) with ESMTP id 96D0B76BA8; Wed, 20 Oct 2004 13:21:13 -0400 (EDT) Subject: Re: Insert performance, what should I expect? From: Rod Taylor To: Robert Creager Cc: Brock Henry , Postgresql Performance In-Reply-To: <20041020104527.14927c06@thunder.mshome.net> References: <97b3fe2041019185337a8c3d8@mail.gmail.com> <1098238348.747.20.camel@home> <20041020104527.14927c06@thunder.mshome.net> Content-Type: text/plain Message-Id: <1098292819.747.188.camel@home> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Wed, 20 Oct 2004 13:20:19 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/326 X-Sequence-Number: 8802 On Wed, 2004-10-20 at 12:45, Robert Creager wrote: > When grilled further on (Tue, 19 Oct 2004 22:12:28 -0400), > Rod Taylor confessed: > > > > I've done some manual benchmarking running my script 'time script.pl' > > > I realise my script uses some of the time, bench marking shows that > > > %50 of the time is spent in dbd:execute. > > > > > 1) Drop DBD::Pg and switch to the Pg driver for Perl instead (non-DBI > > compliant) which has functions similar to putline() that allow COPY to > > be used. > > COPY can be used with DBD::Pg, per a script I use: > > $dbh->do( "COPY temp_obs_$band ( $col_list ) FROM stdin" ); > $dbh->func( join ( "\t", @data ) . "\n", 'putline' ); > $dbh->func( "\\.\n", 'putline' ); > $dbh->func( 'endcopy' ); Thanks for that. All of the conversations I've seen on the subject stated that DBD::Pg only supported standard DB features -- copy not amongst them. > With sets of data from 1000 to 8000 records, my COPY performance is consistent > at ~10000 records per second. Well done.