Message-ID: <469E65D8.4030600@ifi.uio.no>
Date: Wed, 18 Jul 2007 21:11:20 +0200
From: Thomas Finneid <tfinneid@student.matnat.uio.no>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
To: 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>
	<3FCCED29-F5D9-4AEE-878E-A8F407B1FFEE@seespotcode.net>
In-Reply-To: <3FCCED29-F5D9-4AEE-878E-A8F407B1FFEE@seespotcode.net>
Content-Type: text/plain; charset=US-ASCII; format=flowed
Content-Transfer-Encoding: 7bit

Michael Glaesemann wrote:
> 
> As they're individual inserts, I think what you're seeing is overhead 
> from calling this statement 100,000 times, not just on the server but 
> also the overhead through JDBC. For comparison, try
> 
> CREATE TABLE ciu_data_type_copy LIKE ciu_data_type;
> 
> INSERT INTO ciu_data_type_copy (id, loc_id, value3, value5, value8, 
> value9, value10, value11)
> SELECT id, loc_id, value3, value5, value8, value9, value10, value11
> FROM ciu_data_type;
> 
> I think this would be more comparable to what you're seeing.

This is much faster than my previous solution, but, I also tested two 
other solutions
- a stored function with array arguments and it performed 3 times better.
- jdbc with COPY patch performed 8.4 times faster with text input, 
expect binary input to be even faster.

regards

thomas