Re: insert vs select into performance - Mailing list pgsql-performance

From Thomas Finneid
Subject Re: insert vs select into performance
Date
Msg-id 469E65D8.4030600@ifi.uio.no
Whole thread Raw
In response to Re: insert vs select into performance  (Michael Glaesemann <grzm@seespotcode.net>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Thomas Finneid
Date:
Subject: Re: insert vs select into performance
Next
From: Thomas Finneid
Date:
Subject: Re: insert vs select into performance