From: Thomas Finneid <tfinneid@student.matnat.uio.no>
To: pgsql-performance@postgresql.org
Subject: Re: insert vs select into performance
Date: Tue, 17 Jul 2007 23:27:07 +0200
Message-ID: <469D342B.7080103@ifi.uio.no> (raw)
In-Reply-To: <op.tvmnymm1cigqcu@apollo13>
References: <469D1AD3.9030004@ifi.uio.no>
<op.tvmnymm1cigqcu@apollo13>
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
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: tfinneid@student.matnat.uio.no
Subject: Re: insert vs select into performance
In-Reply-To: <469D342B.7080103@ifi.uio.no>
* 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