Re: insert vs select into performance

От: Thomas Finneid
Тема: Re: insert vs select into performance
Дата: ,
Msg-id: 469D2B8E.7050208@ifi.uio.no
(см: обсуждение, исходный текст)
Ответ на: Re: insert vs select into performance  (Michael Glaesemann)
Ответы: Re: insert vs select into performance  (Michael Stone)
Re: insert vs select into performance  (Mark Lewis)
Re: insert vs select into performance  (Michael Glaesemann)
Список: 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, )


Michael Glaesemann wrote:
>
> 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:

create table ciu_data_type
(
    id        integer,
    loc_id          integer,
    value1        integer,
    value2        real,
    value3        integer,
    value4        real,
    value5        real,
    value6        char(2),
    value7        char(3),
    value8        bigint,
    value9        bigint,
    value10        real,
    value11        bigint,
    value12        smallint,
    value13        double precision,
    value14        real,
    value15        real,
    value16        char(1),
    value17        varchar(18),
    value18        bigint,
    value19        char(4)
);

performed with JDBC

insert into ciu_data_type (id, loc_id, value3, value5, value8, value9,
value10, value11 ) values (?,?,?,?,?,?,?,?)

select * into ciu_data_type_copy from ciu_data_type

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

No constraints in this test.

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

disabling autocommit, but nothing more than that


I havent done this test in a stored function yet, nor have I tried it
with a C client so far, so there is the chance that it is java/jdbc that
makes the insert so slow. I'll get to that test soon if there is any
chance my theory makes sence.

regards

thomas



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

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