From: Michael Glaesemann <grzm@seespotcode.net>
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 16:07:04 -0500
Message-ID: <3FCCED29-F5D9-4AEE-878E-A8F407B1FFEE@seespotcode.net> (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>
On Jul 17, 2007, at 15:50 , Thomas Finneid wrote:
> Michael Glaesemann wrote:
>> 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.
> performed with JDBC
>
> insert into ciu_data_type (id, loc_id, value3, value5, value8,
> value9, value10, value11 ) values (?,?,?,?,?,?,?,?)
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.
> 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.
Just testing in psql with \timing should be fairly easy.
Michael Glaesemann
grzm seespotcode net
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: grzm@seespotcode.net, tfinneid@student.matnat.uio.no
Subject: Re: insert vs select into performance
In-Reply-To: <3FCCED29-F5D9-4AEE-878E-A8F407B1FFEE@seespotcode.net>
* 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