From: PFC <lists@peufeu.com>
To: Christian Bourque <christian.bourque@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: bulk insert performance problem
Date: Wed, 09 Apr 2008 00:48:35 +0200
Message-ID: <op.t9bdm9bicigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
References: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
> I have a performance problem with a script that does massive bulk
> insert in 6 tables. When the script starts the performance is really
> good but will degrade minute after minute and take almost a day to
> finish!
Looks like foreign key checks slow you down.
- Batch INSERTS in transactions (1000-10000 per transaction)
- Run ANALYZE once in a while so the FK checks use indexes
- Are there any DELETEs in your script which might hit nonidexed
REFERENCES... columns to cascade ?
- Do you really need to check for FKs on the fly while inserting ?
ie. do you handle FK violations ?
Or perhaps your data is already consistent ?
In this case, load the data without any constraints (and without any
indexes), and add indexes and foreign key constraints after the loading is
finished.
- Use COPY instead of INSERT.
If you use your script to process data, perhaps you could import raw
unprocessed data in a table (with COPY) and process it with SQL. This is
usually much faster than doing a zillion inserts.
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: lists@peufeu.com, christian.bourque@gmail.com
Subject: Re: bulk insert performance problem
In-Reply-To: <op.t9bdm9bicigqcu@apollo13.peufeu.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