From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: How import big amounts of data?
Date: Thu, 29 Dec 2005 11:50:49 +0100
Message-ID: <20051229105049.GB27287@uio.no> (raw)
In-Reply-To: <43B3B0EA.6070406@andromeiberica.com>
References: <43B3B0EA.6070406@andromeiberica.com>
On Thu, Dec 29, 2005 at 10:48:26AM +0100, Arnau wrote:
> Which is the best way to import data to tables? I have to import
> 90000 rows into a column and doing it as inserts takes ages. Would be
> faster with copy? is there any other alternative to insert/copy?
There are multiple reasons why your INSERT might be slow:
- Are you using multiple transactions instead of batching them in all or a
few transactions? (Usually, the per-transaction cost is a lot higher than
the per-row insertion cost.)
- Do you have a foreign key without a matching index in the other table? (In
newer versions of PostgreSQL, EXPLAIN ANALYZE can help with this; do a
single insert and see where it ends up doing work. Older won't show such
things, though.)
- Do you have an insertion trigger taking time? (Ditto wrt. EXPLAIN ANALYZE.)
COPY will be faster than INSERT regardless, though (for more than a few rows,
at least).
/* Steinar */
--
Homepage: http://www.sesse.net/
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: sgunderson@bigfoot.com
Subject: Re: How import big amounts of data?
In-Reply-To: <20051229105049.GB27287@uio.no>
* 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