Subject: Re: Some queries starting to hang
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>, pgsql-performance@postgresql.org
In-Reply-To: <17148.1149605005@sss.pgh.pa.us>
References: <1149534308.3583.56.camel@bg002441.pro-unlimited.com>
	<22706.1149538039@sss.pgh.pa.us>
	<20060605203851.GB7071@phlogiston.dyndns.org>
	<23915.1149541591@sss.pgh.pa.us>
	<1149604741.2621.487.camel@localhost.localdomain>
	<17148.1149605005@sss.pgh.pa.us>
Content-Type: text/plain
Date: Tue, 06 Jun 2006 15:52:56 +0100
Message-Id: <1149605576.2621.502.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2006-06-06 at 10:43 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Mon, 2006-06-05 at 17:06 -0400, Tom Lane wrote:
> >> Well, it's a big query.  If it ought to take a second or two, and
> >> instead is taking an hour or two (1800 times the expected runtime), that
> >> might be close enough to "never" to exhaust Chris' patience.  Besides,
> >> we don't know whether the 1800 might itself be an underestimate (too bad
> >> Chris didn't provide EXPLAIN ANALYZE results).  
> 
> > This is a good example of a case where the inefficiency of EXPLAIN
> > ANALYZE would be a contributory factor to it not actually being
> > available for diagnosing a problem.
> 
> Huh?  The problem is the inefficiency of the underlying query.

Of course that was the main problem from the OP.

You mentioned it would be good if the OP had delivered an EXPLAIN
ANALYZE; I agree(d). The lack of EXPLAIN ANALYZE is frequently because
you can't get them to run to completion - more so when the query you
wish to analyze doesn't appear to complete either. 

The idea I just had was: why do we need EXPLAIN ANALYZE to run to
completion? In severe cases like this thread, we might be able to
discover the root cause by a *partial* execution of the plan, as long as
it was properly instrumented. That way, the OP might have been able to
discover the root cause himself...

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com