Re: insert vs select into performance

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


Mark Lewis wrote:
> If you're performing via JDBC, are you using addBatch/executeBatch, or
> are you directly executing each insert?  If you directly execute each
> insert, then your code will wait for a server round-trip between each
> insert.

I tested both and I found almost no difference in the time it took to
perform it. Mind you this was on a local machine, but I still thought
that it was a bit strange.

> That still won't get you to the speed of select into, but it should
> help.  You could also look at the pgsql-jdbc archives for the JDBC
> driver patches which allow you to use COPY-style bulk loading, which
> should get you to the performance level of COPY, which should be
> reasonably close to the performance of select into.

Yes, someone else on the list suggested this a couple of weeks ago. I
havent had a chance to test it yet, but I am hopeful that I can use it.

The only issue I have is that the test I have done are rather
simplistic, because they are just speed trials. The real system will
probably use 5-10 tables, with up to 100 columns for all tables, that
means I need a stored function which goes through all bulked data and
reinserts them into their real tables. I am worried that this might hurt
the performance so much so that almost the entire bulk copy advantage
diasappears. This is why I am wondering about the details of SELECT INTO
and C functions etc.

regards

thomas


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

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