Message-ID: <48162A67.3090500@Sheeky.Biz>
Date: Tue, 29 Apr 2008 05:19:59 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: John Rouillard <rouilj@renesys.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Very poor performance loading 100M of sql data using
 copy
References: <20080428172431.GI6622@renesys.com>
	<48160F05.2090207@enterprisedb.com>
	<20080428180053.GL6622@renesys.com>
In-Reply-To: <20080428180053.GL6622@renesys.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

John Rouillard wrote:

> We can't do this as we are backfilling a couple of months of data 
> into tables with existing data.

Is this a one off data loading of historic data or an ongoing thing?


>>> The only indexes we have to drop are the ones on the primary keys
>>>  (there is one non-primary key index in the database as well).

If this amount of data importing is ongoing then one thought I would try
is partitioning (this could be worthwhile anyway with the amount of data
you appear to have).
Create an inherited table for the month being imported, load the data 
into it, then add the check constraints, indexes, and modify the 
rules/triggers to handle the inserts to the parent table.


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz