Re: performance for high-volume log insertion

От: Stephen Frost
Тема: Re: performance for high-volume log insertion
Дата: ,
Msg-id: 20090423110434.GM8123@tamriel.snowman.net
(см: обсуждение, исходный текст)
Ответ на: Re: performance for high-volume log insertion  ()
Список: pgsql-performance

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

performance for high-volume log insertion  (, )
 Re: performance for high-volume log insertion  (Stephen Frost, )
  Re: performance for high-volume log insertion  (, )
   Re: performance for high-volume log insertion  (Stephen Frost, )
    Re: performance for high-volume log insertion  (, )
     Re: performance for high-volume log insertion  (Stephen Frost, )
      Re: performance for high-volume log insertion  (, )
       Re: performance for high-volume log insertion  (Stephen Frost, )
        Re: performance for high-volume log insertion  (, )
       Re: performance for high-volume log insertion  (Ben Chobot, )
        Re: performance for high-volume log insertion  (Stephen Frost, )
         Re: performance for high-volume log insertion  (, )
          Re: performance for high-volume log insertion  (Stephen Frost, )
           Re: performance for high-volume log insertion  (, )
            Re: performance for high-volume log insertion  (, )
             Re: performance for high-volume log insertion  (Stephen Frost, )
            Re: performance for high-volume log insertion  (Stephen Frost, )
   Re: performance for high-volume log insertion  (Greg Smith, )
    Re: performance for high-volume log insertion  (Stephen Frost, )
     Re: performance for high-volume log insertion  (, )
      Re: performance for high-volume log insertion  (Stephen Frost, )
    Re: performance for high-volume log insertion  (, )
     Re: performance for high-volume log insertion  (Stephen Frost, )
      Re: performance for high-volume log insertion  (, )
       Re: performance for high-volume log insertion  (Richard Huxton, )
        Re: performance for high-volume log insertion  (Kenneth Marshall, )
         Re: performance for high-volume log insertion  (, )
          Re: performance for high-volume log insertion  (Kenneth Marshall, )
     Re: performance for high-volume log insertion  (Greg Smith, )
      Re: performance for high-volume log insertion  (, )
       Re: performance for high-volume log insertion  (Kenneth Marshall, )
       Re: performance for high-volume log insertion  (Greg Smith, )
   Re: performance for high-volume log insertion  (James Mansion, )
    Re: performance for high-volume log insertion  (Stephen Frost, )
     Re: performance for high-volume log insertion  (, )
      Re: performance for high-volume log insertion  (Robert Haas, )
      Re: performance for high-volume log insertion  (Stephen Frost, )
       Re: performance for high-volume log insertion  (Glenn Maynard, )
        Re: performance for high-volume log insertion  (, )
         Re: performance for high-volume log insertion  (Glenn Maynard, )
        Re: performance for high-volume log insertion  (Stephen Frost, )
         Re: performance for high-volume log insertion  (Tom Lane, )
         Re: performance for high-volume log insertion  (Glenn Maynard, )
          Re: performance for high-volume log insertion  (Stephen Frost, )
           Re: performance for high-volume log insertion  (Glenn Maynard, )
            Re: performance for high-volume log insertion  (Stephen Frost, )
             Re: performance for high-volume log insertion  (Glenn Maynard, )
              Re: performance for high-volume log insertion  (Stephen Frost, )
             Re: performance for high-volume log insertion  (, )
              Re: performance for high-volume log insertion  (Stephen Frost, )
           Re: performance for high-volume log insertion  (Thomas Kellerer, )
            Re: performance for high-volume log insertion  (Kris Jurka, )
             Re: performance for high-volume log insertion  (Thomas, )
             Re: performance for high-volume log insertion  (Scott Marlowe, )
              Re: performance for high-volume log insertion  (Kris Jurka, )
               Re: performance for high-volume log insertion  (Scott Marlowe, )
               Re: performance for high-volume log insertion  (, )
     Re: performance for high-volume log insertion  (James Mansion, )
      Re: performance for high-volume log insertion  (Stephen Frost, )
       Re: performance for high-volume log insertion  (James Mansion, )
        Re: performance for high-volume log insertion  ("Joshua D. Drake", )
        Re: performance for high-volume log insertion  (Glenn Maynard, )
         Re: performance for high-volume log insertion  (, )
          Re: performance for high-volume log insertion  (Stephen Frost, )
         Re: performance for high-volume log insertion  (PFC, )
          Re: performance for high-volume log insertion  (, )
          Re: performance for high-volume log insertion  (Glenn Maynard, )
 Re: performance for high-volume log insertion  (Simon Riggs, )

*  () wrote:
> On Wed, 22 Apr 2009, Stephen Frost wrote:
>> Erm..  Prepared queries is about using PQexecPrepared(), not about
>> sending a text string as an SQL EXECUTE().  PQexecPrepared takes an
>> array of arguments.  That gets translated into a Bind command in the
>> protocol with a defined number of parameters and a length for each
>> parameter being passed.  That removes any need for scanning/parsing the
>> string sent to the backend.  That's the savings I'm referring to.
>
> are you sure? I thought that what goes out over the wire is always text.

Wow, why is there so much confusion and misunderstanding about this?

*psql* sends everything to the backend as text (except perhaps COPY
BINARY..  but that's because the user handles it), but if you're using
libpq, PQexecPrepared, and protocol 3.0 (any recent PG version), it's
going to use the Parse/Bind protocol-level commands.  To make it perhaps
more clear, here's a snippet from the libpq code for PQsendQueryGuts(),
which is the work-horse called by PQexecPrepared:

    /*
     * We will send Parse (if needed), Bind, Describe Portal, Execute, Sync,
     * using specified statement name and the unnamed portal.
     */
[...]

    /* Construct the Bind message */
    if (pqPutMsgStart('B', false, conn) < 0 ||
        pqPuts("", conn) < 0 ||
        pqPuts(stmtName, conn) < 0)
        goto sendFailed;

    /* Send parameter formats */
[...]
-- No param formats included, let the backend know
        if (pqPutInt(0, 2, conn) < 0)
            goto sendFailed;

-- Tell the backend the number of parameters to expect
    if (pqPutInt(nParams, 2, conn) < 0)
        goto sendFailed;

    /* Send parameters */
    for (i = 0; i < nParams; i++)
[...]
-- Pull the length from the caller-provided for each param
                    nbytes = paramLengths[i];
[...]
-- Send the length, then the param, over the wire
            if (pqPutInt(nbytes, 4, conn) < 0 ||
                pqPutnchar(paramValues[i], nbytes, conn) < 0)
                goto sendFailed;
[...]
-- All done, send finish indicator
    if (pqPutInt(1, 2, conn) < 0 ||
        pqPutInt(resultFormat, 2, conn))
        goto sendFailed;
    if (pqPutMsgEnd(conn) < 0)
        goto sendFailed;

    /* construct the Describe Portal message */
    if (pqPutMsgStart('D', false, conn) < 0 ||
        pqPutc('P', conn) < 0 ||
        pqPuts("", conn) < 0 ||
        pqPutMsgEnd(conn) < 0)
        goto sendFailed;

    /* construct the Execute message */
    if (pqPutMsgStart('E', false, conn) < 0 ||
        pqPuts("", conn) < 0 ||
        pqPutInt(0, 4, conn) < 0 ||
        pqPutMsgEnd(conn) < 0)
        goto sendFailed;

[...]
-- clear everything out
    if (pqFlush(conn) < 0)
        goto sendFailed;

Any other questions?

    Thanks,

        Stephen

Вложения

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

От: Tom Lane
Дата:
Сообщение: Re: WHERE condition not being pushed down to union parts
От: Thomas Kellerer
Дата:
Сообщение: Re: performance for high-volume log insertion