pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow long should it take to insert 200,000 records?
16+ messages / 10 participants
[nested] [flat]
* How long should it take to insert 200,000 records?
@ 2007-02-06 00:35 Karen Hill <karen_hill22@yahoo.com>
0 siblings, 3 replies; 16+ messages in thread
From: Karen Hill @ 2007-02-06 00:35 UTC (permalink / raw)
To: pgsql-performance
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? The processor is
a 2ghz cpu. So far I've been sitting here for about 2 million ms
waiting for it to complete, and I'm not sure how many inserts postgres
is doing per second.
regards,
karen
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 05:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karen Hill <karen_hill22@yahoo.com>
2 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2007-02-06 05:33 UTC (permalink / raw)
To: Karen Hill <karen_hill22@yahoo.com>; +Cc: pgsql-performance
"Karen Hill" <karen_hill22@yahoo.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.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 09:33 Csaba Nagy <nagy@ecircle-ag.com>
parent: Karen Hill <karen_hill22@yahoo.com>
2 siblings, 0 replies; 16+ messages in thread
From: Csaba Nagy @ 2007-02-06 09:33 UTC (permalink / raw)
To: Karen Hill <karen_hill22@yahoo.com>; +Cc: pgsql-performance
On Tue, 2007-02-06 at 01:35, Karen Hill wrote:
> [snip] So far I've been sitting here for about 2 million ms
> waiting for it to complete, and I'm not sure how many inserts postgres
> is doing per second.
One way is to run analyze verbose on the target table and see how many
pages it has, and then do it again 1 minute later and check how many
pages it grew. Then multiply the page increase by the record per page
ratio you can get from the same analyze's output, and you'll get an
estimated growth rate. Of course this will only work if you didn't have
lots of free space in the table to start with... if you do have lots of
free space, you still can estimate the growth based on the analyze
results, but it will be more complicated.
In any case, it would be very nice to have more tools to attach to
running queries and see how they are doing... starting with what exactly
they are doing (are they in RI checks maybe ?), the actual execution
plan they are using, how much they've done from their work... it would
help a lot debugging performance problems.
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 16:14 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Karen Hill <karen_hill22@yahoo.com>
2 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2007-02-06 16:14 UTC (permalink / raw)
To: Karen Hill <karen_hill22@yahoo.com>; +Cc: pgsql-performance
On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> 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? The processor is
> a 2ghz cpu. So far I've been sitting here for about 2 million ms
> waiting for it to complete, and I'm not sure how many inserts postgres
> is doing per second.
That really depends. Doing 200,000 inserts as individual transactions
will be fairly slow. Since PostgreSQL generally runs in autocommit
mode, this means that if you didn't expressly begin a transaction, you
are in fact inserting each row as a transaction. i.e. this:
for (i=0;i<200000;i++){
insert into table abc values ('test',123);
}
Is functionally equivalent to:
for (i=0;i<200000;i++){
begin;
insert into table abc values ('test',123);
commit;
}
However, you can add begin / end pairs outside the loop like this:
begin;
for (i=0;i<200000;i++){
insert into table abc values ('test',123);
}
commit;
and it should run much faster.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 16:40 Merlin Moncure <mmoncure@gmail.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 16+ messages in thread
From: Merlin Moncure @ 2007-02-06 16:40 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > 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? The processor is
> > a 2ghz cpu. So far I've been sitting here for about 2 million ms
> > waiting for it to complete, and I'm not sure how many inserts postgres
> > is doing per second.
>
> That really depends. Doing 200,000 inserts as individual transactions
> will be fairly slow. Since PostgreSQL generally runs in autocommit
> mode, this means that if you didn't expressly begin a transaction, you
> are in fact inserting each row as a transaction. i.e. this:
I think OP is doing insertion inside a pl/pgsql loop...transaction is
implied here. For creating test data, generate_series or
insert...select is obviously the way to go. If that's unsuitable for
some reason, I would suggest RAISE NOTICE every n records so you can
monitor the progress and make sure something is not binding up in a
lock or something like that. Be especially wary of degrading
performance during the process.
Another common problem with poor insert performance is a RI check to
an un-indexed column. In-transaction insert performance should be
between 1k and 10k records/second in normal situations, meaning if you
haven't inserted 1 million records inside of an hour something else is
going on.
Generally, insertion performance from fastest to slowest is:
* insert select generate_series...
* insert select
* copy
* insert (),(),()[...] (at least 10 or preferably 100 insertions)
* begin, prepare, n prepared inserts executed, commit
* begin, n inserts, commit
* plpgsql loop, single inserts
* n inserts outside of transaction.
The order of which is faster might not be absolutely set in stone
(copy might beat insert select for example), but the top 4 methods
will always be much faster than the bottom 4.
merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 16:55 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2007-02-06 16:55 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > 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? The processor is
> > > a 2ghz cpu. So far I've been sitting here for about 2 million ms
> > > waiting for it to complete, and I'm not sure how many inserts postgres
> > > is doing per second.
> >
> > That really depends. Doing 200,000 inserts as individual transactions
> > will be fairly slow. Since PostgreSQL generally runs in autocommit
> > mode, this means that if you didn't expressly begin a transaction, you
> > are in fact inserting each row as a transaction. i.e. this:
>
> I think OP is doing insertion inside a pl/pgsql loop...transaction is
> implied here.
Yeah, I noticed that about 10 seconds after hitting send... :)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 17:01 Merlin Moncure <mmoncure@gmail.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 16+ messages in thread
From: Merlin Moncure @ 2007-02-06 17:01 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> > On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > > 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? The processor is
> > > > a 2ghz cpu. So far I've been sitting here for about 2 million ms
> > > > waiting for it to complete, and I'm not sure how many inserts postgres
> > > > is doing per second.
> > >
> > > That really depends. Doing 200,000 inserts as individual transactions
> > > will be fairly slow. Since PostgreSQL generally runs in autocommit
> > > mode, this means that if you didn't expressly begin a transaction, you
> > > are in fact inserting each row as a transaction. i.e. this:
> >
> > I think OP is doing insertion inside a pl/pgsql loop...transaction is
> > implied here.
>
> Yeah, I noticed that about 10 seconds after hitting send... :)
actually, I get the stupid award also because RI check to unindexed
column is not possible :) (this haunts deletes, not inserts).
merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 17:04 Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
0 siblings, 0 replies; 16+ messages in thread
From: Hiltibidal, Robert @ 2007-02-06 17:04 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; Merlin Moncure <mmoncure@gmail.com>; +Cc: Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
What is your row size?
Have you checked to see what your current inserts per second are?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Scott
Marlowe
Sent: Tuesday, February 06, 2007 10:56 AM
To: Merlin Moncure
Cc: Karen Hill; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How long should it take to insert 200,000
records?
On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > 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? The processor
is
> > > a 2ghz cpu. So far I've been sitting here for about 2 million ms
> > > waiting for it to complete, and I'm not sure how many inserts
postgres
> > > is doing per second.
> >
> > That really depends. Doing 200,000 inserts as individual
transactions
> > will be fairly slow. Since PostgreSQL generally runs in autocommit
> > mode, this means that if you didn't expressly begin a transaction,
you
> > are in fact inserting each row as a transaction. i.e. this:
>
> I think OP is doing insertion inside a pl/pgsql loop...transaction is
> implied here.
Yeah, I noticed that about 10 seconds after hitting send... :)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 18:31 Mark Lewis <mark.lewis@mir3.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Mark Lewis @ 2007-02-06 18:31 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On Tue, 2007-02-06 at 12:01 -0500, Merlin Moncure wrote:
> On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > On Tue, 2007-02-06 at 10:40, Merlin Moncure wrote:
> > > On 2/6/07, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > > > On Mon, 2007-02-05 at 18:35, Karen Hill wrote:
> > > > > 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? The processor is
> > > > > a 2ghz cpu. So far I've been sitting here for about 2 million ms
> > > > > waiting for it to complete, and I'm not sure how many inserts postgres
> > > > > is doing per second.
> > > >
> > > > That really depends. Doing 200,000 inserts as individual transactions
> > > > will be fairly slow. Since PostgreSQL generally runs in autocommit
> > > > mode, this means that if you didn't expressly begin a transaction, you
> > > > are in fact inserting each row as a transaction. i.e. this:
> > >
> > > I think OP is doing insertion inside a pl/pgsql loop...transaction is
> > > implied here.
> >
> > Yeah, I noticed that about 10 seconds after hitting send... :)
>
> actually, I get the stupid award also because RI check to unindexed
> column is not possible :) (this haunts deletes, not inserts).
Sure it's possible:
CREATE TABLE parent (col1 int4);
-- insert many millions of rows into parent
CREATE TABLE child (col1 int4 REFERENCES parent(col1));
-- insert many millions of rows into child, very very slowly.
- Mark Lewis
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 18:37 Bruno Wolff III <bruno@wolff.to>
parent: Mark Lewis <mark.lewis@mir3.com>
1 sibling, 0 replies; 16+ messages in thread
From: Bruno Wolff III @ 2007-02-06 18:37 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Scott Marlowe <smarlowe@g2switchworks.com>; Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On Tue, Feb 06, 2007 at 10:31:26 -0800,
Mark Lewis <mark.lewis@mir3.com> wrote:
>
> Sure it's possible:
>
> CREATE TABLE parent (col1 int4);
> -- insert many millions of rows into parent
> CREATE TABLE child (col1 int4 REFERENCES parent(col1));
> -- insert many millions of rows into child, very very slowly.
I don't think Postgres allows this. You don't have to have an index in the
child table, but do in the parent table.
Quote from http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html:
The referenced columns must be the columns of a unique or primary key
constraint in the referenced table.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 19:06 Merlin Moncure <mmoncure@gmail.com>
parent: Mark Lewis <mark.lewis@mir3.com>
1 sibling, 1 reply; 16+ messages in thread
From: Merlin Moncure @ 2007-02-06 19:06 UTC (permalink / raw)
To: Mark Lewis <mark.lewis@mir3.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On 2/6/07, Mark Lewis <mark.lewis@mir3.com> wrote:
> > actually, I get the stupid award also because RI check to unindexed
> > column is not possible :) (this haunts deletes, not inserts).
>
> Sure it's possible:
>
> CREATE TABLE parent (col1 int4);
> -- insert many millions of rows into parent
> CREATE TABLE child (col1 int4 REFERENCES parent(col1));
> -- insert many millions of rows into child, very very slowly.
the database will not allow you to create a RI link out unless the
parent table has a primary key/unique constraint, which the database
backs with an index....and you can't even trick it afterwards by
dropping the constraint.
it's the other direction, when you cascade forwards when you can have
a problem. this is most common with a delete, but can also happen on
an update of a table's primary key with child tables referencing it.
merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 19:10 Mark Lewis <mark.lewis@mir3.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Mark Lewis @ 2007-02-06 19:10 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Karen Hill <karen_hill22@yahoo.com>; pgsql-performance
On Tue, 2007-02-06 at 14:06 -0500, Merlin Moncure wrote:
> On 2/6/07, Mark Lewis <mark.lewis@mir3.com> wrote:
> > > actually, I get the stupid award also because RI check to unindexed
> > > column is not possible :) (this haunts deletes, not inserts).
> >
> > Sure it's possible:
> >
> > CREATE TABLE parent (col1 int4);
> > -- insert many millions of rows into parent
> > CREATE TABLE child (col1 int4 REFERENCES parent(col1));
> > -- insert many millions of rows into child, very very slowly.
>
> the database will not allow you to create a RI link out unless the
> parent table has a primary key/unique constraint, which the database
> backs with an index....and you can't even trick it afterwards by
> dropping the constraint.
>
> it's the other direction, when you cascade forwards when you can have
> a problem. this is most common with a delete, but can also happen on
> an update of a table's primary key with child tables referencing it.
>
Hmmm, should check my SQL before hitting send I guess. Well, at least
you no longer have to wear the stupid award, Merlin :)
-- Mark Lewis
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-06 21:39 Karen Hill <karen_hill22@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 16+ messages in thread
From: Karen Hill @ 2007-02-06 21:39 UTC (permalink / raw)
To: pgsql-performance
On Feb 5, 9:33 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "Karen Hill" <karen_hil...@yahoo.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;
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-07 02:41 Mark Kirkwood <markir@paradise.net.nz>
parent: Karen Hill <karen_hill22@yahoo.com>
2 siblings, 0 replies; 16+ messages in thread
From: Mark Kirkwood @ 2007-02-07 02:41 UTC (permalink / raw)
To: Karen Hill <karen_hill22@yahoo.com>; +Cc: 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-07 03:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karen Hill <karen_hill22@yahoo.com>
2 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2007-02-07 03:07 UTC (permalink / raw)
To: Karen Hill <karen_hill22@yahoo.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How long should it take to insert 200,000 records?
@ 2007-02-14 22:24 Lou O'Quin <LOquin@talleyds.com>
parent: Karen Hill <karen_hill22@yahoo.com>
2 siblings, 0 replies; 16+ messages in thread
From: Lou O'Quin @ 2007-02-14 22:24 UTC (permalink / raw)
To: pgsql-performance; Karen Hill <karen_hill22@yahoo.com>
unless you specify otherwiise, every insert carries its own transaction
begin/commit. That's a lot of overhead for a single insert, no? Why
not use a single transaction for, say, each 1000 inserts? That would
strike a nice balance of security with efficiency.
pseudo code for the insert:
Begin Transaction;
FOR i in 1..200000 LOOP
INSERT INTO viewfoo (x) VALUES (x);
IF i % 1000 = 0 THEN
Commit Transaction;
Begin Transaction;
END IF;
END LOOP;
Commit Transaction;
End
This approach should speed up things dramatically.
>>> "Karen Hill" <karen_hill22@yahoo.com> 2/6/2007 2:39 PM >>>
On Feb 5, 9:33 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "Karen Hill" <karen_hil...@yahoo.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;
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2007-02-14 22:24 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-06 00:35 How long should it take to insert 200,000 records? Karen Hill <karen_hill22@yahoo.com>
2007-02-06 05:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-06 21:39 ` Karen Hill <karen_hill22@yahoo.com>
2007-02-07 02:41 ` Mark Kirkwood <markir@paradise.net.nz>
2007-02-07 03:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-14 22:24 ` Lou O'Quin <LOquin@talleyds.com>
2007-02-06 09:33 ` Csaba Nagy <nagy@ecircle-ag.com>
2007-02-06 16:14 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-02-06 16:40 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-06 16:55 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-02-06 17:01 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-06 18:31 ` Mark Lewis <mark.lewis@mir3.com>
2007-02-06 18:37 ` Bruno Wolff III <bruno@wolff.to>
2007-02-06 19:06 ` Merlin Moncure <mmoncure@gmail.com>
2007-02-06 19:10 ` Mark Lewis <mark.lewis@mir3.com>
2007-02-06 17:04 Re: How long should it take to insert 200,000 records? Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
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