Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id AF5559FB2E4
	for <pgsql-hackers-postgresql.org@postgresql.org>;
	Fri, 23 Mar 2007 20:04:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 24359-03 for
	<pgsql-hackers-postgresql.org@postgresql.org>;
	Fri, 23 Mar 2007 20:04:10 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 090AD9FA59C
	for <pgsql-hackers@postgresql.org>;
	Fri, 23 Mar 2007 20:04:13 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l2NN4CBS026176;
	Fri, 23 Mar 2007 19:04:12 -0400 (EDT)
To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org,
	"Andrea Olson" <Aolson.CCAP.Courts@wicourts.gov>,
	"Bill Severson" <BSEVERS.CCAP.Courts@wicourts.gov>,
	"John Hutchins" <jhutchi.CCAP.Courts@wicourts.gov>,
	"Randy Peterson" <RPETERS.CCAP.Courts@wicourts.gov>,
	"Shannon Spranger" <ssprang.CCAP.Courts@wicourts.gov>
Subject: Re: EXISTS optimization 
In-reply-to: <46040DAC.EE98.0025.0@wicourts.gov> 
References: 
 <4603C1A3.EE98.0025.0@wicourts.gov><4603C1A3.EE98.0025.0@wicourts.gov>
	<25339.1174686582@sss.pgh.pa.us>
	<46040DAC.EE98.0025.0@wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Fri, 23 Mar 2007 17:26:04 -0500"
Date: Fri, 23 Mar 2007 19:04:12 -0400
Message-ID: <26175.1174691052@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/1430
X-Sequence-Number: 100914

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
>> If you want that, try rewriting the EXISTS to an IN:
>> 
>> AND ("H"."tranNo", "H"."countyNo") IN
>> (
>> SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
>> WHERE "D"."caseNo" LIKE '2006TR%'
>> )

> That's the good news.  The bad news is that I operate under a
> management portability dictate which doesn't currently allow that
> syntax, since not all of the products they want to cover support it.

Which part of it don't they like --- the multiple IN-comparisons?

> I tried something which seems equivalent, but it is running for a very
> long time.
>     AND "H"."tranNo" IN
>         (
>           SELECT "D"."tranNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>               AND "D"."countyNo" = "H"."countyNo"
>         )

No, that's not gonna accomplish a darn thing, because you've still got
a correlated subquery (ie, a reference to outer "H") and so turning the
IN into a join doesn't work.

			regards, tom lane