Re: insert vs select into performance

От: Thomas Finneid
Тема: Re: insert vs select into performance
Дата: ,
Msg-id: 469D342B.7080103@ifi.uio.no
(см: обсуждение, исходный текст)
Ответ на: Re: insert vs select into performance  (PFC)
Ответы: Re: insert vs select into performance  (PFC)
Список: pgsql-performance

Скрыть дерево обсуждения

insert vs select into performance  (Thomas Finneid, )
 Re: insert vs select into performance  (Michael Glaesemann, )
  Re: insert vs select into performance  (Tom Lane, )
   Re: insert vs select into performance  (Thomas Finneid, )
    Re: insert vs select into performance  (Michael Stone, )
     Re: insert vs select into performance  (Adriaan van Os, )
      Re: insert vs select into performance  (Heikki Linnakangas, )
  Re: insert vs select into performance  (Thomas Finneid, )
   Re: insert vs select into performance  (Michael Stone, )
   Re: insert vs select into performance  (Mark Lewis, )
    Re: insert vs select into performance  (Thomas Finneid, )
   Re: insert vs select into performance  (Michael Glaesemann, )
    Re: insert vs select into performance  (Thomas Finneid, )
 Re: insert vs select into performance  (Arjen van der Meijden, )
  Re: insert vs select into performance  (Thomas Finneid, )
   Re: insert vs select into performance  (Michael Stone, )
    Re: insert vs select into performance  (Thomas Finneid, )
     Re: insert vs select into performance  (Michael Stone, )
 Re: insert vs select into performance  (PFC, )
  Re: insert vs select into performance  (Thomas Finneid, )
   Re: insert vs select into performance  (PFC, )
    Re: insert vs select into performance  (Thomas Finneid, )


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


В списке pgsql-performance по дате сообщения:

От: Ron Mayer
Дата:
Сообщение: ionice to make vacuum friendier?
От: Adriaan van Os
Дата:
Сообщение: Re: TRUNCATE TABLE