Date: Tue, 29 Apr 2008 15:04:32 +0000
From: John Rouillard <rouilj@renesys.com>
To: pgsql-performance@postgresql.org
Subject: Re: Very poor performance loading 100M of sql data using copy
Message-ID: <20080429150432.GO6622@renesys.com>
References: <20080428172431.GI6622@renesys.com>
	<48160F05.2090207@enterprisedb.com>
	<20080428180053.GL6622@renesys.com> <48162A67.3090500@Sheeky.Biz>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <48162A67.3090500@Sheeky.Biz>
User-Agent: Mutt/1.4.1i

On Tue, Apr 29, 2008 at 05:19:59AM +0930, Shane Ambler wrote:
> 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?

Yes it's a one off bulk data load of many days of data. The daily
loads will also take 3 hour's but that is ok since we only do those
once a day so we have 21 hours of slack in the schedule 8-).

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

Hmm, interesting idea, worth considering if we have to do this again
(I hope not). 

Thaks for the reply.

-- 
				-- rouilj

John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111