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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
