Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id E56AE9F9AA6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 13 Aug 2007 17:02:43 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 00448-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 13 Aug 2007 17:02:36 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id 011B19F9576
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Aug 2007 17:02:35 -0300 (ADT)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Mon, 13 Aug 2007 15:02:34 -0500
Message-Id: <46C07277.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Mon, 13 Aug 2007 15:02:15 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>, "Mike Relyea" <Mike.Relyea@xerox.com>
Subject: Re: Help optimize view
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>
In-Reply-To: 
 <1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/135
X-Sequence-Number: 26265

>>> On Mon, Aug 13, 2007 at  1:48 PM, in message
<1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>, =
"Relyea,
Mike" <Mike.Relyea@xerox.com> wrote:=20
> I've increased shared_buffers to 128MB, and restarted the server.  My
> total run time didn't really change.
=20
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:
=20
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 =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"."Meas=
urementID"
          ) ON "ParameterNames"."ParameterID" =3D "ParameterValues"."Parame=
terID"
        ) ON "PrintSampleAnalyses"."psaID" =3D "Measurements"."psaID"
      ) ON "PrintSamples"."PrintSampleID" =3D "DigitalImages"."PrintSampleI=
D"
    ) ON "tblColors"."ColorID" =3D "tblTPNamesAndColors"."ColorID"
  ) ON "AnalysisModules"."MetricID" =3D "Measurements"."MetricID"
  WHERE "AnalysisModules"."AnalysisModuleName"::text =3D 'NMF'::text
    AND "ParameterNames"."ParameterName"::text =3D 'NMF'::text
    AND "PrintSamples"."TestPatternName"::text ~~ 'IQAF-TP8%'::text
    AND "tblColors"."ColorID" <> 3
  GROUP BY
    "PrintSamples"."MachineID",
    "PrintSamples"."PrintCopyID",
    "tblColors"."ColorID"
;
=20
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.
=20
-Kevin
=20