Re: performance for high-volume log insertion

On Wed, 22 Apr 2009, Stephen Frost wrote:

> * Glenn Maynard (glennfmaynard@gmail.com) wrote:
>> On Wed, Apr 22, 2009 at 5:51 PM, Stephen Frost <sfrost@snowman.net> wrote:
>>> For a single column table, I wouldn't expect much either.  With more
>>> columns I think it would be a larger improvement.
>>
>> Maybe.  I'm not sure why parsing "(1,2,3,4,5)" in an EXECUTE parameter
>> should be faster than parsing the exact same thing in an INSERT,
>> though.
>
> 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.

David Lang

> If you weren't using PQexecPrepared() (and using psql, you wouldn't
> be..), then the difference you saw was more likely planning cost.
>
>> Of course, you still need to get it in that format.  Be careful to
>> include any parsing you're doing to create the binary date in the
>> benchmarks.  Inevitably, at least part of the difference will be costs
>> simply moving from the psql process to your own.
>
> Sure.  What I recall from when I was working on this is that it wasn't
> terribly hard to go from unix timestamps (epoch from 1970) to a PG
> timestamp format (and there was nice example code in the backend) in
> terms of CPU time.
>
>     Thanks,
>
>         Stephen
>