In-Reply-To: <469D2B8E.7050208@ifi.uio.no>
References: <469D1AD3.9030004@ifi.uio.no>
	<0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net>
	<469D2B8E.7050208@ifi.uio.no>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <3FCCED29-F5D9-4AEE-878E-A8F407B1FFEE@seespotcode.net>
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: insert vs select into performance
Date: Tue, 17 Jul 2007 16:07:04 -0500
To: Thomas Finneid <tfinneid@student.matnat.uio.no>


On Jul 17, 2007, at 15:50 , Thomas Finneid wrote:

> Michael Glaesemann wrote:

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

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

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.

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

Just testing in psql with \timing should be fairly easy.

Michael Glaesemann
grzm seespotcode net