Re: insert vs select into performance

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