Re: insert performance riddle

On Thu, Aug 11, 2005 at 12:59:32PM -0600, Ed L. wrote:
> > Michael, you seem to have nailed it.  The local inserts (via
> > Unix domain sockets?) that were running at 6 QPS ran at 6800
> > to 41000 QPS in a PL/pgSQL function.
>
> Here's another part of the riddle.  The query durations for the
> individual inserts as logged in the server log are ranging
> 100ms-500ms per insert when connecting via DBI/DBD::Pg from
> localhost.  Inside pl/pgsql, they're clearly averaging faster
> than 1ms per insert.  I would have thought those times were
> independent of the client communication layers...?

Have you done any client-side tests that eliminate Perl?  I'd suggest
writing a little C program so you can measure libpq's performance
without the extra layers of Perl and DBI/DBD::Pg.  Test both local
(Unix socket) and network (IPv4 or IPv6 socket) connections.

A couple of months ago, Tom Lane noticed that benchmarks using
DBD::Pg make PostgreSQL look worse than it deserves:

http://archives.postgresql.org/pgsql-interfaces/2005-06/msg00003.php

--
Michael Fuhr