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 14:54:55 -0500
Message-ID: <0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net> (raw)
In-Reply-To: <469D1AD3.9030004@ifi.uio.no>
References: <469D1AD3.9030004@ifi.uio.no>
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:
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.
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.
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: <0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@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