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