Message-ID: <469D2B8E.7050208@ifi.uio.no>
Date: Tue, 17 Jul 2007 22:50:22 +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>
In-Reply-To: <0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net>
Content-Type: text/plain; charset=US-ASCII; format=flowed
Content-Transfer-Encoding: 7bit


Michael Glaesemann wrote:
> 
> On Jul 17, 2007, at 14:38 , Thomas Finneid wrote:
> 
>> I was doing some testing on "insert" compared to "select into". I 
>> inserted 100 000 rows (with 8 column values) into a table, which took 
>> 14 seconds, compared to a select into, which took 0.8 seconds.
>> (fyi, the inserts where batched, autocommit was turned off and it all 
>> happend on the local machine)
>>
>> Now I am wondering why the select into is that much faster?
> 
> It would be helpful if you included the actual queries you're using, as 
> there are a number of variables:

create table ciu_data_type
(
	id		integer,
	loc_id	  	integer,
	value1		integer,
	value2		real,
	value3		integer,
	value4		real,
	value5		real,
	value6		char(2),
	value7		char(3),
	value8		bigint,
	value9		bigint,
	value10		real,
	value11		bigint,
	value12		smallint,
	value13		double precision,
	value14		real,
	value15		real,
	value16		char(1),
	value17		varchar(18),
	value18		bigint,
	value19		char(4)
);

performed with JDBC

insert into ciu_data_type (id, loc_id, value3, value5, value8, value9, 
value10, value11 ) values (?,?,?,?,?,?,?,?)

select * into ciu_data_type_copy from ciu_data_type

> 1) If there are any constraints on the original table, the INSERT will 
> be checking those constraints. AIUI, SELECT INTO does not generate any 
> table constraints.

No constraints in this test.

> 2a) Are you using INSERT INTO foo (foo1, foo2, foo2) SELECT foo1, foo2, 
> foo3 FROM pre_foo or individual inserts for each row? The former would 
> be faster than the latter.
> 
> 2b) If you are doing individual inserts, are you wrapping them in a 
> transaction? The latter would be faster.

disabling autocommit, but nothing more than that


I havent done this test in a stored function yet, nor have I tried it 
with a C client so far, so there is the chance that it is java/jdbc that 
makes the insert so slow. I'll get to that test soon if there is any 
chance my theory makes sence.

regards

thomas