Message-ID: <469D342B.7080103@ifi.uio.no>
Date: Tue, 17 Jul 2007 23:27:07 +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: Re: insert vs select into performance
References: <469D1AD3.9030004@ifi.uio.no> <op.tvmnymm1cigqcu@apollo13>
In-Reply-To: <op.tvmnymm1cigqcu@apollo13>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit


PFC 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)
> 
>     Did you use prepared statements ?
>     Did you use INSERT INTO ... VALUES () with a long list of values, or 
> just 100K insert statements ?

It was prepared statements and I tried it both batched and non-batched 
(not much difference on a local machine)

>     It's the time to parse statements, plan, execute, roundtrips with 
> the client, context switches, time for your client library to escape the 
> data and encode it and for postgres to decode it, etc. In a word : 
> OVERHEAD.

I know there is some overhead, but that much when running it batched...?

>     By the way which language and client library are you using ?
> 
>     FYI 14s / 100k = 140 microseconds per individual SQL query. That 
> ain't slow at all.

Unfortunately its not fast enough, it needs to be done in no more than 
1-2 seconds, ( and in production it will be maybe 20-50 columns of data, 
perhaps divided over 5-10 tables.)
Additionally it needs to scale to perhaps three times as many columns 
and perhaps 2 - 3 times as many rows in some situation within 1 seconds.
Further on it needs to allow for about 20 - 50 clients reading much of 
that data before the next batch of data arrives.

I know the computer is going to be a much faster one than the one I am 
testing with, but I need to make sure the solution scales well.


regars

thomas