Date: Wed, 09 Apr 2008 00:48:35 +0200
To: "Christian Bourque" <christian.bourque@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: bulk insert performance problem
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
Content-Transfer-Encoding: 7bit
Message-ID: <op.t9bdm9bicigqcu@apollo13.peufeu.com>
In-Reply-To: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
User-Agent: Opera Mail/9.24 (Linux)

> 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.