From cheighlund@yahoo.com Wed Feb 7 17:23:34 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 4ADEB9FB3A5 for ; Wed, 7 Feb 2007 13:23:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 08915-05 for ; Wed, 7 Feb 2007 13:23:19 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web34201.mail.mud.yahoo.com (web34201.mail.mud.yahoo.com [66.163.178.116]) by postgresql.org (Postfix) with SMTP id 4DDE99FB389 for ; Wed, 7 Feb 2007 13:00:36 -0400 (AST) Received: (qmail 55612 invoked by uid 60001); 7 Feb 2007 17:00:33 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=zXw1o9/McZDhF8G4D4jpyLFzttzIZJD0IVRDZacjDlWRs5gIrsAww86ECsd/CmPvIG23d4DEkH81NgipCM6l0gi2ehz4JXeEgymvFyU2rhQDHEOaYZoHJXPwtbCl2XdaKL6cNqQ5a3bqwjIg9Z228Rj+O+V8xS7FO42SoUzFOSg=; X-YMail-OSG: YfeZJq8VM1mU3mm6FDkIzd5QmYN71U2ZHPziOOaW Received: from [216.180.132.69] by web34201.mail.mud.yahoo.com via HTTP; Wed, 07 Feb 2007 09:00:32 PST Date: Wed, 7 Feb 2007 09:00:32 -0800 (PST) From: Andrew Edson Subject: Measuring statement runtime? To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-386747197-1170867632=:55484" Content-Transfer-Encoding: 8bit Message-ID: <95626.55484.qm@web34201.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/110 X-Sequence-Number: 24575 --0-386747197-1170867632=:55484 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit I've got a particular sql statement I'm trying to work out that seems to be taking far too long to run. I've been told that there's supposed to be some kind of timing command to record how long a statement runs for, but I'm having trouble figuring it out. The textbook I'm working with, PostgreSQL (Second Edition) seems to be indicating a command called 'timer' but that has, so far, always thrown an error on the 't'. A search for both 'timer' and 'timing' on Postgresql.org brings up a number of results, but they all appear to have something to do with 'time' and timestamps rather than what I am attempting to find out. What is the command I should be looking for to measure the runtime on my statements? --------------------------------- Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. --0-386747197-1170867632=:55484 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
I've got a particular sql statement I'm trying to work out that seems to be taking far too long to run.  I've been told that there's supposed to be some kind of timing command to record how long a statement runs for, but I'm having trouble figuring it out.  The textbook I'm working with, PostgreSQL (Second Edition) seems to be indicating a command called 'timer' but that has, so far, always thrown an error on the 't'.  A search for both 'timer' and 'timing' on Postgresql.org brings up a number of results, but they all appear to have something to do with 'time' and timestamps rather than what I am attempting to find out.
 
What is the command I should be looking for to measure the runtime on my statements?


Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. --0-386747197-1170867632=:55484-- From ajs@crankycanuck.ca Wed Feb 7 18:41:22 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 967F19FB1DC for ; Wed, 7 Feb 2007 14:41:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 21654-02 for ; Wed, 7 Feb 2007 14:41:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140]) by postgresql.org (Postfix) with ESMTP id 1D68B9FA4CD for ; Wed, 7 Feb 2007 14:41:12 -0400 (AST) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227] helo=phlogiston.dydns.org) by main2.mycybernet.net with esmtp (Exim 4.62) (envelope-from ) id 1HErv8-0003lR-Ad for pgsql-admin@postgresql.org; Wed, 07 Feb 2007 13:53:30 -0500 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 909F43FAF; Wed, 7 Feb 2007 13:41:06 -0500 (EST) Date: Wed, 7 Feb 2007 13:41:06 -0500 From: Andrew Sullivan To: pgsql-admin@postgresql.org Subject: Re: Measuring statement runtime? Message-ID: <20070207184106.GA31198@phlogiston.dyndns.org> References: <95626.55484.qm@web34201.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <95626.55484.qm@web34201.mail.mud.yahoo.com> User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/111 X-Sequence-Number: 24576 On Wed, Feb 07, 2007 at 09:00:32AM -0800, Andrew Edson wrote: > What is the command I should be looking for to measure the > runtime on my statements? In psql, use \timing. In your postgresql.conf file, you can set log_min_duration_statement=0. You can also use EXPLAIN ANALYSE to see the full execution plan, plus information about how long each step _actually_ takes. A -- Andrew Sullivan | ajs@crankycanuck.ca Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz