From: Mark Stosberg <mark@summersault.com>
To: pgsql-performance@postgresql.org
Subject: Re: bulk insert performance problem
Date: Tue, 08 Apr 2008 09:50:40 -0400
Message-ID: <ftft7g$19r$2@ger.gmane.org> (raw)
In-Reply-To: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
References: <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
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: mark@summersault.com
Subject: Re: bulk insert performance problem
In-Reply-To: <ftft7g$19r$2@ger.gmane.org>
* 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