Message-ID: <47FAE418.3000406@postnewspapers.com.au>
Date: Tue, 08 Apr 2008 11:18:48 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Christian Bourque <christian.bourque@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: bulk insert performance problem
References: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
In-Reply-To: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Christian Bourque wrote:
> Hi,
>
> 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!
>   
Would I be correct in guessing that there are foreign key relationships 
between those tables, and that there are significant numbers of indexes 
in use?

The foreign key checking costs will go up as the tables grow, and AFAIK 
the indexes get a bit more expensive to maintain too.

If possible you should probably drop your foreign key relationships and 
drop your indexes, insert your data, then re-create the indexes and 
foreign keys. The foreign keys will be rechecked when you recreate them, 
and it's *vastly* faster to do it that way. Similarly, building an index 
from scratch is quite a bit faster than progressively adding to it. Of 
course, dropping the indices is only useful if you aren't querying the 
tables as you build them.

Also, if you're loading data using stored procedures you should avoid 
the use of exception blocks. I had some major problems with my bulk data 
conversion code due to overuse of exception blocks creating large 
numbers of subtransactions behind the scenes and slowing everything to a 
crawl.

--
Craig Ringer