Message-ID: <469D1AD3.9030004@ifi.uio.no>
Date: Tue, 17 Jul 2007 21:38:59 +0200
From: Thomas Finneid <tfinneid@student.matnat.uio.no>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: insert vs select into performance
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi

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?
Does the select into translate into a specially optimised function in c 
that can cut corners which a insert can not do (e.g. lazy copying), or 
is it some other reason?

The reason I am asking is that select into shows that a number of rows 
can be inserted into a table quite a lot faster than one would think was 
possible with ordinary sql. If that is the case, it means that if I 
write an pl-pgsql insert function in C instead of sql, then I can have 
my db perform order of magnitude faster.

Any comments?

regards

thomas