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 1FB41D1B54C for ; Tue, 18 Nov 2003 21:05:23 +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 11482-10 for ; Tue, 18 Nov 2003 17:04:52 -0400 (AST) Received: from ms-smtp-03-eri0.ohiordc.rr.com (ms-smtp-03-smtplb.ohiordc.rr.com [65.24.5.137]) by svr1.postgresql.org (Postfix) with ESMTP id 93A33D1BB4F for ; Tue, 18 Nov 2003 17:04:48 -0400 (AST) Received: from corvette.mascari.com (uar225045.columbus.rr.com [204.210.225.45]) by ms-smtp-03-eri0.ohiordc.rr.com (8.12.10/8.12.7) with ESMTP id hAIL4YjG009482; Tue, 18 Nov 2003 16:04:46 -0500 (EST) Received: from mascari.com (ferrari.mascari.com [192.168.2.1]) by corvette.mascari.com (8.11.6/8.11.6) with ESMTP id hAIL3D313556; Tue, 18 Nov 2003 16:03:13 -0500 Message-ID: <3FBA8911.5060805@mascari.com> Date: Tue, 18 Nov 2003 16:03:13 -0500 From: Mike Mascari User-Agent: Mozilla/5.0 (Windows; U; WinNT4.0; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rick Gigger Cc: PgSQL General ML Subject: Re: performance problem 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> In-Reply-To: <01b201c3ae14$92bd3c00$0700a8c0@trogdor> X-Enigmail-Version: 0.74.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-MailScanner: Found to be clean X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/957 X-Sequence-Number: 52607 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