>>> On Mon, Aug 13, 2007 at 1:48 PM, in message
<1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>, "Relyea,
Mike" <Mike.Relyea@xerox.com> wrote:
> I've increased shared_buffers to 128MB, and restarted the server. My
> total run time didn't really change.
Please forgive me if this guess doesn't help either, but could you try eliminating the GROUP BY options which don't
echovalues in the select value list, and move the HAVING conditions to a WHERE clause? Something like:
explain analyze
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 = "PrintSamples"."TestPatternName"::text)
JOIN
(
"DigitalImages"
JOIN "PrintSampleAnalyses"
ON ("DigitalImages"."ImageID" = "PrintSampleAnalyses"."ImageID")
JOIN
(
"ParameterNames"
JOIN
(
"Measurements"
JOIN "ParameterValues"
ON "Measurements"."MeasurementID" = "ParameterValues"."MeasurementID"
) ON "ParameterNames"."ParameterID" = "ParameterValues"."ParameterID"
) ON "PrintSampleAnalyses"."psaID" = "Measurements"."psaID"
) ON "PrintSamples"."PrintSampleID" = "DigitalImages"."PrintSampleID"
) ON "tblColors"."ColorID" = "tblTPNamesAndColors"."ColorID"
) ON "AnalysisModules"."MetricID" = "Measurements"."MetricID"
WHERE "AnalysisModules"."AnalysisModuleName"::text = 'NMF'::text
AND "ParameterNames"."ParameterName"::text = 'NMF'::text
AND "PrintSamples"."TestPatternName"::text ~~ 'IQAF-TP8%'::text
AND "tblColors"."ColorID" <> 3
GROUP BY
"PrintSamples"."MachineID",
"PrintSamples"."PrintCopyID",
"tblColors"."ColorID"
;
I'd also be inclined to simplify the FROM clause by eliminating the parentheses and putting the ON conditions closer to
wherethey are used, but that would be more for readability than any expectation that it would affect the plan.
-Kevin
