Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A6AD59F9587 for ; Wed, 10 Oct 2007 17:48:58 -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 48935-09 for ; Wed, 10 Oct 2007 17:48:53 -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 E2BDE9F9259 for ; Wed, 10 Oct 2007 17:48:52 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 10 Oct 2007 15:48:51 -0500 Message-Id: <470CF450.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Wed, 10 Oct 2007 15:48:32 -0500 From: "Kevin Grittner" To: "Tom Lane" Cc: "Simon Riggs" , Subject: Re: hashjoin chosen over 1000x faster plan References: <470B99BE.EE98.0025.0@wicourts.gov> <1191997904.4233.125.camel@ebony.site> <470C9813.EE98.0025.0@wicourts.gov> <1192035955.4233.306.camel@ebony.site> <1192035955.4233.306.camel@ebony.site> <20980.1192039650@sss.pgh.pa.us> <470CD3E3.EE98.0025.0@wicourts.gov> <1192042492.4233.334.camel@ebony.site> <470CE34D.EE98.0025.0@wicourts.gov><470CE34D.EE98.0025.0@wicourts.gov> <23650.1192048377@sss.pgh.pa.us> In-Reply-To: <23650.1192048377@sss.pgh.pa.us> 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: 200710/113 X-Sequence-Number: 27135 >>> On Wed, Oct 10, 2007 at 3:32 PM, in message <23650.1192048377@sss.pgh.= pa.us>, Tom Lane wrote:=20 >=20 > I'd be very hesitant > to make it choose a plan that is fast only if there were exactly zero > such rows and is slow otherwise. =20 I'm not sure why it looks at the slow option at all; it seems like a = remaining weakness in the OUTER JOIN optimizations. If I change the query = to use an inner join between the CaseHist table and the view, I get more = of what I was expecting for the "slow" option. This ten times faster, and = I can't see why it would not be usable with an outer join. =20 bigbird=3D# explain analyze bigbird-# SELECT bigbird-# "CH"."caseNo", bigbird-# "CH"."countyNo", bigbird-# "CH"."chargeNo", bigbird-# "CH"."statuteCite", bigbird-# "CH"."sevClsCode", bigbird-# "CH"."modSevClsCode", bigbird-# "CH"."descr", bigbird-# "CH"."offenseDate", bigbird-# "CH"."pleaCode", bigbird-# "CH"."pleaDate", bigbird-# "CH"."chargeSeqNo", bigbird-# "CHST"."eventDate" AS "reopEventDate", bigbird-# "CTHE"."descr" AS "reopEventDescr" bigbird-# FROM "Charge" "CH" bigbird-# LEFT OUTER JOIN "CaseHist" "CHST" bigbird-# ON ( "CHST"."countyNo" =3D "CH"."countyNo" bigbird(# AND "CHST"."caseNo" =3D "CH"."caseNo" bigbird(# AND "CHST"."histSeqNo" =3D "CH"."reopHistSeqNo" bigbird(# ) bigbird-# JOIN "CaseTypeHistEvent" "CTHE" bigbird-# ON ( "CHST"."eventType" =3D "CTHE"."eventType" bigbird(# AND "CHST"."caseType" =3D "CTHE"."caseType" bigbird(# AND "CHST"."countyNo" =3D "CTHE"."countyNo" bigbird(# ) bigbird-# WHERE ( bigbird(# ("CH"."caseNo" =3D '2004CF002575') bigbird(# AND ("CH"."countyNo" =3D 13)) bigbird-# ORDER BY bigbird-# "chargeNo", bigbird-# "chargeSeqNo" bigbird-# ; = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------- Sort (cost=3D126.69..126.70 rows=3D1 width=3D168) (actual time=3D36.854..= 36.855 rows=3D2 loops=3D1) Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo" -> Nested Loop Left Join (cost=3D0.00..126.68 rows=3D1 width=3D168) = (actual time=3D36.465..36.623 rows=3D2 loops=3D1) Join Filter: ((d."countyNo")::smallint =3D (c."countyNo")::smallin= t) -> Nested Loop (cost=3D0.00..123.44 rows=3D1 width=3D185) = (actual time=3D24.264..24.408 rows=3D2 loops=3D1) -> Index Scan using "ControlRecord_pkey" on "ControlRecord"= c (cost=3D0.00..4.27 rows=3D1 width=3D2) (actual time=3D9.424..9.427 = rows=3D1 loops=3D1) Index Cond: (13 =3D ("countyNo")::smallint) -> Nested Loop (cost=3D0.00..119.16 rows=3D1 width=3D185) = (actual time=3D14.835..14.975 rows=3D2 loops=3D1) -> Nested Loop (cost=3D0.00..115.67 rows=3D1 = width=3D131) (actual time=3D8.346..8.463 rows=3D2 loops=3D1) -> Index Scan using "Charge_pkey" on "Charge" = "CH" (cost=3D0.00..10.69 rows=3D4 width=3D112) (actual time=3D5.723..8.228= rows=3D4 loops=3D1) Index Cond: ((("countyNo")::smallint =3D = 13) AND (("caseNo")::bpchar =3D '2004CF002575'::bpchar)) -> Index Scan using "CaseHist_pkey" on = "CaseHist" "CHST" (cost=3D0.00..26.18 rows=3D5 width=3D41) (actual = time=3D0.052..0.053 rows=3D0 loops=3D4) Index Cond: ((13 =3D ("CHST"."countyNo")::= smallint) AND ('2004CF002575'::bpchar =3D ("CHST"."caseNo")::bpchar) AND = (("CHST"."histSeqNo")::smallint =3D ("CH"."reopHistSeqNo")::smallint)) -> Index Scan using "CaseTypeHistEventB_pkey" on = "CaseTypeHistEventB" b (cost=3D0.00..3.48 rows=3D1 width=3D69) (actual = time=3D3.248..3.250 rows=3D1 loops=3D2) Index Cond: ((("CHST"."caseType")::bpchar =3D = (b."caseType")::bpchar) AND (("CHST"."eventType")::bpchar =3D (b."eventType= ")::bpchar)) -> Index Scan using "CaseTypeHistEventD_CaseType" on "CaseTypeHis= tEventD" d (cost=3D0.00..3.23 rows=3D1 width=3D17) (actual time=3D6.103..6= .103 rows=3D0 loops=3D2) Index Cond: (((d."caseType")::bpchar =3D (b."caseType")::bpc= har) AND ((d."eventType")::bpchar =3D (b."eventType")::bpchar)) Total runtime: 46.072 ms (18 rows) =20 -Kevin =20