From: Tom Lane <tgl@sss.pgh.pa.us>
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: Tue, 06 Feb 2007 22:07:17 -0500
Message-ID: <29362.1170817637@sss.pgh.pa.us> (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" <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
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: tgl@sss.pgh.pa.us, karen_hill22@yahoo.com
Subject: Re: How long should it take to insert 200,000 records?
In-Reply-To: <29362.1170817637@sss.pgh.pa.us>
* 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