X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E76A09DCD35
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Dec 2005 10:00:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 88183-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Dec 2005 10:00:03 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.186])
	by postgresql.org (Postfix) with ESMTP id B7FD19DCAB5
	for <pgsql-performance@postgresql.org>;
	Thu,  1 Dec 2005 10:00:05 -0400 (AST)
Received: from [84.143.28.162] (helo=pse.dyndns.org)
	by mrelayeu.kundenserver.de (node=mrelayeu6) with ESMTP (Nemesis),
	id 0ML29c-1Ehoyn0F7B-0002rH; Thu, 01 Dec 2005 15:00:09 +0100
Received: from pse1 ([192.168.0.3]) by pse.dyndns.org with esmtp (Exim 4.44)
	id 1Ehoym-0003Ho-LF; Thu, 01 Dec 2005 15:00:08 +0100
Message-ID: <438F01E7.9000300@pse-consulting.de>
Date: Thu, 01 Dec 2005 14:00:07 +0000
From: Andreas Pflug <pgadmin@pse-consulting.de>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Wolfgang Gehner <wgehner@infonoia.com>
CC: pgsql-performance@postgresql.org
Subject: Re: slow insert into very large table
References: <438EED91.9080302@infonoia.com>
In-Reply-To: <438EED91.9080302@infonoia.com>
X-Enigmail-Version: 0.89.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: kundenserver.de abuse@kundenserver.de
	login:0ce7ee5c3478b8d72edd8e05ccd40b70
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/9
X-Sequence-Number: 15830

Wolfgang Gehner wrote:
> Hi there,
> 
> I need a simple but large table with several million records. I do batch 
> inserts with JDBC. After the first million or so records,
> the inserts degrade to become VERY slow (like 8 minutes vs initially 20 
> secondes).
> 
> The table has no indices except PK while I do the inserts.
> 
> This is with PostgreSQL 8.0 final for WindowsXP on a Pentium 1.86 GHz, 
> 1GB Memory. HD is fast IDE.
> 
> I already have shared buffers already set to 25000.
> 
> I wonder what else I can do. Any ideas?

Run VACUUM ANALYZE to have statistics reflect the growth of the table. 
The planner probably still assumes your table to be small, and thus 
takes wrong plans to check PK indexes or so.

Regards,
Andreas