Re: insert vs select into performance

От: PFC
Тема: Re: insert vs select into performance
Дата: ,
Msg-id: op.tvmnymm1cigqcu@apollo13
(см: обсуждение, исходный текст)
Ответ на: insert vs select into performance  (Thomas Finneid)
Ответы: Re: insert vs select into performance  (Thomas Finneid)
Список: 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, )

> 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'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.

    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.

> 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?

    Yeah : instead of your client having to encode 100K * 8 values, send it
over a socket, and postgres decoding it, INSERT INTO SELECT just takes the
data, and writes the data. Same thing as writing a file a byte at a time
versus using a big buffer.

> 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.

    Fortunately this is already done for you : there is the PREPARE
statement, which will remove the parsing overhead. If you must insert many
rows, use VALUES (),(),()...


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

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