Postgres Professional
en

Re: insert vs select into performance

От: Michael Glaesemann
Тема: Re: insert vs select into performance
Дата: ,
Msg-id: 0D6011B2-D09F-45E8-A5D7-5F84ADD1DC2B@seespotcode.net
(см: обсуждение, исходный текст)
Ответ на: insert vs select into performance  (Thomas Finneid)
Ответы: Re: insert vs select into performance  (Tom Lane)
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, ) 
On Jul 17, 2007, at 14:38 , Thomas Finneid 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)
>
> Now I am wondering why the select into is that much faster?

It would be helpful if you included the actual queries you're using,
as there are a number of variables:

1) If there are any constraints on the original table, the INSERT
will be checking those constraints. AIUI, SELECT INTO does not
generate any table constraints.

2a) Are you using INSERT INTO foo (foo1, foo2, foo2) SELECT foo1,
foo2, foo3 FROM pre_foo or individual inserts for each row? The
former would be faster than the latter.

2b) If you are doing individual inserts, are you wrapping them in a
transaction? The latter would be faster.

Michael Glaesemann
grzm seespotcode net

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

Предыдущее:
От: Ron Mayer
Дата:
Сообщение: ionice to make vacuum friendier?
Следующее:
От: Adriaan van Os
Дата:
Сообщение: Re: TRUNCATE TABLE