From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Beecroft <CBeecroft@PrO-Unlimited.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Some queries starting to hang
Date: Mon, 05 Jun 2006 16:07:19 -0400
Message-ID: <22706.1149538039@sss.pgh.pa.us> (raw)
In-Reply-To: <1149534308.3583.56.camel@bg002441.pro-unlimited.com>
References: <1149534308.3583.56.camel@bg002441.pro-unlimited.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, CBeecroft@PrO-Unlimited.com
Subject: Re: Some queries starting to hang
In-Reply-To: <22706.1149538039@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox