Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 30A249FB2DF for ; Tue, 6 Feb 2007 12:40:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 43336-05 for ; Tue, 6 Feb 2007 12:40:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id C7A8B9FB210 for ; Tue, 6 Feb 2007 12:40:02 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1466133ugf for ; Tue, 06 Feb 2007 08:40:01 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=myuKMRQ+BuHl9Wwxid/vezNeIBSsoOuqBPliaPnm4QbZ+XqBsUydZCPlLIdkAIt3bVfiG6u0btoD5VIqlhpokzJf5SUJjc/XP2AdcRbyWCRi7RFdfZS8B28tIRNifEKKUlhl5qpfoJclxF11QE0kdlrhMY2yymuQN8TiqTt8XRg= Received: by 10.78.193.19 with SMTP id q19mr1784482huf.1170780001530; Tue, 06 Feb 2007 08:40:01 -0800 (PST) Received: by 10.78.45.3 with HTTP; Tue, 6 Feb 2007 08:40:01 -0800 (PST) Message-ID: Date: Tue, 6 Feb 2007 11:40:01 -0500 From: "Merlin Moncure" To: "Scott Marlowe" Subject: Re: How long should it take to insert 200,000 records? Cc: "Karen Hill" , pgsql-performance@postgresql.org In-Reply-To: <1170778484.5451.97.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1170722158.973141.208230@j27g2000cwj.googlegroups.com> <1170778484.5451.97.camel@state.g2switchworks.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/67 X-Sequence-Number: 23121 On 2/6/07, Scott Marlowe 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