From: Mark Kirkwood <markir@paradise.net.nz>
To: Karen Hill <karen_hill22@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How long should it take to insert 200,000 records?
Date: Wed, 07 Feb 2007 15:41:34 +1300
Message-ID: <45C93C5E.4030706@paradise.net.nz> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markir@paradise.net.nz, karen_hill22@yahoo.com
Subject: Re: How long should it take to insert 200,000 records?
In-Reply-To: <45C93C5E.4030706@paradise.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox