Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9963A9FA173 for ; Thu, 14 Dec 2006 12:36:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 28001-09 for ; Thu, 14 Dec 2006 12:35:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mir3-fs.mir3.com (mail.mir3.com [65.208.188.100]) by postgresql.org (Postfix) with ESMTP id 1F3469FA182 for ; Thu, 14 Dec 2006 12:35:49 -0400 (AST) Received: mir3-fs.mir3.com 172.16.1.11 from 172.16.2.68 172.16.2.68 via HTTP with MS-WebStorage 6.0.6249 Received: from archimedes.mirlogic.com by mir3-fs.mir3.com; 14 Dec 2006 08:34:55 -0800 Subject: Re: Slow update with simple query From: Mark Lewis To: Tom Lane Cc: Arnaud Lesauvage , Ragnar , Jens Schipkowski , pgsql-performance@postgresql.org In-Reply-To: <21233.1166113140@sss.pgh.pa.us> References: <457FDB1E.6040801@freesurf.fr> <457FF0CD.4050606@freesurf.fr> <4580025D.2040903@freesurf.fr> <1166021340.6369.103.camel@localhost.localdomain> <45801A1B.6070200@freesurf.fr> <8399.1166028385@sss.pgh.pa.us> <4581593E.8080401@freesurf.fr> <20426.1166109494@sss.pgh.pa.us> <45816C54.5010602@freesurf.fr> <21233.1166113140@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: MIR3, Inc. Date: Thu, 14 Dec 2006 08:34:55 -0800 Message-Id: <1166114095.27428.133.camel@archimedes> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-27.rhel4.6) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/376 X-Sequence-Number: 22432 Out of curiosity, how hard would it be to modify the output of EXPLAIN ANALYZE when doing an insert/update to include the index update times and/or non-FK constraint checking times and/or the table row update times? Or any other numbers that might be useful in circumstances like this. I'm wondering if it's possible to shed some light on the remaining dark shadows of PG performance troubleshooting. -- Mark Lewis On Thu, 2006-12-14 at 11:19 -0500, Tom Lane wrote: > Arnaud Lesauvage writes: > > Tom Lane a crit : > >> It seems the time must be going into this trigger function. What > >> does it do? > > > A lot of things ! Indeed, if it runs it will very badly hurt performances (table > > lookups, string manipulation, etc...) ! > > But it should only be tringered on INSERTs, and I am doing an UPDATE ! > > Doh, right, I obviously still need to ingest more caffeine this morning. > > I think the conclusion must be that there was just too much I/O to be > done to update all the rows. Have you done any tuning of shared_buffers > and so forth? I recall having seen cases where update performance went > bad as soon as the upper levels of a large index no longer fit into > shared_buffers ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend