To: "Karen Hill" <karen_hill22@yahoo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: How long should it take to insert 200,000 records? 
In-reply-to: <1170797956.583767.246670@p10g2000cwp.googlegroups.com> 
References: <1170722158.973141.208230@j27g2000cwj.googlegroups.com>
	<29906.1170740015@sss.pgh.pa.us>
	<1170797956.583767.246670@p10g2000cwp.googlegroups.com>
Comments: In-reply-to "Karen Hill" <karen_hill22@yahoo.com>
	message dated "06 Feb 2007 13:39:16 -0800"
Date: Tue, 06 Feb 2007 22:07:17 -0500
Message-ID: <29362.1170817637@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Karen Hill" <karen_hill22@yahoo.com> writes:
> On Feb 5, 9:33 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
>> I think you have omitted a bunch of relevant facts.

> The postgres version is 8.2.1 on Windows.   The pl/pgsql function is
> inserting to an updatable view (basically two tables).
> [ sketch of schema ]

I think the problem is probably buried in the parts you left out.  Can
you show us the full schemas for those tables, as well as the rule
definition?  The plpgsql function itself can certainly go a lot faster
than what you indicated.  On my slowest active machine:

regression=# create table viewfoo(x int);
CREATE TABLE
regression=# 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;
CREATE FUNCTION
regression=# \timing
Timing is on.
regression=# select functionFoo();
 functionfoo 
-------------
 
(1 row)

Time: 16939.667 ms
regression=# 

			regards, tom lane