From: Mark Lewis <mark.lewis@mir3.com>
To: Thomas Finneid <tfinneid@student.matnat.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert vs select into performance
Date: Tue, 17 Jul 2007 13:59:28 -0700
Message-ID: <1184705968.387.327.camel@archimedes> (raw)
In-Reply-To: <469D2B8E.7050208@ifi.uio.no>
References: <469D1AD3.9030004@ifi.uio.no>
<0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net>
<469D2B8E.7050208@ifi.uio.no>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.lewis@mir3.com, tfinneid@student.matnat.uio.no
Subject: Re: insert vs select into performance
In-Reply-To: <1184705968.387.327.camel@archimedes>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox