Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 1F85F9F9805
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 18 Aug 2007 22:01:59 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 53413-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 18 Aug 2007 22:01:56 -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 459F89F97F6
	for <pgsql-performance@postgresql.org>;
	Sat, 18 Aug 2007 22:01:56 -0300 (ADT)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Sat, 18 Aug 2007 20:01:54 -0500
Message-Id: <46C7502D.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Sat, 18 Aug 2007 20:01:50 -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: 
 <1806D1F73FCB7F439F2C842EE0627B18065BEC18@usa0300ms01.na.xerox.net>
In-Reply-To: 
 <1806D1F73FCB7F439F2C842EE0627B18065BEC18@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/191
X-Sequence-Number: 26321

>>> On Fri, Aug 10, 2007 at 11:57 AM, in message
<1806D1F73FCB7F439F2C842EE0627B18065BEC18@usa0300ms01.na.xerox.net>, =
"Relyea,
Mike" <Mike.Relyea@xerox.com> wrote:=20
> I'm have the following view as part of a larger, aggregate query that is
> running slower than I'd like.
> . . .
> HAVING "PrintSamples"."MachineID" =3D 4741 OR "PrintSamples"."MachineID" =
=3D
> 4745 AND "AnalysisModules"."AnalysisModuleName" =3D 'NMF' AND
> "ParameterNames"."ParameterName" =3D 'NMF' AND "tblColors"."ColorID" <> =
3
> AND "PrintSamples"."TestPatternName" LIKE 'IQAF-TP8%';
=20
First off, let's make sure we're optimizing the query you really want to =
run.
AND binds tighter than OR, so as you have it written, it is the same as:
=20
  HAVING "PrintSamples"."MachineID" =3D 4741
      OR (   "PrintSamples"."MachineID" =3D 4745
         AND "AnalysisModules"."AnalysisModuleName" =3D 'NMF'
         AND "ParameterNames"."ParameterName" =3D 'NMF'
         AND "tblColors"."ColorID" <> 3
         AND "PrintSamples"."TestPatternName" LIKE 'IQAF-TP8%';
         )
=20
I fear you may really want it evaluate to:
=20
  HAVING ("PrintSamples"."MachineID" =3D 4741 OR "PrintSamples"."MachineID"=
 =3D 4745)
     AND "AnalysisModules"."AnalysisModuleName" =3D 'NMF'
     AND "ParameterNames"."ParameterName" =3D 'NMF'
     AND "tblColors"."ColorID" <> 3
     AND "PrintSamples"."TestPatternName" LIKE 'IQAF-TP8%';
=20
-Kevin
=20