X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EF4FDD1D5A2 for ; Tue, 18 Nov 2003 21:41:46 +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 15200-10 for ; Tue, 18 Nov 2003 17:41:17 -0400 (AST) Received: from b0x.supernerd.com (unknown [208.186.134.90]) by svr1.postgresql.org (Postfix) with ESMTP id 9A327D1D58D for ; Tue, 18 Nov 2003 17:41:14 -0400 (AST) Received: from trogdor (unknown [67.42.122.198]) by b0x.supernerd.com (Postfix) with ESMTP id 224EE60003; Tue, 18 Nov 2003 14:37:28 -0700 (MST) Message-ID: <01cc01c3ae1c$b54fa980$0700a8c0@trogdor> From: "Rick Gigger" To: "Mike Mascari" Cc: "PgSQL General ML" References: <1069164075.986.239.camel@bbking.uio.no> <20031118142919.GI4953@libertyrms.info> <1069180780.2769.91.camel@haggis.homelan> <019301c3ae07$d714e220$0700a8c0@trogdor> <87llqdqy37.fsf@asmodeus.mcnaught.org> <20031118205057.K606@hermes.hilbert.loc> <01b201c3ae14$92bd3c00$0700a8c0@trogdor> <3FBA8911.5060805@mascari.com> Subject: Re: performance problem Date: Tue, 18 Nov 2003 14:41:20 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/961 X-Sequence-Number: 52611 Uh, I feel a little silly now. I had and index on the field in question (needed to locate the row to update) but later recreated the table and forgot to readd it. I had assumed that it was there but double checked just now and it was gone. I then readded the index and and it finished in a few minutes. Sorry about that one. Thanks for the help. rg ----- Original Message ----- From: "Mike Mascari" To: "Rick Gigger" Cc: "PgSQL General ML" Sent: Tuesday, November 18, 2003 2:03 PM Subject: Re: [GENERAL] performance problem > Rick Gigger wrote: > > > I am currently trying to import a text data file without about 45,000 > > records. At the end of the import it does an update on each of the 45,000 > > records. Doing all of the inserts completes in a fairly short amount of > > time (about 2 1/2 minutes). Once it gets to the the updates though it slows > > to a craw. After about 10 minutes it's only done about 3000 records. > > > > Is that normal? Is it because it's inside such a large transaction? Is > > there anything I can do to speed that up. It seems awfully slow to me. > > > > I didn't think that giving it more shared buffers would help but I tried > > anyway. It didn't help. > > > > I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot > > of stuff but it didn't speed up the updates at all. > > > > I am using a dual 800mhz xeon box with 2 gb of ram. I've tried anywhere > > from about 16,000 to 65000 shared buffers. > > > > What other factors are involved here? > > It is difficult to say without knowing either the definition of the > relation(s) or the update queries involved. Are there indexes being > created after the import that would allow PostgreSQL to locate the > rows being updated quickly, or is the update an unqualified update (no > WHERE clause) that affects all tuples? > > EXPLAIN ANALYZE is your friend... > > Mike Mascari > mascarm@mascari.com > > >