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 3F95FD1C915 for ; Tue, 18 Nov 2003 20:43:51 +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 09808-05 for ; Tue, 18 Nov 2003 16:43:21 -0400 (AST) Received: from b0x.supernerd.com (unknown [208.186.134.90]) by svr1.postgresql.org (Postfix) with ESMTP id 95458D1BA81 for ; Tue, 18 Nov 2003 16:43:15 -0400 (AST) Received: from trogdor (unknown [67.42.122.198]) by b0x.supernerd.com (Postfix) with ESMTP id B12B55FD1B for ; Tue, 18 Nov 2003 13:39:13 -0700 (MST) Message-ID: <01b201c3ae14$92bd3c00$0700a8c0@trogdor> From: "Rick Gigger" To: "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> Subject: performance problem Date: Tue, 18 Nov 2003 13:43:06 -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-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=REFERENCES X-Spam-Level: X-Archive-Number: 200311/953 X-Sequence-Number: 52603 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?