Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 3A9039FBBF0;
	Fri, 23 Mar 2007 19:37:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 07061-02; Fri, 23 Mar 2007 19:37:22 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id 4D4379FB9B8;
	Fri, 23 Mar 2007 19:37:26 -0300 (ADT)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Fri, 23 Mar 2007 17:37:25 -0500
Message-Id: <4604104C.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Fri, 23 Mar 2007 17:37:16 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Tom Lane" <tgl@sss.pgh.pa.us>,
	"Kevin Grittner" <Kgrittn.CCAP.Courts@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: [PERFORM] EXISTS optimization
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>
In-Reply-To: <46040DAC.EE98.0025.0@wicourts.gov>
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: 200703/1429
X-Sequence-Number: 100913



>>> On Fri, Mar 23, 2007 at  5:26 PM, in message
<46040DAC.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:=20

> I tried something which seems=20
> equivalent, but it is running for a very long time.  I'll show it with =
just=20
> the explain while I wait to see how long the explain analyze takes.
> =20
> explain
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",=20
> "H"."userId", "H"."time"
>   FROM "Adjustment" "A"
>   JOIN "TranHeader" "H" ON ("H"."tranId" =3D "A"."adjustmentNo" AND=20
> "H"."countyNo" =3D "A"."countyNo" AND "H"."tranNo" =3D "A"."tranNo")
>   WHERE "H"."tranType" =3D 'A'
>     AND "A"."date" > DATE '2006-  01-  01'
>     AND "H"."countyNo" =3D 66
>     AND "A"."countyNo" =3D 66
>     AND "H"."tranNo" IN
>         (
>           SELECT "D"."tranNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>               AND "D"."countyNo" =3D "H"."countyNo"
>         )
> ;

explain analyze results:
=20
 Nested Loop  (cost=3D0.00..181673.08 rows=3D1 width=3D46) (actual =
time=3D42224.077..964266.969 rows=3D2209 loops=3D1)
   Join Filter: (("H"."tranId")::bpchar =3D ("A"."adjustmentNo")::bpchar)
   ->  Seq Scan on "Adjustment" "A"  (cost=3D0.00..2384.27 rows=3D11733 =
width=3D22) (actual time=3D15.355..146.620 rows=3D13003 loops=3D1)
         Filter: (((date)::date > '2006-01-01'::date) AND (("countyNo")::sm=
allint =3D 66))
   ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=3D0.00=
..15.27 rows=3D1 width=3D46) (actual time=3D74.141..74.141 rows=3D0 =
loops=3D13003)
         Index Cond: ((("H"."tranNo")::integer =3D ("A"."tranNo")::integer)=
 AND (("H"."countyNo")::smallint =3D 66))
         Filter: ((("tranType")::bpchar =3D 'A'::bpchar) AND (subplan))
         SubPlan
           ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" =
"D"  (cost=3D0.00..27.66 rows=3D20 width=3D4) (actual time=3D0.039..58.234 =
rows=3D42342 loops=3D13003)
                 Index Cond: ((("caseNo")::bpchar >=3D '2006TR'::bpchar) =
AND (("caseNo")::bpchar < '2006TS'::bpchar) AND (("countyNo")::smallint =
=3D ($0)::smallint))
                 Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
 Total runtime: 964269.555 ms