X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 36FC9D1D2DB for ; Mon, 2 Feb 2004 21:37:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04591-05 for ; Mon, 2 Feb 2004 17:37:11 -0400 (AST) Received: from internet.potentialtech.com (h-66-167-251-6.PHLAPAFG.covad.net [66.167.251.6]) by svr1.postgresql.org (Postfix) with ESMTP id 8D145D1BCA7 for ; Mon, 2 Feb 2004 17:37:08 -0400 (AST) Received: from potentialtech.com (pa-plum1c-102.pit.adelphia.net [24.53.179.102]) by internet.potentialtech.com (Postfix) with ESMTP id C383F69A68; Mon, 2 Feb 2004 16:37:05 -0500 (EST) Message-ID: <401EC2FE.3000500@potentialtech.com> Date: Mon, 02 Feb 2004 16:37:02 -0500 From: Bill Moran User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.4) Gecko/20031005 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: Qing Zhao , pgsql-performance@postgresql.org Subject: Re: inserting large number of rows was: Re: Increasing References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/17 X-Sequence-Number: 5541 I must have missed this post when it was made earlier. Pardon the noise if my suggestion has already been made. Unlike MySQL (and possibly other database servers) PostgreSQL is faster when inserting inside a transaction. Depending on the method in which you are actually adding the records. In my own experience (generating a list of INSERT statements from a perl script and using psql to execute them) the difference in performance was incredibly dramatic when I added a "BEGIN WORK" at the beginning and "COMMIT WORK" at the end. scott.marlowe wrote: > On Mon, 2 Feb 2004, Qing Zhao wrote: > > >>I am new here. I have a question related to this in some way. >> >>Our web site needs to upload a large volume of data into Postgres at a >>time. The performance deterioates as number of rows becomes larger. >>When it reaches 2500 rows, it never come back to GUI. Since the tests >>were run through GUI, my suspision is >>that it might be caused by the way the application server talking to >>Postgres server, the connections, etc.. What might be the factors >>involved here? Does anyone know? > > > Actually, I'm gonna go out on a limb here and assume two things: > > 1. you've got lotsa fk/pk relationships setup. > 2. you're analyzing the table empty before loading it up. > > What happens in this instance is that the analyze on an empty, or nearly > so, table, means that during the inserts, postgresql thinks you have only > a few rows. At first, this is fine, as pgsql will seq scan the > tables to make sure there is a proper key in both. As the number of > rows increases, the planner needs to switch to index scans but doesn't, > because it doesn't know that the number of rows is increasing. > > Fix: insert a few hundred rows, run analyze, check to see if the explain > for inserts is showing index scans or not. If not, load a few more > hundred rows, analyze, rinse, repeat. > > Also, look for fk/pk mismatches. I.e. an int4 field pointing to an int8 > field. That's a performance killer, so if the pk/fk types don't match, > see if you can change your field types to match and try again. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bill Moran Potential Technologies http://www.potentialtech.com