От: Mischa Sandberg
Тема: Re: COPY vs INSERT
Дата: ,
Msg-id: 1115268106.4279a40a7481a@webmail.telus.net
(см: обсуждение, исходный текст)
Ответ на: Re: COPY vs INSERT  (Kris Jurka)
Ответы: Re: COPY vs INSERT  (Kris Jurka)
Список: pgsql-performance

Скрыть дерево обсуждения

 (Steven Rosenstein, )
 Re:  (Tom Lane, )
  Re: COPY vs INSERT  (Mischa Sandberg, )
   Re: COPY vs INSERT  ("David Roussel", )
    Re: COPY vs INSERT  (Mischa Sandberg, )
     Re: COPY vs INSERT  (Christopher Petrilli, )
      Re: COPY vs INSERT  (Keith Worthington, )
     Re: COPY vs INSERT  (Kris Jurka, )
      Re: COPY vs INSERT  (Mischa Sandberg, )
       Re: COPY vs INSERT  (Kris Jurka, )
    Re: COPY vs INSERT  (John A Meinel, )
    Re: COPY vs INSERT  (Christopher Kings-Lynne, )
     Re: COPY vs INSERT  (Tom Lane, )
      Re: COPY vs INSERT  ("Jim C. Nasby", )
       Re: COPY vs INSERT  (Dennis Bjorklund, )
        Re: COPY vs INSERT  ("Jim C. Nasby", )
       Re: COPY vs INSERT  (Harald Fuchs, )
       Re: COPY vs INSERT  (Bruno Wolff III, )
        Re: COPY vs INSERT  (Tom Lane, )
  Re:  (Mike Rylander, )

Quoting Kris Jurka <>:

> On Wed, 4 May 2005, Mischa Sandberg wrote:
>
> > Copy makes better use of the TCP connection for transmission. COPY
> uses
> > the TCP connection like a one-way pipe. INSERT is like an RPC: the
> > sender has to wait until the insert's return status roundtrips.
>
> Not true.  A client may send any number of Bind/Execute messages on
a
> prepared statement before a Sync message.  So multiple inserts may
be
> sent
> in one network roundtrip.  This is exactly how the JDBC driver
> implements batch statements.  There is some limit to the number of
> queries
> in flight at any given moment because there is the potential to
> deadlock
> if both sides of network buffers are filled up and each side is
> blocked
> waiting on a write.  The JDBC driver has conservatively selected 256
> as
> the maximum number of queries to send at once.

Hunh. Interesting optimization in the JDBC driver. I gather it is
sending a string of (;)-separated inserts. Sounds like
efficient-but-risky stuff we did for ODBC drivers at Simba ... gets
interesting when one of the insert statements in the middle fails.
Good to know. Hope that the batch size is parametric, given that you
can have inserts with rather large strings bound to 'text' columns in
PG --- harder to identify BLOBs when talking to PG, than when talking
to MSSQL/Oracle/Sybase.

--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.



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

От: Jona
Дата:
Сообщение: Bad choice of query plan from PG 7.3.6 to PG 7.3.9 part 1
От: Tom Lane
Дата:
Сообщение: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9 part 1