Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B148F9FB342 for ; Thu, 28 Sep 2006 13:16:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 16719-10 for ; Thu, 28 Sep 2006 13:15:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 898269FB33C for ; Thu, 28 Sep 2006 13:15:50 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id EEE4D5643A; Thu, 28 Sep 2006 11:15:49 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 28 Sep 2006 11:15:49 -0500 Date: Thu, 28 Sep 2006 11:15:49 -0500 From: "Jim C. Nasby" To: Bill Moran Cc: Matthew Schumacher , pgsql-performance@postgresql.org Subject: Re: Problems with inconsistant query performance. Message-ID: <20060928161549.GN34238@nasby.net> References: <451AC4E2.90106@aptalaska.net> <1c9501c6e2fb$91e30200$0c67a8c0@maniek> <451BE7CD.3060807@aptalaska.net> <20060928112843.0b6e3715.wmoran@collaborativefusion.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060928112843.0b6e3715.wmoran@collaborativefusion.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060928:wmoran@collaborativefusion.com::X+GQgyNdXvw6HWiv:0000000 0000000000000000000000000N4a X-Hashcash: 1:20:060928:matt.s@aptalaska.net::uKNZ8qrfIoy6gBpC:00000000000000000 0000000000000000000000005NEW X-Hashcash: 1:20:060928:pgsql-performance@postgresql.org::olV+c9E4nhq0dCKZ:00000 0000000000000000000000000HjG X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200609/385 X-Sequence-Number: 21071 On Thu, Sep 28, 2006 at 11:28:43AM -0400, Bill Moran wrote: > In response to Matthew Schumacher : > > > > What I really need is a way to profile my proc when it runs slow so that > > I can resolve which of the queries is really slow. Anyone with an idea > > on how to do this? > > You could turn on statement logging and duration logging. This would > give you a record of when things run and how long they take. A little > work analyzing should show you which queries are running when your > favorite query slows down. By default, that doesn't help you debug what's happening inside a function, because you only get the call to the function. I don't know if you can increase verbosity to combat that. Something else to consider is that gettimeofday() on some platforms is painfully slow, which could completely skew all your numbers. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)