From: Scott Marlowe <smarlowe@g2switchworks.com>
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 10:14:44 -0600
Message-ID: <1170778484.5451.97.camel@state.g2switchworks.com> (raw)
In-Reply-To: <1170722158.973141.208230@j27g2000cwj.googlegroups.com>
References: <1170722158.973141.208230@j27g2000cwj.googlegroups.com>
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.
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: smarlowe@g2switchworks.com, karen_hill22@yahoo.com
Subject: Re: How long should it take to insert 200,000 records?
In-Reply-To: <1170778484.5451.97.camel@state.g2switchworks.com>
* 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