DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=MZMq00xAksTWTI+M7+APKuYJ9o4thAucDx90uuUlujN0PlO/kz3FGw/uIkMb9MNaATK2dgXYEJFLeoDXc0+Mq9DRplmhx7WrmJE2mRXicU6EqFYGPSAk9ljfro6C4cJbe1psIS8+GQeJkcNhQkJe51/QPNqx0uef9Jb1LZknwHk=
Message-ID: <47FAE768.80200@gmail.com>
Date: Tue, 08 Apr 2008 13:32:56 +1000
From: Chris <dmagick@gmail.com>
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>
	<47FAE418.3000406@postnewspapers.com.au>
In-Reply-To: <47FAE418.3000406@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> 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.

If you are, add "analyze" commands through the import, eg every 10,000 
rows. Then your checks should be a bit faster.

The other suggestion would be to do block commits:

begin;
do stuff for 5000 rows;
commit;

repeat until finished.

-- 
Postgresql & php tutorials
http://www.designmagick.com/