From: | "Karen Hill" <karen_hill22(at)yahoo(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: How long should it take to insert 200,000 records? |
Date: | 2007-02-06 21:39:16 |
Message-ID: | 1170797956.583767.246670@p10g2000cwp.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Feb 5, 9:33 pm, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> "Karen Hill" <karen_hil(dot)(dot)(dot)(at)yahoo(dot)com> writes:
> > I have a pl/pgsql function that is inserting 200,000 records for
> > testing purposes. What is the expected time frame for this operation
> > on a pc with 1/2 a gig of ram and a 7200 RPM disk?
>
> I think you have omitted a bunch of relevant facts. Bare INSERT is
> reasonably quick:
>
> regression=# create table foo (f1 int);
> CREATE TABLE
> regression=# \timing
> Timing is on.
> regression=# insert into foo select x from generate_series(1,200000) x;
> INSERT 0 200000
> Time: 5158.564 ms
> regression=#
>
> (this on a not-very-fast machine) but if you weigh it down with a ton
> of index updates, foreign key checks, etc, it could get slow ...
> also you haven't mentioned what else that plpgsql function is doing.
>
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2007-02-07 02:41:34 | Re: How long should it take to insert 200,000 records? |
Previous Message | Bill Howe | 2007-02-06 19:18:45 | Re: index scan through a subquery |