Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id CC8FF9FB6DA for ; Wed, 10 Oct 2007 18:03:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 22897-06 for ; Wed, 10 Oct 2007 18:03:04 -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 87D359FB51F for ; Wed, 10 Oct 2007 18:03:11 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 10 Oct 2007 16:03:08 -0500 Message-Id: <470CF7B3.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Wed, 10 Oct 2007 16:02:59 -0500 From: "Kevin Grittner" To: "Tom Lane" , "Kevin Grittner" 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> <470CF450.EE98.0025.0@wicourts.gov> In-Reply-To: <470CF450.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: 200710/115 X-Sequence-Number: 27137 >>> On Wed, Oct 10, 2007 at 3:48 PM, in message <470CF450.EE98.0025.0@wicourts.gov>, "Kevin Grittner" wrote:=20 > =20 > This ten times faster =20 That understates it -- I forgot to get things cached, as I had done for all the other tests. When cached, this is sub-millisecond, although not quite the 1000-fold increase which I get when no matches are found. =20 -Kevin =20 Sort (cost=3D126.70..126.70 rows=3D1 width=3D168) (actual time=3D0.259..0= .261 rows=3D2 loops=3D1) Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo" -> Nested Loop Left Join (cost=3D0.00..126.69 rows=3D1 width=3D168) = (actual time=3D0.157..0.234 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=3D0.139..0.203 rows=3D2 loops=3D1) -> Index Scan using "ControlRecord_pkey" on "ControlRecord"= c (cost=3D0.00..4.27 rows=3D1 width=3D2) (actual time=3D0.024..0.026 = rows=3D1 loops=3D1) Index Cond: (13 =3D ("countyNo")::smallint) -> Nested Loop (cost=3D0.00..119.17 rows=3D1 width=3D185) = (actual time=3D0.109..0.169 rows=3D2 loops=3D1) -> Nested Loop (cost=3D0.00..115.67 rows=3D1 = width=3D131) (actual time=3D0.087..0.127 rows=3D2 loops=3D1) -> Index Scan using "Charge_pkey" on "Charge" = "CH" (cost=3D0.00..10.69 rows=3D4 width=3D112) (actual time=3D0.038..0.051= 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.014..0.015 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=3D0.015..0.017 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=3D0.011..0= .011 rows=3D0 loops=3D2) Index Cond: (((d."caseType")::bpchar =3D (b."caseType")::bpc= har) AND ((d."eventType")::bpchar =3D (b."eventType")::bpchar)) Total runtime: 0.605 ms (18 rows)