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 115F65321F for ; Mon, 2 May 2005 12:27:44 -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 15876-08 for ; Mon, 2 May 2005 15:27:38 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.193]) by svr1.postgresql.org (Postfix) with ESMTP id 866A1531FE for ; Mon, 2 May 2005 12:27:37 -0300 (ADT) Received: by rproxy.gmail.com with SMTP id a41so912019rng for ; Mon, 02 May 2005 08:27:37 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ZI4IJD44egRl7HYxrYXCJsNpcgdZgA3erbE0xJHR5YpnM8zn0XUVddvbh0GXQ4FLePbiVQauuuhk9Xk3k4hQOyEzwc8xsrYq8wdSyBxcd/6dF0JzA646zyq3kleTzkM68Zf0pkenulqnxTgxnjt/gJ94XIq5XOMEa3/jvxAUAdM= Received: by 10.38.149.48 with SMTP id w48mr6314657rnd; Mon, 02 May 2005 08:27:37 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Mon, 2 May 2005 08:27:37 -0700 (PDT) Message-ID: <59d991c40505020827e694eeb@mail.gmail.com> Date: Mon, 2 May 2005 11:27:37 -0400 From: Christopher Petrilli Reply-To: Christopher Petrilli To: tim@se.linux.org Subject: Re: batch inserts are "slow" Cc: Pawel Bernat , pgsql-performance@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20050502160321.A14165@vlo> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.319 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200505/6 X-Sequence-Number: 12170 On 5/2/05, Tim Terleg=E5rd wrote: > > > Howdy! > > > > > > I'm converting an application to be using postgresql instead of oracl= e. > > > There seems to be only one issue left, batch inserts in postgresql se= em > > > 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 sever= al > > > more minutes to complete the task. By fixing this I think the > > > application using postgresql over all would be faster than when using > > > oracle. > > > > Have you tried COPY statement? >=20 > I did that now. I copied all 3 million rows of data into a text file and > executed the COPY command. It takes about 0.25 seconds per job. So that's > much better. I'm afraid jdbc doesn't support COPY though. But now I know > what the theoretical lower limit is atleast. >=20 > Should it be possible to get anyway nearer 0.25s from my current 1.3s? My experience says 'no'. What you're likely seeing is the parse overhead of the setup. When you use COPY (as opposed to \copy), the postmaster is reading the file directory. There's just a lot less overhead. Can you write the files on disk and then kick off the psql process to run t= hem? Chris --=20 | Christopher Petrilli | petrilli@gmail.com