Re: Some queries starting to hang

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 2006-06-05 at 17:06 -0400, Tom Lane wrote:
> Andrew Sullivan <ajs@xxxxxxxxxxxxxxx> writes:
> > On Mon, Jun 05, 2006 at 04:07:19PM -0400, Tom Lane wrote:
> >> I'm wondering about out-of-date or nonexistent ANALYZE stats, missing
> >> custom adjustments of statistics target settings, etc.
> 
> > But even the nested loop shouldn't be a "never returns" case, should
> > it?  For 1800 rows?
> 
> 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.

Maybe we need something even more drastic than recent proposed changes
to EXPLAIN ANALYZE?

Perhaps we could annotate the query tree with individual limits. That
way a node that was expecting to deal with 1 row would simply stop
executing the EXPLAIN ANALYZE when it hit N times as many rows
(default=no limit). That way, we would still be able to see a bad plan
even without waiting for the whole query to execute - just stop at a
point where the plan is far enough off track. That would give us what we
need: pinpoint exactly which part of the plan is off-track and see how
far off track it is. If the limits were configurable, we'd be able to
opt for faster-but-less-accurate or slower-yet-100% accuracy behaviour.
We wouldn't need to worry about timing overhead either then.

e.g. EXPLAIN ANALYZE ERRLIMIT 10 SELECT ...

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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux