Re: Some queries starting to hang

Thanks Tom,

I knew you would come through again!

Query is now returning with results on our replicated database. Will
vacuum analyze production now. So it seems to have done the trick. Now
the question is has our auto vacuum failed or was not set up properly...
A question for my IT people.

Thanks once again,
Chris Beecroft

On Mon, 2006-06-05 at 13:07, Tom Lane wrote:
> Chris Beecroft <CBeecroft(at)PrO-Unlimited(dot)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