Re: performance for high-volume log insertion

[Stephen Frost <sfrost@xxxxxxxxxxx>]

* david@xxxxxxx (david@xxxxxxx) 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

Attachment: signature.asc
Description: Digital signature