Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id DB0F39FB76F
	for <pgsql-hackers-postgresql.org@postgresql.org>;
	Fri, 23 Mar 2007 18:49:46 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 82004-08 for
	<pgsql-hackers-postgresql.org@postgresql.org>;
	Fri, 23 Mar 2007 18:49:43 -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 5DE4F9FB2A0
	for <pgsql-hackers@postgresql.org>;
	Fri, 23 Mar 2007 18:49:43 -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 l2NLngYF025340;
	Fri, 23 Mar 2007 17:49:42 -0400 (EDT)
To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org,
	"Andrea Olson" <Andrea.Olson@wicourts.gov>,
	"Bill Severson" <Bill.Severson@wicourts.gov>,
	"John Hutchins" <John.Hutchins@wicourts.gov>,
	"Randy Peterson" <Randy.Peterson@wicourts.gov>,
	"Shannon Spranger" <Shannon.Spranger@wicourts.gov>
Subject: Re: EXISTS optimization 
In-reply-to: <4603C1A3.EE98.0025.0@wicourts.gov> 
References: <4603C1A3.EE98.0025.0@wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Fri, 23 Mar 2007 12:01:40 -0500"
Date: Fri, 23 Mar 2007 17:49:42 -0400
Message-ID: <25339.1174686582@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/1424
X-Sequence-Number: 100908

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> explain analyze
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
>   FROM "Adjustment" "A"
>   JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
>   WHERE "H"."tranType" = 'A'
>     AND "A"."date" > DATE '2006-01-01'
>     AND "H"."countyNo" = 66
>     AND "A"."countyNo" = 66
>     AND EXISTS
>         (
>           SELECT 1 FROM "TranDetail" "D"
>             WHERE "D"."tranNo" = "H"."tranNo"
>               AND "D"."countyNo" = "H"."countyNo"
>               AND "D"."caseNo" LIKE '2006TR%'
>         )
> ;

> The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses indexed access to the TranHeader and then to Adjustment.

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%'
        )

We don't currently try to flatten EXISTS into a unique/join plan as we
do for IN.  I seem to recall not doing so when I rewrote IN planning
because I didn't think it would be exactly semantically equivalent,
but that was awhile ago.  Right at the moment it seems like it ought
to be equivalent as long as the comparison operators are strict.

			regards, tom lane