From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Mike Relyea <Mike.Relyea@xerox.com>
Subject: Re: Help optimize view
Date: Mon, 13 Aug 2007 15:02:15 -0500
Message-ID: <46C07277.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>
References: <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
<14336.1186782218@sss.pgh.pa.us>
<1806D1F73FCB7F439F2C842EE0627B18065BF2C0@USA0300MS01.na.xerox.net>
<46C03F8C.EE98.0025.0@wicourts.gov>
<1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>
>>> 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 echo values 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 where they are used, but that would be more for readability than any expectation that it would affect the plan.
-Kevin
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: Kevin.Grittner@wicourts.gov, Mike.Relyea@xerox.com
Subject: Re: Help optimize view
In-Reply-To: <46C07277.EE98.0025.0@wicourts.gov>
* 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