From: Tom Lane <tgl@sss.pgh.pa.us>
To: Brad Pepers <brad@linuxcanada.com>
Cc: ALPESH KOTHARI <alpeshkothari@yahoo.com>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Speed of Postgres with Java
Date: Sat, 04 Dec 1999 12:07:10 -0500
Message-ID: <4499.944327230@sss.pgh.pa.us> (raw)
In-Reply-To: <3848EBEB.910DE62A@linuxcanada.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, brad@linuxcanada.com, alpeshkothari@yahoo.com
Subject: Re: [INTERFACES] Speed of Postgres with Java
In-Reply-To: <4499.944327230@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox