Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 9CB589F9266
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 10 Aug 2007 18:43:42 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 12247-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 10 Aug 2007 18:43:34 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id C571D9F9265
	for <pgsql-performance@postgresql.org>;
	Fri, 10 Aug 2007 18:43:39 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7ALhc0k014337;
	Fri, 10 Aug 2007 17:43:38 -0400 (EDT)
To: "Relyea, Mike" <Mike.Relyea@xerox.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Help optimize view 
In-reply-to: 
 <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
References: 
 <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
Comments: In-reply-to "Relyea, Mike" <Mike.Relyea@xerox.com>
	message dated "Fri, 10 Aug 2007 15:04:44 -0400"
Date: Fri, 10 Aug 2007 17:43:38 -0400
Message-ID: <14336.1186782218@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/118
X-Sequence-Number: 26248

"Relyea, Mike" <Mike.Relyea@xerox.com> writes:
> SELECT "PrintSamples"."MachineID", "PrintSamples"."PrintCopyID",
> "tblColors"."ColorID", avg("ParameterValues"."ParameterValue") AS
> "Mottle_NMF"
>    FROM "AnalysisModules"
>    JOIN ("tblColors"
>    JOIN ("tblTPNamesAndColors"
>    JOIN "PrintSamples" ON "tblTPNamesAndColors"."TestPatternName"::text
> =3D "PrintSamples"."TestPatternName"::text
>    JOIN ("DigitalImages"
>    JOIN "PrintSampleAnalyses" ON "DigitalImages"."ImageID" =3D
> "PrintSampleAnalyses"."ImageID"
>    JOIN ("ParameterNames"
>    JOIN ("Measurements"
>    JOIN "ParameterValues" ON "Measurements"."MeasurementID" =3D
> "ParameterValues"."MeasurementID") ON "ParameterNames"."ParameterID" =3D
> "ParameterValues"."ParameterID") ON "PrintSampleAnalyses"."psaID" =3D
> "Measurements"."psaID") ON "PrintSamples"."PrintSampleID" =3D
> "DigitalImages"."PrintSampleID") ON "tblColors"."ColorID" =3D
> "tblTPNamesAndColors"."ColorID") ON "AnalysisModules"."MetricID" =3D
> "Measurements"."MetricID"

Try increasing join_collapse_limit --- you have just enough tables here
that the planner isn't going to consider all possible join orders.
And it sorta looks like it's picking a bad one.

			regards, tom lane