Re: insert performance riddle

On Wed, Aug 10, 2005 at 05:02:46PM -0600, Ed L. wrote:
> I have two identical servers giving abysmal INSERT performance in
> pgsql 7.3.4, 7.4.8, and 8.1devel under no load or I/O contention
> at all (no dumps, no vacuums, no apps, etc). Any suggested
> investigations appreciated...
>
> Metric: I'm measuring average insert speed on the following
> table with the following psuedo-loop via DBI 1.48 and DBD::Pg
> 1.43 (older versions of DBD::Pg/DBI are slow, too):
>
> create table foo (id serial, msg varchar);
> for i in 1..1000 do
> insert into foo (msg, "this is a test message");
>
> Results: The slow servers are doing 6...count'em, SIX...that's
> right, I said S-I-X inserts per second (QPS) on average.

Have you done any tests that eliminate the client code and client-server
communcation? Something like the following:

CREATE TABLE foo (id serial, msg varchar);

CREATE FUNCTION insert_foo(integer) RETURNS void AS '
DECLARE
i integer;
BEGIN
FOR i IN 1 .. $1 LOOP
INSERT INTO foo (msg) VALUES (''this is a test message'');
END LOOP;

RETURN;
END;
' LANGUAGE plpgsql VOLATILE STRICT;

EXPLAIN ANALYZE SELECT insert_foo(1000);

--
Michael Fuhr