Re: Help optimize view

"Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)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