From: | Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> |
---|---|
To: | Karen Hill <karen_hill22(at)yahoo(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: How long should it take to insert 200,000 records? |
Date: | 2007-02-07 02:41:34 |
Message-ID: | 45C93C5E.4030706@paradise.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-02-07 03:07:17 | Re: How long should it take to insert 200,000 records? |
Previous Message | Karen Hill | 2007-02-06 21:39:16 | Re: How long should it take to insert 200,000 records? |