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 35BA9D1D59B for ; Tue, 18 Nov 2003 21:47:57 +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 20163-06 for ; Tue, 18 Nov 2003 17:47:28 -0400 (AST) Received: from smtp1.gigared.com (smtp1.gigared.com [200.68.216.7]) by svr1.postgresql.org (Postfix) with ESMTP id A1DF7D1D58C for ; Tue, 18 Nov 2003 17:47:20 -0400 (AST) Received: from 10.41.139.99 (unknown [10.41.139.99]) by smtp1.gigared.com (Postfix) with ESMTP id 4FF2D4230; Tue, 18 Nov 2003 18:44:14 -0300 (GMT) From: Martin Marques To: "Rick Gigger" , "PgSQL General ML" Subject: Re: performance problem Date: Tue, 18 Nov 2003 18:50:14 -0300 User-Agent: KMail/1.5 References: <1069164075.986.239.camel@bbking.uio.no> <20031118205057.K606@hermes.hilbert.loc> <01b201c3ae14$92bd3c00$0700a8c0@trogdor> In-Reply-To: <01b201c3ae14$92bd3c00$0700a8c0@trogdor> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200311181850.14802.martin@bugs.unl.edu.ar> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/962 X-Sequence-Number: 52612 El Mar 18 Nov 2003 17:43, Rick Gigger escribi=F3: > 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 sl= ows > to a craw. After about 10 minutes it's only done about 3000 records. Thats not a big amount of rows. It shouldn't be making that amount of troub= le,=20 unless you have something wrong with the update query. Try inserting all the 45K rows and then run an explain analyze to the updat= e=20 query to see what's wrong (reply the output of explain analyze to the list)= .=20 The update query itself would help alot, too. > 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. Not sure, but I have had lot's of big transactions with heady load, and nev= er=20 had a problem like the one you describe. > I didn't think that giving it more shared buffers would help but I tried > anyway. It didn't help. >=20 > I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a l= ot > of stuff but it didn't speed up the updates at all. >=20 > 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. How's memory performace while you're running the updates? What does free sa= y=20 (if you are in some unix environment). --=20 select 'mmarques' || '@' || 'unl.edu.ar' AS email; ----------------------------------------------------------------- Mart=EDn Marqu=E9s | mmarques@unl.edu.ar Programador, Administrador, DBA | Centro de Telem=E1tica Universidad Nacional del Litoral -----------------------------------------------------------------