From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: batch inserts are "slow"
Date: Mon, 02 May 2005 12:16:42 -0400
Message-ID: <60wtqhy55h.fsf@dba2.int.libertyrms.com> (raw)
References: <Pine.LNX.4.44.0505021526450.7150-100000@naskur.se.linux.org>
<59d991c405050208174b6ebbc@mail.gmail.com>
petrilli@gmail.com (Christopher Petrilli) writes:
> On 5/2/05, Tim TerlegÄrd <tim@se.linux.org> wrote:
>> Howdy!
>>
>> I'm converting an application to be using postgresql instead of
>> oracle. There seems to be only one issue left, batch inserts in
>> postgresql seem 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 several more minutes to complete the task. By fixing
>> this I think the application using postgresql over all would be
>> faster than when using oracle.
>
> Just as on Oracle you would use SQL*Loader for this application, you
> should use the COPY syntax for PostgreSQL. You will find it a lot
> faster. I have used it by building the input files and executing
> 'psql' with a COPY command, and also by using it with a subprocess,
> both are quite effective.
I'd suggest taking a peek at the PGForge project, pgloader
<http://pgfoundry.org/projects/pgloader/;.
This is intended to provide somewhat analagous functionality to
SQL*Loader; a particularly useful thing about it is that it will load
those records that it can, and generate a file consisting of just the
failures.
It uses COPY, internally, so it does run reasonably fast.
To the extent to which it is inadequate, it would be neat to see some
enhancements...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;
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-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: batch inserts are "slow"
In-Reply-To: <60wtqhy55h.fsf@dba2.int.libertyrms.com>
* 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