To: pgsql-performance@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Re: bulk insert performance problem
Date: Tue, 08 Apr 2008 09:50:40 -0400
Lines: 24
Message-ID: <ftft7g$19r$2@ger.gmane.org>
References: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
In-Reply-To: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>

Christian Bourque wrote:
> 
> Any idea? Is there any other improvements I could do?

Are you using the "COPY" syntax in the import script or individual 
insert statements? Using COPY will always be *much* faster.

I believe COPY always appends to tables rather than replacing the 
contents, you can combine this technique with the possibility of 
splitting up the task into multiple copy statements, but that has never 
been necessary in my case, switching from INSERTS to a COPY statement 
always provided the huge performance improvement I needed.

It's easy to confuse "pg_dump -d" with "psql -d" ...it's too bad they 
mean very different things.

For pg_dump, "-d" causes INSERT statements to be generated instead of a 
COPY statement, and is has been a mistake I made in the past, because I 
expected to work like "psql -d", where "-d" means "database name".

I suppose the safe thing to do is to avoid using "-d" altogether!


	Mark