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 DEA26533C1 for ; Tue, 3 May 2005 15:30: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 69201-08 for ; Tue, 3 May 2005 18:30:03 +0000 (GMT) Received: from noao.edu (noao.edu [140.252.1.54]) by svr1.postgresql.org (Postfix) with ESMTP id 9B5BE5408B for ; Tue, 3 May 2005 15:30:02 -0300 (ADT) X-TFF-CGPSA-Version: 1.4f1 X-TFF-CGPSA-Filter: Scanned Received: from [140.252.14.8] (HELO weaver.tuc.noao.edu) by noao.edu (CommuniGate Pro SMTP 4.3c5) with ESMTPS id 17999795; Tue, 03 May 2005 11:29:55 -0700 Received: from [127.0.0.1] (localhost.localdomain [127.0.0.1]) by weaver.tuc.noao.edu (8.13.1/8.13.1) with ESMTP id j43ITs3V028685; Tue, 3 May 2005 11:29:55 -0700 Message-ID: <4277C322.2010901@noao.edu> Date: Tue, 03 May 2005 11:29:54 -0700 From: Steve Wampler User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: tim@se.linux.org Cc: Christopher Petrilli , pgsql-performance@postgresql.org Subject: Re: batch inserts are "slow" References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/23 X-Sequence-Number: 12187 Tim Terlegård wrote: >> >>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 tried this now. Now it's down to 0.45 seconds. It feels a bit hacky to > run /usr/bin/psql from java, but it sure works. Thanks for the hint! There was a patch against 7.4 that provided direct JDBC access to PostgreSQL's COPY. (I have it installed here and *love* it - it gives outstanding performance.) However, it hasn't made into an official release yet. I don't know why, perhaps there's a problem yet to be solved with it ('works for me', though)? Is this still on the board? I won't upgrade past 7.4 without it. -- Steve Wampler -- swampler@noao.edu The gods that smiled on your birth are now laughing out loud.