Re: insert vs select into performance

[Thomas Finneid <tfinneid(at)student(dot)matnat(dot)uio(dot)no>]

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