Date: Wed, 07 Feb 2007 15:41:34 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: How long should it take to insert 200,000 records?
In-reply-to: <1170797956.583767.246670@p10g2000cwp.googlegroups.com>
To: Karen Hill <karen_hill22@yahoo.com>
Cc: pgsql-performance@postgresql.org
Message-id: <45C93C5E.4030706@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <1170722158.973141.208230@j27g2000cwj.googlegroups.com>
	<29906.1170740015@sss.pgh.pa.us>
	<1170797956.583767.246670@p10g2000cwp.googlegroups.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20061227)

Karen Hill wrote:

> 
> The postgres version is 8.2.1 on Windows.   The pl/pgsql function is
> inserting to an updatable view (basically two tables).
> 
> CREATE TABLE foo1
> (
> 
> 
> ) ;
> 
> CREATE TABLE foo2
> (
> 
> );
> 
> CREATE VIEW viewfoo AS
> (
> 
> );
> CREATE RULE ruleFoo AS ON INSERT TO viewfoo DO INSTEAD
> (
> 
> );
> 
> CREATE OR REPLACE FUNCTION functionFoo() RETURNS VOID AS $$
> BEGIN
> FOR i in 1..200000 LOOP
> INSERT INTO viewfoo (x) VALUES (x);
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
> 


Sorry - but we probably need *still* more detail! - the definition of 
viewfoo is likely to be critical. For instance a simplified variant of 
your setup does 200000 inserts in 5s on my PIII tualatin machine:

CREATE TABLE foo1 (x INTEGER);

CREATE VIEW viewfoo AS SELECT * FROM foo1;

CREATE RULE ruleFoo AS ON INSERT TO viewfoo DO INSTEAD
(
  INSERT INTO foo1 VALUES (new.x);
)

CREATE OR REPLACE FUNCTION functionFoo() RETURNS VOID AS $$
BEGIN
     FOR i in 1..200000 LOOP
         INSERT INTO viewfoo (x) VALUES (i);
     END LOOP;
END;
$$ LANGUAGE plpgsql;


postgres=# \timing
postgres=# SELECT functionFoo() ;
  functionfoo
-------------

(1 row)

Time: 4659.477 ms

postgres=# SELECT count(*) FROM viewfoo;
  count
--------
  200000
(1 row)

Cheers

Mark