Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id MAA46471
	for <pgsql-interfaces@postgreSQL.org>;
	Sat, 4 Dec 1999 12:07:47 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id MAA04501;
	Sat, 4 Dec 1999 12:07:10 -0500 (EST)
To: Brad Pepers <brad@linuxcanada.com>
cc: ALPESH KOTHARI <alpeshkothari@yahoo.com>, pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Speed of Postgres with Java 
In-reply-to: Your message of Sat, 04 Dec 1999 03:24:43 -0700
	<3848EBEB.910DE62A@linuxcanada.com>
Date: Sat, 04 Dec 1999 12:07:10 -0500
Message-ID: <4499.944327230@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Brad Pepers <brad@linuxcanada.com> writes:
> ALPESH KOTHARI wrote:
>> To store such 1000 data it takes as large as 90
>> seconds. I don't have any pre-experience using db.
>> So, is this much time OK?

> You've turned off auto-commit right and just do one commit at
> the end?  Otherwise its doing a lot of work for each record
> added.  In general when you are doing bulk inserts, you want
> to turn off some of the database features to gain speed.

Other commonly used tricks for speeding up bulk inserts are:

1. Use "COPY FROM STDIN" to load all the records in one command, instead
of a series of INSERT commands.  This reduces parsing, planning, etc
overhead a great deal.  (If you do this then it's not necessary to fool
around with autocommit.)

2. If you are loading a freshly created table, the fastest way is to
create the table, bulk-load with COPY, then create any indexes needed
for the table.  Creating an index on pre-existing data is quicker than
updating it incrementally as each record is loaded.  This isn't useful
for adding to an existing table, of course.

			regards, tom lane