From: Tom Lane <tgl@sss.pgh.pa.us>
To: Relyea, Mike <Mike.Relyea@xerox.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help optimize view
Date: Fri, 10 Aug 2007 17:43:38 -0400
Message-ID: <14336.1186782218@sss.pgh.pa.us> (raw)
In-Reply-To: <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
References: <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
"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
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, Mike.Relyea@xerox.com
Subject: Re: Help optimize view
In-Reply-To: <14336.1186782218@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