insert performance - Mailing list pgsql-novice

Hello,

having some problems with the performance of insert statements, I did
some tests, see script below.

Is it right, that the insert performance depends on the number of
columns?

The first insert test took 5 seconds, the second 25 seconds.

Thanks

Marc Orgassa


CREATE TABLE tsomedata
(
  id varchar(40) NOT NULL,
  cnt int4 NOT NULL,
  text varchar(80),
  CONSTRAINT tsomedata_pkey PRIMARY KEY (id, cnt)
);

CREATE OR REPLACE FUNCTION GenSomeData() RETURNS INTEGER AS $BODY$
DECLARE
  i integer;
  SomeData tSomeData%ROWTYPE;
BEGIN
  RAISE NOTICE 'Start GenSomeData:%',timeofday();
  SomeData.text:='SOME TEXT';
  FOR i in 1..10000 LOOP
    SomeData.id:=round(random()*1000)::varchar;
    SomeData.cnt:=i;
    INSERT INTO tSomeData VALUES (SomeData.*);
  END LOOP;
  RAISE NOTICE 'Start GenSomeData:%',timeofday();
  RETURN 10000;
END
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

VACUUM;

SELECT GenSomeData();

DROP TABLE tsomedata;
CREATE TABLE tsomedata
(
  id varchar(40) NOT NULL,
  cnt int4 NOT NULL,
  text varchar(80),
  daten1 numeric(10,2),
  daten2 numeric(10,2),
  daten3 numeric(10,2),
  daten4 numeric(10,2),
  daten5 numeric(10,2),
  daten6 numeric(10,2),
  daten7 numeric(10,2),
  daten8 numeric(10,2),
  daten9 numeric(10,2),
  daten10 numeric(10,2),
  daten11 numeric(10,2),
  daten12 numeric(10,2),
  daten13 numeric(10,2),
  daten14 numeric(10,2),
  daten15 numeric(10,2),
  daten16 numeric(10,2),
  daten17 numeric(10,2),
  daten18 numeric(10,2),
  daten19 numeric(10,2),
  daten20 numeric(10,2),
  daten21 numeric(10,2),
  daten22 numeric(10,2),
  daten23 numeric(10,2),
  daten24 numeric(10,2),
  daten25 numeric(10,2),
  daten26 numeric(10,2),
  daten27 numeric(10,2),
  daten28 numeric(10,2),
  daten29 numeric(10,2),
  daten30 numeric(10,2),
  CONSTRAINT tsomedata_pkey PRIMARY KEY (id, cnt)
);

CREATE OR REPLACE FUNCTION GenSomeData() RETURNS INTEGER AS $BODY$
DECLARE
  i integer;
  SomeData tSomeData%ROWTYPE;
BEGIN
  RAISE NOTICE 'Start GenSomeData:%',timeofday();
  SomeData.text:='SOME TEXT';
  FOR i in 1..10000 LOOP
    SomeData.id:=round(random()*1000)::varchar;
    SomeData.cnt:=i;
    INSERT INTO tSomeData VALUES (SomeData.*);
  END LOOP;
  RAISE NOTICE 'Start GenSomeData:%',timeofday();
  RETURN 10000;
END
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

VACUUM;

SELECT GenSomeData();