Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 32C8D9FB633
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 17 Jul 2007 18:10:59 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 91808-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 17 Jul 2007 18:10:49 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from pat.uio.no (pat.uio.no [129.240.10.15])
	by postgresql.org (Postfix) with ESMTP id D5F9E9FB708
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jul 2007 18:10:52 -0300 (ADT)
Received: from mail-mx5.uio.no ([129.240.10.46])
	by pat.uio.no with esmtp (Exim 4.67)
	(envelope-from <tfinneid@ifi.uio.no>)
	id 1IAuJn-00049f-PN; Tue, 17 Jul 2007 23:10:51 +0200
Received: from smtp.uio.no ([129.240.10.9] helo=mail-mx5.uio.no)
	by mail-mx5.uio.no with esmtp (Exim 4.67)
	(envelope-from <tfinneid@ifi.uio.no>)
	id 1IAuJn-00084M-ET; Tue, 17 Jul 2007 23:10:51 +0200
Received: from 113.80-203-240.nextgentel.com ([80.203.240.113]
	helo=[10.0.0.128])
	by mail-mx5.uio.no with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.67)
	(envelope-from <tfinneid@ifi.uio.no>)
	id 1IAuJn-00084I-83; Tue, 17 Jul 2007 23:10:51 +0200
Message-ID: <469D305A.80606@ifi.uio.no>
Date: Tue, 17 Jul 2007 23:10:50 +0200
From: Thomas Finneid <tfinneid@student.matnat.uio.no>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
To: Mark Lewis <mark.lewis@mir3.com>
CC: Thomas Finneid <tfinneid@student.matnat.uio.no>,
	pgsql-performance@postgresql.org
Subject: Re: insert vs select into performance
References: <469D1AD3.9030004@ifi.uio.no>	
	<0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net>	
	<469D2B8E.7050208@ifi.uio.no> <1184705968.387.327.camel@archimedes>
In-Reply-To: <1184705968.387.327.camel@archimedes>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-UiO-SPF-Received: 
X-UiO-Resend: resent
X-UiO-SPF-Received: 
X-UiO-Spam-info: not spam, SpamAssassin (score=0.0, required=12.0,
	autolearn=disabled, none)
X-UiO-Scanned: 223F2F5D265FB0FCE083AC30EF27F1C095E08199
X-UiO-SPAM-Test: remote_host: 129.240.10.9 spam_score: 0 maxlevel 99990
	minaction 0 bait 0 mail/h: 70 total 2877069 max/h 8345
	blacklist 0 greylist 0 ratelimit 0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/200
X-Sequence-Number: 25884



Mark Lewis wrote:
> If you're performing via JDBC, are you using addBatch/executeBatch, or
> are you directly executing each insert?  If you directly execute each
> insert, then your code will wait for a server round-trip between each
> insert.

I tested both and I found almost no difference in the time it took to 
perform it. Mind you this was on a local machine, but I still thought 
that it was a bit strange.

> That still won't get you to the speed of select into, but it should
> help.  You could also look at the pgsql-jdbc archives for the JDBC
> driver patches which allow you to use COPY-style bulk loading, which
> should get you to the performance level of COPY, which should be
> reasonably close to the performance of select into.

Yes, someone else on the list suggested this a couple of weeks ago. I 
havent had a chance to test it yet, but I am hopeful that I can use it.

The only issue I have is that the test I have done are rather 
simplistic, because they are just speed trials. The real system will 
probably use 5-10 tables, with up to 100 columns for all tables, that 
means I need a stored function which goes through all bulked data and 
reinserts them into their real tables. I am worried that this might hurt 
the performance so much so that almost the entire bulk copy advantage 
diasappears. This is why I am wondering about the details of SELECT INTO 
and C functions etc.

regards

thomas