Re: insert vs select into performance

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