If you're performing via JDBC, are you using addBatch/executeBatch, or are you directly executing each insert? If you directly execute each insert, then your code will wait for a server round-trip between each insert.
That still won't get you to the speed of select into, but it should help. You could also look at the pgsql-jdbc archives for the JDBC driver patches which allow you to use COPY-style bulk loading, which should get you to the performance level of COPY, which should be reasonably close to the performance of select into. -- Mark Lewis On Tue, 2007-07-17 at 22:50 +0200, Thomas Finneid wrote: > > Michael Glaesemann wrote: > > > > On Jul 17, 2007, at 14:38 , Thomas Finneid wrote: > > > >> I was doing some testing on "insert" compared to "select into". I > >> inserted 100 000 rows (with 8 column values) into a table, which took > >> 14 seconds, compared to a select into, which took 0.8 seconds. > >> (fyi, the inserts where batched, autocommit was turned off and it all > >> happend on the local machine) > >> > >> Now I am wondering why the select into is that much faster? > > > > It would be helpful if you included the actual queries you're using, as > > there are a number of variables: > > create table ciu_data_type > ( > id integer, > loc_id integer, > value1 integer, > value2 real, > value3 integer, > value4 real, > value5 real, > value6 char(2), > value7 char(3), > value8 bigint, > value9 bigint, > value10 real, > value11 bigint, > value12 smallint, > value13 double precision, > value14 real, > value15 real, > value16 char(1), > value17 varchar(18), > value18 bigint, > value19 char(4) > ); > > performed with JDBC > > insert into ciu_data_type (id, loc_id, value3, value5, value8, value9, > value10, value11 ) values (?,?,?,?,?,?,?,?) > > select * into ciu_data_type_copy from ciu_data_type > > > 1) If there are any constraints on the original table, the INSERT will > > be checking those constraints. AIUI, SELECT INTO does not generate any > > table constraints. > > No constraints in this test. > > > 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. > > > > 2b) If you are doing individual inserts, are you wrapping them in a > > transaction? The latter would be faster. > > disabling autocommit, but nothing more than that > > > 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. > > regards > > thomas > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
