Postgres Professional
en

Re: insert vs select into performance

От: Michael Glaesemann
Тема: Re: insert vs select into performance
Дата: ,
Msg-id: 3FCCED29-F5D9-4AEE-878E-A8F407B1FFEE@seespotcode.net
(см: обсуждение, исходный текст)
Ответ на: Re: 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, ) 
On Jul 17, 2007, at 15:50 , Thomas Finneid wrote:

> Michael Glaesemann wrote:

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

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

As they're individual inserts, I think what you're seeing is overhead
from calling this statement 100,000 times, not just on the server but
also the overhead through JDBC. For comparison, try

CREATE TABLE ciu_data_type_copy LIKE ciu_data_type;

INSERT INTO ciu_data_type_copy (id, loc_id, value3, value5, value8,
value9, value10, value11)
SELECT id, loc_id, value3, value5, value8, value9, value10, value11
FROM ciu_data_type;

I think this would be more comparable to what you're seeing.

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

Just testing in psql with \timing should be fairly easy.

Michael Glaesemann
grzm seespotcode net

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

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