Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C900D9FB53D for ; Tue, 13 Mar 2007 08:06:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 36594-05 for ; Tue, 13 Mar 2007 08:06:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id F32E49FB52D for ; Tue, 13 Mar 2007 08:06:30 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so210602ugf for ; Tue, 13 Mar 2007 04:06:29 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=L3M7B6kQrobNCGNDsPmSR3qMshtckYItsKb4Vip877MWTMMSFCrn454jmaGD1hyrfykoKQlDHjH4kA47H6JFBiRnaOrbm7eTS6pzruHRqD5xXBykK8zhPUurR1JDD++dBqEhTcFOyGF68PP9Iz1QRK17SPLvffmZcOuCVzjuLnc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=hGKnlSGNIwjjU4DalIW9iyxM5MTJsk4fFjelj338ad2EjuMfE00Scdk5g6fnwOw8oIZXRKCsTFktFMeHtScPlDlDn3LPWjiEsaHFTPY4Lvj/mS/V4l12Zo4G6G2XjL/erHX8Brg0m7J9RUqr1mkgdOVgu9aue1ZskObd3oJm6g0= Received: by 10.67.98.9 with SMTP id a9mr1460103ugm.1173783989436; Tue, 13 Mar 2007 04:06:29 -0700 (PDT) Received: from ?172.24.32.132? ( [62.232.55.118]) by mx.google.com with ESMTP id k1sm9385340ugf.2007.03.13.04.06.26; Tue, 13 Mar 2007 04:06:27 -0700 (PDT) Message-ID: <45F685B7.5040001@enterprisedb.com> Date: Tue, 13 Mar 2007 11:06:31 +0000 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.8 (X11/20061116) MIME-Version: 1.0 To: femski CC: pgsql-performance@postgresql.org Subject: Re: Postgres batch write very slow - what to do References: <9452092.post@talk.nabble.com> In-Reply-To: <9452092.post@talk.nabble.com> 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: 200703/212 X-Sequence-Number: 23617 femski wrote: > I have a batch application that writes approx. 4 million rows into a narrow > table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off. > Batch size is 100. So far I am seeing Postgres take roughly five times the > time it takes to do this in the Oracle. The usual tricks are: - Drop indexes before loading, and rebuild them afterwards. - Disable foreign key references while loading - Use COPY instead of JDBC Is the loading I/O or CPU bound? How long does the load actually take? Are you running the latest version of PostgreSQL and the JDBC driver? Have you read the "Populating a Database" chapter in the manual: http://www.postgresql.org/docs/8.2/interactive/populate.html -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com