To: Chris Beecroft <CBeecroft@PrO-Unlimited.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Some queries starting to hang 
In-reply-to: <1149534308.3583.56.camel@bg002441.pro-unlimited.com> 
References: <1149534308.3583.56.camel@bg002441.pro-unlimited.com>
Comments: In-reply-to Chris Beecroft <CBeecroft@PrO-Unlimited.com>
	message dated "Mon, 05 Jun 2006 12:05:08 -0700"
Date: Mon, 05 Jun 2006 16:07:19 -0400
Message-ID: <22706.1149538039@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris Beecroft <CBeecroft@PrO-Unlimited.com> writes:
> Our problem is that about a week and a half ago we started to get some
> queries that would (seemingly) never return (e.g., normally run in a
> couple minutes, but after 2.5 hours, they were still running, the
> process pushing the processor up to 99.9% active).

> Attached are an example query plan: Query.sql
> The query plan from our production sever: QueryPlanBroke.txt
> The working query plan from our backup server: QueryPlanWork.txt

Note the major difference in estimated row counts.  That's the key to
your problem... you need to find out why the "broke" case thinks only
one row is getting selected.

broke:
>    ->  Nested Loop  (cost=30150.77..129334.04 rows=1 width=305)

work:
>    ->  Hash Join  (cost=30904.77..125395.89 rows=1810 width=306)

I'm wondering about out-of-date or nonexistent ANALYZE stats, missing
custom adjustments of statistics target settings, etc.

			regards, tom lane