Re: insert vs select into performance - Mailing list pgsql-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