Обсуждение: TIMING A QUERY ???
How can I get the time it takes a query to execute - explain analyze is taking over 5 hours to complete...can I use \timing??? I don't get any time when using the \timing option... Thanks...Marsha -- View this message in context: http://www.nabble.com/TIMING-A-QUERY-----tf4062567.html#a11542393 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote: > > How can I get the time it takes a query to execute - explain analyze is > taking over 5 hours to complete You can't get it any faster than what explain analyse does: it runs the query. How else would you get the answer? > ...can I use \timing??? I don't get any time when using the > \timing option... How so? It returns Time: N ms at the end of output for me. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland
Andrew Sullivan <ajs@crankycanuck.ca> writes: > On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote: >> How can I get the time it takes a query to execute - explain analyze is >> taking over 5 hours to complete > You can't get it any faster than what explain analyse does: it runs > the query. How else would you get the answer? Well, on some platforms (ie consumer-grade PCs) explain analyze can be a lot slower than just running the query, because of the overhead of all those gettimeofday() calls it does. El cheapo clock hardware is slow to read. (I think the problem is actually that the PC-standard hardware API for clocks was designed back when taking a whole microsecond to read the clock didn't seem like a problem.) >> ...can I use \timing??? I don't get any time when using the >> \timing option... > How so? It returns Time: N ms at the end of output for me. Works for me too. regards, tom lane
"Andrew Sullivan" <ajs@crankycanuck.ca> writes: > On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote: >> >> How can I get the time it takes a query to execute - explain analyze is >> taking over 5 hours to complete > > You can't get it any faster than what explain analyse does: it runs > the query. How else would you get the answer? explain analyze does actually run slower than the actual query because it has to check the time before and after each step of the query, potentially thousands of times. If it's a disk-bound query that doesn't matter much but if it's a cpu-bound query it can. >> ...can I use \timing??? I don't get any time when using the >> \timing option... Yes you can use \timing. You'll have to provide more information of what you're doing before anyone can help you. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Wed, Jul 11, 2007 at 12:10:55PM -0400, Tom Lane wrote: > Well, on some platforms (ie consumer-grade PCs) explain analyze can be a > lot slower than just running the query, Yes, I suppose I exaggerated when I said "can't get any faster", but given that the OP was talking on the order of hours for the EXPLAIN ANALYSE to return, I assumed that the problem is one of impatience and not clock cycles. After all, the gettimeofday() additional overhead is still not going to come in on the order of minutes without a _bursting_ huge query plan. A -- Andrew Sullivan | ajs@crankycanuck.ca Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris
Thanks all...\timing works. -- View this message in context: http://www.nabble.com/TIMING-A-QUERY-----tf4062567.html#a11559115 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.