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 F2B8D53690 for ; Mon, 2 May 2005 14:04:13 -0300 (ADT) 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 40580-07 for ; Mon, 2 May 2005 17:04:08 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 269095364B for ; Mon, 2 May 2005 14:04:07 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id A2C0C30952; Mon, 2 May 2005 19:02:20 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.performance Subject: Re: batch inserts are "slow" Date: Mon, 02 May 2005 12:16:42 -0400 Organization: cbbrowne Computing Inc Lines: 38 Message-ID: <60wtqhy55h.fsf@dba2.int.libertyrms.com> References: <59d991c405050208174b6ebbc@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:nyksrB2eSi9NtHXM1q2RaIenbAM= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.157 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/10 X-Sequence-Number: 12174 petrilli@gmail.com (Christopher Petrilli) writes: > On 5/2/05, Tim Terlegård wrote: >> Howdy! >> >> I'm converting an application to be using postgresql instead of >> oracle. There seems to be only one issue left, batch inserts in >> postgresql seem significant slower than in oracle. I have about 200 >> batch jobs, each consisting of about 14 000 inserts. Each job takes >> 1.3 seconds in postgresql and 0.25 seconds in oracle. With 200 jobs >> this means several more minutes to complete the task. By fixing >> this I think the application using postgresql over all would be >> faster than when using oracle. > > Just as on Oracle you would use SQL*Loader for this application, you > should use the COPY syntax for PostgreSQL. You will find it a lot > faster. I have used it by building the input files and executing > 'psql' with a COPY command, and also by using it with a subprocess, > both are quite effective. I'd suggest taking a peek at the PGForge project, pgloader . This is intended to provide somewhat analagous functionality to SQL*Loader; a particularly useful thing about it is that it will load those records that it can, and generate a file consisting of just the failures. It uses COPY, internally, so it does run reasonably fast. To the extent to which it is inadequate, it would be neat to see some enhancements... -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"